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

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Piping CSV data to psql when executing COPY .. FROM STDIN
Date: 2008-10-28 15:45:19
Message-ID: 20081028154519.GX2459@frubble.xen.chris-lamb.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, Oct 28, 2008 at 03:11:05PM +0200, Allan Kamau wrote:
> Sam, I have been unable to understand your shell script well enough to
> use it. Seems am slow this afternoon :-)

Don't worry, I've just spent an hour going through three computers
trying to figure out why they didn't work. In the end just decided to
give them to someone else to fix.

> 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'd expect postgres to try and interpret "1 qrst a" as an SQL command,
this is obviously fail.

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

Sorry, I put a "CSV" mode into there as well. It's expecting commas
between fields, not tabs. I'd only ever seen the "HEADER" option
supported with CSV mode before, so had assumed this is what you wanted.

Sam

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sam Mason 2008-10-28 15:48:21 Re: How to know the password for the user 'postgres'
Previous Message Alvaro Herrera 2008-10-28 15:36:39 Re: Archiving WAL for 7.4.21