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

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