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

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.