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

Allan Kamau wrote:
> 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.
>
>
test=# COPY abc FROM STDIN WITH CSV HEADER;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1,qrst,a
>> 2,zvy,b
>> \.
test=# select * from abc;
id | strone | strtwo
----+--------+--------
2 | zvy | b
(1 row)

test=#