Skip site navigation (1) Skip section navigation (2)

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

From: Allan Kamau <allank(at)sanbi(dot)ac(dot)za>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Piping CSV data to psql when executing COPY .. FROM STDIN (Solved)
Date: 2008-10-29 10:11:43
Message-ID: 490836DF.4040905@sanbi.ac.za (view raw or flat)
Thread:
Lists: pgsql-general
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
>
>   


In response to

Responses

pgsql-general by date

Next:From: Isak HansenDate: 2008-10-29 10:20:11
Subject: Re: postgresql and Mac OS X
Previous:From: Dave PageDate: 2008-10-29 08:54:33
Subject: Re: using plpgsql debuggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group