pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [INTERFACES] COPY syntax
2+ messages / 2 participants
[nested] [flat]
* Re: [INTERFACES] COPY syntax
@ 1998-08-13 16:41 Eric Marsden <emarsden@mail.dotcom.fr>
0 siblings, 1 reply; 2+ messages in thread
From: Eric Marsden @ 1998-08-13 16:41 UTC (permalink / raw)
To: pgsql-interfaces
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:
TL> COPY IN/OUT data needs to be handled slightly differently from
TL> data seen in SQL commands and SELECT results. In particular,
TL> COPY likes to represent null values as \N, which can be confused
TL> with a valid string value if you don't escape backslash.
I am struggling with this right at the moment (I'm new to databases,
I'm struggling with everything). 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
I get an ERROR: pg_atoi: error in "nextval(attribute_id_sequence)":
can't parse "nextval(attribute_id_sequence)". Should I be using
auto-generated numbers and setting the sequence to an initial value
instead?
--
Eric Marsden
emarsden @ mail.dotcom.fr
It's elephants all the way down
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [INTERFACES] COPY syntax
@ 1998-08-13 16:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Eric Marsden <emarsden@mail.dotcom.fr>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 1998-08-13 16:57 UTC (permalink / raw)
To: emarsden@mail.dotcom.fr; +Cc: pgsql-interfaces
Eric Marsden <emarsden@mail.dotcom.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1998-08-13 16:57 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-08-13 16:41 Re: [INTERFACES] COPY syntax Eric Marsden <emarsden@mail.dotcom.fr>
1998-08-13 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox