Piping CSV data to psql when executing COPY .. FROM STDIN

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:

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

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:

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=#

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

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 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