Re: Piping CSV data to psql when executing COPY .. FROM STDIN (Solved)

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