Hello
I am trying to run r.what to populate a table in PostgreSQL using;
echo 'select x, y from xy limit 10' | db.select -c fs=' ' | r.what
input=layer fs=',' | ...
This bit works OK (I do get an extra blank column - see below)
140.877,-33.9604,4071
140.88,-33.9604,3670
140.882,-33.9604,3967
140.884,-33.9604,4630
140.887,-33.9604,4932
140.889,-33.9604,5090
140.891,-33.9604,4593
140.894,-33.9604,4678
140.896,-33.9604,4561
140.898,-33.9604,4561
I would then like to pipe/copy/append this to another table in PostgreSQL.
I have tried using db.copy and db.out.ogr but none seem to work.
Any help would be great!
Regards, Phil
--
View this message in context: http://osgeo-org.1803224.n2.nabble.com/Piping-a-table-to-PostgreSQL-tp6864910p6864910.html
Sent from the Grass - Users mailing list archive at Nabble.com.
Hello
I am trying to run r.what to populate a table in PostgreSQL using;
echo 'select x, y from xy limit 10' | db.select -c fs=' ' | r.what
input=layer fs=',' | ...
This bit works OK (I do get an extra blank column - see below)
140.877,-33.9604,,4071
140.88,-33.9604,,3670
140.882,-33.9604,,3967
140.884,-33.9604,,4630
140.887,-33.9604,,4932
140.889,-33.9604,,5090
140.891,-33.9604,,4593
140.894,-33.9604,,4678
140.896,-33.9604,,4561
140.898,-33.9604,,4561
I would then like to pipe/copy/append this to another table in PostgreSQL.
I have tried using db.copy and db.out.ogr but none seem to work.
I don’t know of any way to bulk insert values into a PostgreSQL table, other than regular SQL INSERT commands or a COPY. I think the best would be to dump your results into a temporary CSV text file, then run a psql COPY command to upload the file to your PostgreSQL table.
To stay with GRASS commands, you could pipe INSERT commands to db.execute, but that would still require an intermediate file with the x,y,val columns. Then you would need read that intermediate file, loop thru the rows and set up an SQL INSERT for each row.
HTH,
Micha
Hello
I am trying to run r.what to populate a table in PostgreSQL using;
echo 'select x, y from xy limit 10' | db.select -c fs=' ' | r.what
input=layer fs=',' | ...
This bit works OK (I do get an extra blank column - see below)
140.877,-33.9604,4071
140.88,-33.9604,3670
140.882,-33.9604,3967
140.884,-33.9604,4630
140.887,-33.9604,4932
140.889,-33.9604,5090
140.891,-33.9604,4593
140.894,-33.9604,4678
140.896,-33.9604,4561
140.898,-33.9604,4561
I would then like to pipe/copy/append this to another table in PostgreSQL.
I have tried using db.copy and db.out.ogr but none seem to work.
I did something similar once like this:
cat filename.csv | psql -U username databasename -c "COPY tablename(column1,
column2, column3, column4) FROM STDIN WITH CSV HEADER;"
- pure COPY didn't work because the database was on a remote machine
- instead of "cat filename" you use your data generating pipe
- if your data stream has no column names erase the keyword HEADER
hope it helps,
robert