Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux