Thread: Piping CSV data to psql when executing COPY .. FROM STDIN
Hi all, I am trying to copy data (results of a SELECT * FROM abc WHERE ...) to a remote PC (on my network). If I execute the "COPY abc2 FROM abc.txt CSV WITH HEADER" on psql connected to the remote PC, PostgreSQL will expect the file to be resident on the (remote) server's file system (or atleast accessible via it's file system). This would require me to configure password less scp for a given OS user account between the two PC. Then have a script send the csv file to the remote server before I call the COPY command. Am trying to avoid this step. The alternative I am attempting is to use "COPY abc FROM STDIN WITH HEADER". I pipe the contents of the CSV file on my PC to the psql command (that connects to the remote PC) while issuing this copy command. This does seems not to work. Is there a way around it. Allan.
On Tue, Oct 28, 2008 at 01:25:00PM +0200, Allan Kamau wrote:
> The alternative I am attempting is to use "COPY abc FROM STDIN WITH
> HEADER". I pipe the contents of the CSV file on my PC to the psql
> command (that connects to the remote PC) while issuing this copy command.
> This does seems not to work.
It does whenever I try it and if you've ever restored from a pg_dump
then you've used it as well!
> Is there a way around it.
When I've had a CSV file and needed to bung it into a database, I've
tended to end up with shell scripts like this before:
( echo 'COPY abc FROM STDIN WITH CSV HEADER;'
cat "$1"
echo '\.'
) | psql
an alternative is to use the "\copy" feature inside psql that does this
sort of thing internally. One thing to be aware of is that it doesn't
expect a semicolon at the end of the line, but is otherwise the same as
the SQL COPY command.
Sam
Sam, I have been unable to understand your shell script well enough to use it. Seems am slow this afternoon :-) On this list I saw a message detailing using copy as illustrated below (see <code/>)when I run this command I get the following output (see <output/>) <output> COPY abc FROM STDIN WITH CSV HEADER; \. 1 qrst a 2 zvy b </output> As you can see the ./ is placed a the top instead of the bottom of the output. The does create some error when I run this output via psql. I then get a datatype error when I pass to psql the following (edited) sql from a text editor (see <sql/>) <sql> COPY abc FROM STDIN WITH CSV HEADER; 1 qrst a 2 zvy b \. </sql> The error reads as follows <output2> psql:sql/some2.sql:7: ERROR: invalid input syntax for integer: "1 qrst a" CONTEXT: COPY item_major, line 1, column id: "1 qrst a" </output2> <code> \echo 'COPY abc FROM STDIN WITH CSV HEADER;' COPY ( SELECT * FROM abc ) to STDOUT WITH delimiter E'\t' \echo '\\.' </code> Sam Mason wrote: > On Tue, Oct 28, 2008 at 01:25:00PM +0200, Allan Kamau wrote: > >> The alternative I am attempting is to use "COPY abc FROM STDIN WITH >> HEADER". I pipe the contents of the CSV file on my PC to the psql >> command (that connects to the remote PC) while issuing this copy command. >> This does seems not to work. >> > > It does whenever I try it and if you've ever restored from a pg_dump > then you've used it as well! > > >> Is there a way around it. >> > > When I've had a CSV file and needed to bung it into a database, I've > tended to end up with shell scripts like this before: > > ( echo 'COPY abc FROM STDIN WITH CSV HEADER;' > cat "$1" > echo '\.' > ) | psql > > an alternative is to use the "\copy" feature inside psql that does this > sort of thing internally. One thing to be aware of is that it doesn't > expect a semicolon at the end of the line, but is otherwise the same as > the SQL COPY command. > > > Sam > >
Reid Thompson wrote: > Allan Kamau wrote: >> Sam, I have been unable to understand your shell script well enough >> to use it. Seems am slow this afternoon :-) >> >> On this list I saw a message detailing using copy as illustrated >> below (see <code/>)when I run this command I get the following output >> (see <output/>) >> >> >> <output> >> COPY abc FROM STDIN WITH CSV HEADER; >> \. >> 1 qrst a >> 2 zvy b >> </output> >> >> As you can see the ./ is placed a the top instead of the bottom of >> the output. The does create some error when I run this output via psql. >> I then get a datatype error when I pass to psql the following >> (edited) sql from a text editor (see <sql/>) >> >> <sql> >> COPY abc FROM STDIN WITH CSV HEADER; >> 1 qrst a >> 2 zvy b >> \. >> </sql> >> >> The error reads as follows >> <output2> >> psql:sql/some2.sql:7: ERROR: invalid input syntax for integer: >> "1 qrst a" >> CONTEXT: COPY item_major, line 1, column id: "1 qrst a" >> </output2> >> >> >> >> <code> >> \echo 'COPY abc FROM STDIN WITH CSV HEADER;' >> COPY >> ( >> SELECT * FROM abc >> ) >> to STDOUT >> WITH delimiter E'\t' >> \echo '\\.' >> </code> >> >> >> >> >> Sam Mason wrote: >>> On Tue, Oct 28, 2008 at 01:25:00PM +0200, Allan Kamau wrote: >>> >>>> The alternative I am attempting is to use "COPY abc FROM STDIN WITH >>>> HEADER". I pipe the contents of the CSV file on my PC to the psql >>>> command (that connects to the remote PC) while issuing this copy >>>> command. >>>> This does seems not to work. >>>> >>> >>> It does whenever I try it and if you've ever restored from a pg_dump >>> then you've used it as well! >>> >>> >>>> Is there a way around it. >>>> >>> >>> When I've had a CSV file and needed to bung it into a database, I've >>> tended to end up with shell scripts like this before: >>> >>> ( echo 'COPY abc FROM STDIN WITH CSV HEADER;' >>> cat "$1" >>> echo '\.' >>> ) | psql >>> >>> an alternative is to use the "\copy" feature inside psql that does this >>> sort of thing internally. One thing to be aware of is that it doesn't >>> expect a semicolon at the end of the line, but is otherwise the same as >>> the SQL COPY command. >>> >>> >>> Sam >>> >>> >> >> > are these space delimited values, or tab delimited values? > 1 qrst a > 2 zvy b They are (tab delimited values), I've rechecked them, did a find and replace for any space between the columns with a tab, the error persists. Allan.
Allan Kamau wrote: > Sam, I have been unable to understand your shell script well enough to > use it. Seems am slow this afternoon :-) > > On this list I saw a message detailing using copy as illustrated below > (see <code/>)when I run this command I get the following output (see > <output/>) > > > <output> > COPY abc FROM STDIN WITH CSV HEADER; > \. > 1 qrst a > 2 zvy b > </output> > > As you can see the ./ is placed a the top instead of the bottom of the > output. The does create some error when I run this output via psql. > I then get a datatype error when I pass to psql the following (edited) > sql from a text editor (see <sql/>) > > <sql> > COPY abc FROM STDIN WITH CSV HEADER; > 1 qrst a > 2 zvy b > \. > </sql> > > The error reads as follows > <output2> > psql:sql/some2.sql:7: ERROR: invalid input syntax for integer: "1 > qrst a" > CONTEXT: COPY item_major, line 1, column id: "1 qrst a" > </output2> > > > > <code> > \echo 'COPY abc FROM STDIN WITH CSV HEADER;' > COPY > ( > SELECT * FROM abc > ) > to STDOUT > WITH delimiter E'\t' > \echo '\\.' > </code> > > > > > Sam Mason wrote: >> On Tue, Oct 28, 2008 at 01:25:00PM +0200, Allan Kamau wrote: >> >>> The alternative I am attempting is to use "COPY abc FROM STDIN WITH >>> HEADER". I pipe the contents of the CSV file on my PC to the psql >>> command (that connects to the remote PC) while issuing this copy >>> command. >>> This does seems not to work. >>> >> >> It does whenever I try it and if you've ever restored from a pg_dump >> then you've used it as well! >> >> >>> Is there a way around it. >>> >> >> When I've had a CSV file and needed to bung it into a database, I've >> tended to end up with shell scripts like this before: >> >> ( echo 'COPY abc FROM STDIN WITH CSV HEADER;' >> cat "$1" >> echo '\.' >> ) | psql >> >> an alternative is to use the "\copy" feature inside psql that does this >> sort of thing internally. One thing to be aware of is that it doesn't >> expect a semicolon at the end of the line, but is otherwise the same as >> the SQL COPY command. >> >> >> Sam >> >> > > are these space delimited values, or tab delimited values? 1 qrst a 2 zvy b
On Tue, Oct 28, 2008 at 03:11:05PM +0200, Allan Kamau wrote: > Sam, I have been unable to understand your shell script well enough to > use it. Seems am slow this afternoon :-) Don't worry, I've just spent an hour going through three computers trying to figure out why they didn't work. In the end just decided to give them to someone else to fix. > On this list I saw a message detailing using copy as illustrated below > (see <code/>)when I run this command I get the following output (see > <output/>) > > > <output> > COPY abc FROM STDIN WITH CSV HEADER; > \. > 1 qrst a > 2 zvy b > </output> > > As you can see the ./ is placed a the top instead of the bottom of the > output. The does create some error when I run this output via psql. I'd expect postgres to try and interpret "1 qrst a" as an SQL command, this is obviously fail. > I then get a datatype error when I pass to psql the following (edited) > sql from a text editor (see <sql/>) > > <sql> > COPY abc FROM STDIN WITH CSV HEADER; > 1 qrst a > 2 zvy b > \. > </sql> > > The error reads as follows > <output2> > psql:sql/some2.sql:7: ERROR: invalid input syntax for integer: "1 > qrst a" > CONTEXT: COPY item_major, line 1, column id: "1 qrst a" > </output2> Sorry, I put a "CSV" mode into there as well. It's expecting commas between fields, not tabs. I'd only ever seen the "HEADER" option supported with CSV mode before, so had assumed this is what you wanted. Sam
Allan Kamau wrote: > Reid Thompson wrote: >> Allan Kamau wrote: >>> Sam, I have been unable to understand your shell script well enough >>> to use it. Seems am slow this afternoon :-) >>> >>> On this list I saw a message detailing using copy as illustrated >>> below (see <code/>)when I run this command I get the following output >>> (see <output/>) >>> >>> >>> <output> >>> COPY abc FROM STDIN WITH CSV HEADER; >>> \. >>> 1 qrst a >>> 2 zvy b >>> </output> >>> >>> As you can see the ./ is placed a the top instead of the bottom of >>> the output. The does create some error when I run this output via psql. >>> I then get a datatype error when I pass to psql the following >>> (edited) sql from a text editor (see <sql/>) >>> >>> <sql> >>> COPY abc FROM STDIN WITH CSV HEADER; >>> 1 qrst a >>> 2 zvy b >>> \. >>> </sql> >>> >>> The error reads as follows >>> <output2> >>> psql:sql/some2.sql:7: ERROR: invalid input syntax for integer: >>> "1 qrst a" >>> CONTEXT: COPY item_major, line 1, column id: "1 qrst a" >>> </output2> >>> >>> >>> >>> <code> >>> \echo 'COPY abc FROM STDIN WITH CSV HEADER;' >>> COPY >>> ( >>> SELECT * FROM abc >>> ) >>> to STDOUT >>> WITH delimiter E'\t' >>> \echo '\\.' >>> </code> >>> >>> >>> >>> >>> Sam Mason wrote: >>>> On Tue, Oct 28, 2008 at 01:25:00PM +0200, Allan Kamau wrote: >>>> >>>>> The alternative I am attempting is to use "COPY abc FROM STDIN WITH >>>>> HEADER". I pipe the contents of the CSV file on my PC to the psql >>>>> command (that connects to the remote PC) while issuing this copy >>>>> command. >>>>> This does seems not to work. >>>>> >>>> >>>> It does whenever I try it and if you've ever restored from a pg_dump >>>> then you've used it as well! >>>> >>>> >>>>> Is there a way around it. >>>>> >>>> >>>> When I've had a CSV file and needed to bung it into a database, I've >>>> tended to end up with shell scripts like this before: >>>> >>>> ( echo 'COPY abc FROM STDIN WITH CSV HEADER;' >>>> cat "$1" >>>> echo '\.' >>>> ) | psql >>>> >>>> an alternative is to use the "\copy" feature inside psql that does this >>>> sort of thing internally. One thing to be aware of is that it doesn't >>>> expect a semicolon at the end of the line, but is otherwise the same as >>>> the SQL COPY command. >>>> >>>> >>>> Sam >>>> >>>> >>> >>> >> are these space delimited values, or tab delimited values? >> 1 qrst a >> 2 zvy b > They are (tab delimited values), I've rechecked them, did a find and > replace for any space between the columns with a tab, the error persists. > > Allan. > > test=# COPY abc FROM STDIN WITH CSV HEADER; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1,qrst,a >> 2,zvy,b >> \. test=# select * from abc; id | strone | strtwo ----+--------+-------- 2 | zvy | b (1 row) test=#
Thanks Reid, Sam and others. Indeed the oversight was once again at my end. As rightfully pointed out I was using tab as a delimiter where the copy command "COPY abc FROM STDIN WITH CSV HEADER" expects a comma character and will ignore the first line. It does work appropriately when I pipe the two psql commands at the command line. The contents below are not important. I am however unable to do the same successfully (the Java code simply hangs, probably as a result of the second psql not getting the input to it) from Java code using objects of ProcessBuilder and Process. I have used threads consume the STDOUT and STDERR streams (I write the STDOUT stream to file) do the waitFor(), then I read the file contents and write them to STDIN stream of the second call to psql. I have therefore resorted to password-less ssh. So far all is well. Am writing to CSV file which I scp to the remote server then I issue another call to psql to connect to the remote server's PostgreSQL and execute an sql having a COPY abc FROM .. Allan. Sam Mason wrote: > On Tue, Oct 28, 2008 at 03:11:05PM +0200, Allan Kamau wrote: > >> Sam, I have been unable to understand your shell script well enough to >> use it. Seems am slow this afternoon :-) >> > > Don't worry, I've just spent an hour going through three computers > trying to figure out why they didn't work. In the end just decided to > give them to someone else to fix. > > >> On this list I saw a message detailing using copy as illustrated below >> (see <code/>)when I run this command I get the following output (see >> <output/>) >> >> >> <output> >> COPY abc FROM STDIN WITH CSV HEADER; >> \. >> 1 qrst a >> 2 zvy b >> </output> >> >> As you can see the ./ is placed a the top instead of the bottom of the >> output. The does create some error when I run this output via psql. >> > > I'd expect postgres to try and interpret "1 qrst a" as an SQL command, > this is obviously fail. > > >> I then get a datatype error when I pass to psql the following (edited) >> sql from a text editor (see <sql/>) >> >> <sql> >> COPY abc FROM STDIN WITH CSV HEADER; >> 1 qrst a >> 2 zvy b >> \. >> </sql> >> >> The error reads as follows >> <output2> >> psql:sql/some2.sql:7: ERROR: invalid input syntax for integer: "1 >> qrst a" >> CONTEXT: COPY item_major, line 1, column id: "1 qrst a" >> </output2> >> > > Sorry, I put a "CSV" mode into there as well. It's expecting commas > between fields, not tabs. I'd only ever seen the "HEADER" option > supported with CSV mode before, so had assumed this is what you wanted. > > > Sam > >
On Wed, Oct 29, 2008 at 12:11:43PM +0200, Allan Kamau wrote: > I am however unable to do the same successfully (the Java code simply > hangs, probably as a result of the second psql not getting the input to > it) from Java code using objects of ProcessBuilder and Process. Why don't you use the JDBC driver? You wouldn't have to worry about file descriptors blocking at all that way. Sam
> I am however unable to do the same successfully (the Java code simply > hangs, probably as a result of the second psql not getting the input to > it) from Java code using objects of ProcessBuilder and Process. I have > used threads consume the STDOUT and STDERR streams (I write the STDOUT > stream to file) do the waitFor(), then I read the file contents and > write them to STDIN stream of the second call to psql. > I have therefore resorted to password-less ssh. So far all is well. Am > writing to CSV file which I scp to the remote server then I issue > another call to psql to connect to the remote server's PostgreSQL and > execute an sql having a COPY abc FROM .. Why aren't you using \COPY from psql rather than COPY? With \COPY, you can execute the commands from your remote client without having to do the SSH stuff. It may run a bit longer but it is easier to maintain and looks to be more secure. Jon