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

Re: [INTERFACES] COPY syntax

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: emarsden(at)mail(dot)dotcom(dot)fr
Cc: pgsql-interfaces(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] COPY syntax
Date: 1998-08-13 16:57:14
Message-ID: 18008.903027434@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfaces
Eric Marsden <emarsden(at)mail(dot)dotcom(dot)fr> writes:
> Is it possible to use functions in
> COPY input? Specifically, I am trying to do this
>    COPY attributes FROM 'home/ecm/prog/database/attributes.data';
> where the file contains entries like (with tabs)
>    nextval('attribute_id_sequence')        Control and Monitoring  \N

Nope, won't work.  COPY handles literal data only --- if it were smart
enough to do SQL expressions then we wouldn't have to have this weird \N
kluge for null values.  (I think there are a few tiny exceptions, like
you could put "now" as the value for a datetime field and the expected
thing would happen during COPY IN.  That's because the literal-string-
to-data-value parser for type datetime implements that conversion all
by itself.  But nextval() is definitely an SQL expression.)

You should use a series of INSERT commands if you need to do SQL
calculations while inserting data.  For instance, I have an app that
uses nextval() to assign ID numbers like yours, and it issues tons
of commands like this:

INSERT INTO FEHistory_3 (accountID, instrumentID, orderType,
numContracts, orderTime, simStatus, realStatus, sequenceNo, orderPrice,
orderDivisor, ifDonePrice) VALUES(13, 120, 'S', 1, '1998-07-16 18:00:00 GMT',
'A', '-', nextval('FEHistory_3_Seq'), 10603, 100, 10574)

			regards, tom lane

pgsql-interfaces by date

Next:From: JohnDzDate: 1998-08-13 18:28:43
Subject: pltcl
Previous:From: Eric MarsdenDate: 1998-08-13 16:41:46
Subject: Re: [INTERFACES] COPY syntax

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