Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group