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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
