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

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