pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting sequence value after inserting many rows at a time
3+ messages / 2 participants
[nested] [flat]
* Getting sequence value after inserting many rows at a time
@ 2003-01-10 00:48 Maurício Sessue Otta <mauricio@cristorei.com.br>
0 siblings, 1 reply; 3+ messages in thread
From: Maurício Sessue Otta @ 2003-01-10 00:48 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a PHP script that do something like this:
INSERT INTO table_with_sequence (field1, field2...., fieldn)
SELECT field1, field2, ..., fieldn FROM table,table
WHERE condition, condition
My doubt:
Will the rows just inserted in "table_with_sequence" always be sequencial?
(won't it happen to have X rows from this INSERT, 1 rows from another INSERT
in the middle and the rest rows of the first INSERT?)
How can I safely get the first value the INSERT "generated" for
the sequence?
[]'s Mauricio
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting sequence value after inserting many rows at a time
@ 2003-01-10 01:25 Bruno Wolff III <bruno@wolff.to>
parent: Maurício Sessue Otta <mauricio@cristorei.com.br>
0 siblings, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2003-01-10 01:25 UTC (permalink / raw)
To: Maurício Sessue Otta <mauricio@cristorei.com.br>; +Cc: pgsql-sql
On Thu, Jan 09, 2003 at 22:48:11 -0200,
Maurício Sessue Otta <mauricio@cristorei.com.br> wrote:
> Hi,
>
> I have a PHP script that do something like this:
>
> INSERT INTO table_with_sequence (field1, field2...., fieldn)
> SELECT field1, field2, ..., fieldn FROM table,table
> WHERE condition, condition
>
> My doubt:
> Will the rows just inserted in "table_with_sequence" always be sequencial?
> (won't it happen to have X rows from this INSERT, 1 rows from another INSERT
> in the middle and the rest rows of the first INSERT?)
>
> How can I safely get the first value the INSERT "generated" for
> the sequence?
You might want to reconsider the design. You might want to use another
serial column to indicate the grouping. This will allow you to pick out
records inserted as a group.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting sequence value after inserting many rows at a time
@ 2003-01-11 18:45 Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2003-01-11 18:45 UTC (permalink / raw)
To: Maurício Sessue Otta <mauricio@cristorei.com.br>; +Cc: pgsql-sql
On Sat, Jan 11, 2003 at 12:25:47 -0600,
Maurício Sessue Otta <mauricio@cristorei.com.br> wrote:
> Hi,
>
> I have a PHP script that do something like
> this:
>
> INSERT INTO table_with_sequence (field1,
> field2...., fieldn)
> SELECT field1, field2, ..., fieldn FROM table,table
>
> WHERE condition, condition
>
> My doubt:
> Will the rows just inserted in
> "table_with_sequence" always be sequencial?
> (won't it happen to have X rows from this
> INSERT, 1 rows from another INSERT
> in the middle and the rest rows of the first
> INSERT?)
>
> How can I safely get the first value the
> INSERT "generated" for
> the sequence?
I wanted to expound on my previous answer some more.
In general sequences are designed to provide an efficient way to generate
unique ids. If you are trying to get any more than uniqness out of them
you need to be careful. And in being careful, you may need to do things
that will make them less efficient.
In particular there can be gaps in sequences and they only have a loose
correlation with time.
I suspect that what you are trying to do is label a group in your example.
This is based on the insert not have a guarenteed order and your concern
about other transactions using sequence numbers between the lowest and
highest numbers used in a specif transaction.
My suggestion is to instead use one sequence number for the whole group.
A sketched example follows:
INSERT INTO newtable (groupid, field1, field2...., fieldn)
SELECT a.groupid, b.field1, b.field2, ..., b.fieldn
FROM (SELECT nextval('groupid_seq') as groupid) a,
(SELECT * FROM oldtable WHERE condition, condition) b
The groupid will be the same for each row inserted by a single instance
of this insert statement. Following sql statements can use
currval('groupid_seq') to get the value used for this insert statement.
Doing things this way will probably make other parts of what you are doing
easier. In particular getting records from a specific group will be much
simpler if they all have the same groupid as opposed to all groupids within
a specific range (that will somehow need to be tracked).
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-11 18:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-10 00:48 Getting sequence value after inserting many rows at a time Maurício Sessue Otta <mauricio@cristorei.com.br>
2003-01-10 01:25 ` Bruno Wolff III <bruno@wolff.to>
2003-01-11 18:45 Re: Getting sequence value after inserting many rows at a time Bruno Wolff III <bruno@wolff.to>
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