Re: automatic sequence number?

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Alessandro Polverini <polverini(at)nibbles(dot)it>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: automatic sequence number?
Date: 2003-11-04 18:05:22
Message-ID: 1067969122.1622.150.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Getting the generated keys out of a postgres database is not trivial,
the SERIAL type is really a manufactured type which uses a trigger, and
sequences to do it's work.

Rod's suggestion of doing an insert and retrieving the current value of
the sequence is really the fastest way to get it out. Curval does the
right thing ie it will get the value for this connection even if another
connection has incremented it before you retrieve it.

Dave

On Tue, 2003-11-04 at 12:25, Alessandro Polverini wrote:
> On Sun, 2003-11-02 at 15:28, apnv3(at)muha(dot)net wrote:
> > Hello.
> >
> > I have a table with one field acting as primary key, the key is
> > retrieved from a sequence-generator in postgresql.
> > Currently I am executing two queries for every insert;
> >
> > Query #1 retrieves a number from a sequence-generator, and places it into the
> > record.
> >
> > Query #2 performs the actual insert.
> >
> > Does anyone have an idea how to cut this down to only one query?
> > And maybe somehow get back the primary key in the ResultSet.
> >
> > Or maybe this is how it is supposed to be done, it is just that I am not
> > very experienced in jdbc/sql to know. :)
>
> JDBC has native support for that with the method
> Statement.getGeneratedKeys(), only I'm unsure postgresql jdbc driver
> implements it (actually, querying DataBaseMetaData it says no).
>
> I've asked the list some time ago for that information but I got no
> response :(
>
> Bye,
> Alex
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2003-11-05 01:39:38 patch: don't use cursors with scrollable resultsets
Previous Message Alessandro Polverini 2003-11-04 17:25:07 Re: automatic sequence number?