Re: [SQL] Getting sequence value after inserting many rows at a time
On Sat, Jan 11, 2003 at 12:25:47 -0600,
Maurício Sessue Otta <[EMAIL PROTECTED]> 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).
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] switching default integer datatype to int8 and "IN (...)"
Thanks Andy - this "solves" the problem(*1) on the first level, where I know how to quote the params, so they must be evaluated / casted. But when I get into the subselects, I don't know how to make postgres to cast / evaluate the results of the subselect, so it again does only sequential scan. How do I make postgres cast ( or evaluate? ) the subselect ?, so that when I do # explain _the_right_select_quesry_with_subselect_ I want to get (Index Scan): Index Scan using file_pkey on file (cost=0.00..5.01 rows=1 width=8) SubPlan -> Materialize (cost=37209.28..37209.28 rows=9535 width=8) -> Index Scan using parentid_name_idx on file (cost=0.00..37209.28 rows=9535 width=8) but now instead I'm getting with this: # explain select id from file where id in( select id from file where parentid ='355764'); I don't want to get (Seq Scan): (that's what I'm getting now with the above query) Seq Scan on file (cost=0.00..70956514802.83 rows=953478 width=8) SubPlan -> Materialize (cost=37209.28..37209.28 rows=9535 width=8) -> Index Scan using parentid_name_idx on file (cost=0.00..37209.28 rows=9535 width=8) What's the right _the_right_select_quesry_with_subselect_ with possibly several nested subselects ? Thanks, John (*1) PS: I guess the problem is that somehow postgres doesn't know by default that it should try to "cast" the results of the subselects into type that it is to be comparing it with. (which is int8). Is there a way to formulate the query to ask for the cast, perhaps explicitly ? Or is there a way to set a variable or some other condition which will tell postgres to perform this cast implicitly ? -- Thanx ! On Thu, 9 Jan 2003, Andrew J. Kopciuch wrote: > > but a select like this takes ages (long time): > > # select * from file where id = 1921773; > >id | name > >-+ > > 1921777 | icons > > > > I believe the reason is this : the numeric literal is first considered an int4 > becuase it falls within the range of int4 (-2147483648 to +2147483647). > > try quoting the literal like this: > > # select * from file where id = '1921773'; > > This forces the literal to be evaluated. If you do an explain on that query > ... you should see that the query planner uses the index as expected and that > the condition used on the index is using the literal value cast to a big int. > > > That's just my understanding anyway. > > > Andy -- -- Gospel of Jesus' kingdom = saving power of God for all who believe -- ## To some, nothing is impossible. ## http://Honza.Vicherek.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
