pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbulk imports with sequence
3+ messages / 3 participants
[nested] [flat]
* bulk imports with sequence
@ 2002-08-20 11:55 Aaron Held <aaron@metrony.com>
0 siblings, 2 replies; 3+ messages in thread
From: Aaron Held @ 2002-08-20 11:55 UTC (permalink / raw)
To: pgsql-sql
I am importing a large number of records monthly using the \copy (from text file)command.
I would like to use a sequence as a unique row ID for display in my app.
Is there any way to let postgresql generate the sequence itself. Currently the only way I
can make it work is to grab the next seq value and insert my own numbers into the file
Thank You,
-Aaron Held
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: bulk imports with sequence
@ 2002-08-20 12:00 Dan Langille <dan@langille.org>
parent: Aaron Held <aaron@metrony.com>
1 sibling, 0 replies; 3+ messages in thread
From: Dan Langille @ 2002-08-20 12:00 UTC (permalink / raw)
To: Aaron Held <aaron@metrony.com>; +Cc: pgsql-sql
On 20 Aug 2002 at 7:55, Aaron Held wrote:
> I am importing a large number of records monthly using the \copy (from
> text file)command.
>
> I would like to use a sequence as a unique row ID for display in my
> app.
>
> Is there any way to let postgresql generate the sequence itself.
> Currently the only way I can make it work is to grab the next seq
> value and insert my own numbers into the file
Yes:
create sequence mytable_id_seq;
alter table mytable alter column id set default
nextval('mycolumn_id_seq'::text);
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: bulk imports with sequence
@ 2002-08-20 14:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aaron Held <aaron@metrony.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-08-20 14:53 UTC (permalink / raw)
To: Aaron Held <aaron@metrony.com>; +Cc: pgsql-sql
"Aaron Held" <aaron@metrony.com> writes:
> I am importing a large number of records monthly using the \copy (from text file)command.
> I would like to use a sequence as a unique row ID for display in my app.
> Is there any way to let postgresql generate the sequence itself. Currently the only way I
> can make it work is to grab the next seq value and insert my own numbers into the file
Right now the only reasonable way to do that is to do the \copy into a
temporary table (that's missing the sequence column) and then do
insert into realtable(column list) select * from temptable;
where the column list lists the columns you're pulling from the temp
table. The INSERT will substitute the default value (viz, nextval())
in the sequence column.
In 7.3 it'll be possible to do this in one step with no temp table:
COPY will accept a column list, so you can get the same effect just
with COPY.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-08-20 14:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-20 11:55 bulk imports with sequence Aaron Held <aaron@metrony.com>
2002-08-20 12:00 ` Dan Langille <dan@langille.org>
2002-08-20 14:53 ` 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