Re: Sequence

Alan, Simon,

You can't do this, at least not safely.

Sequences can't be rolled back and are visible across transactions. In
other words if thread 1 inserted a row, and before you read the sequence
thread b inserted a row, you would get the same value for both threads.
The only way I know is to get the sequence before hand and insert it.
The overhead is the same.

Dave

On Sat, 2003-01-11 at 20:19, Simon Mitchell wrote:
> Alan,
> Just doing some test from psql prompt.
>
> After your insert into the table the id sequence is available by
>
> select currval('table_id_seq');
> currval
> ---------
> 5006
> (1 row)
>
>
> If your session has not done an insert you should get an error.
> select currval('table_id_seq');
> ERROR: table_id_seq.currval is not yet defined in this session
>
> Do a describe (\d) on your table to check seq name.
>
> So this is equivalent to MySql > last_insert_id() .
>
> ***************************************************
> If you wont the last value of the sequence (which could be your current
> session or another session) you can select it with
>
> SELECT last_value FROM table_id_seq;
> last_value
> ------------
> 5006
> (1 row)
>
> **This is not transaction safe as another session could have done an
> insert. Please use select currval('table_id_seq');**
> It is just nice to know.
> ***************************************************
>
> Simon
>
>
>
>
>
>
> Dave Cramer wrote:
>
> >Alan,
> >
> >You can't, get the sequence before and insert it.
> >
> >Dave
> >On Thu, 2003-01-09 at 14:35, Alan Roberto Romaniuc wrote:
> >
> >
> >>I have a table with sequence field (id).
> >>
> >>I insert a new row in this table, and I would like to get the sequence
> >>number that postgresql assign to id.
> >>
> >>How can I get it??? ... Max function is not nice.......
> >>
> >>jdbc2 ....
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 3: if posting/reading through Usenet, please send an appropriate
> >>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> >>message can get through to the mailing list cleanly
> >>
> >>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer <Dave(at)micro-automation(dot)net>