Re: Piping CSV data to psql when executing COPY .. FROM STDIN - Mailing list pgsql-general

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