Re: Sequence

I am using the method Dave recommends successfully - do a select nextval('table_id_seq') and then use that value in your insert.

Tim

> -----Original Message-----
> From: Dave Cramer [mailto:Dave(at)micro-automation(dot)net]
> Sent: Saturday, January 11, 2003 8:42 PM
> To: Simon Mitchell
> Cc: Alan Roberto Romaniuc; pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] 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>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>