Postgres Pro
Downloads
Home   >   mailing lists

Thread: currval, lastval, nextvar?

currval, lastval, nextvar?

From
Carol Walter
Date:
Hello,

I want write a query that will insert a record in a person table,
insert a record in a presentation table, and then insert a record into
a bridge table that contains the keys of both of the newly created
records from the other two tables.  According to the documentation,
takes the next number in the sequence and reports it.  Both currval
and lastval report the last value that was taken from the sequence,
but both will produce errors if a "nextval" was not called in the
current session.  Does writing a record that automatically updates a
sequence count as calling nextval?

Thanks,
Carol Walter

Re: currval, lastval, nextvar?

From
"A. Kretschmer"
Date:
In response to Carol Walter :
> Hello,
>
> I want write a query that will insert a record in a person table,
> insert a record in a presentation table, and then insert a record into
> a bridge table that contains the keys of both of the newly created
> records from the other two tables.  According to the documentation,
> takes the next number in the sequence and reports it.  Both currval
> and lastval report the last value that was taken from the sequence,
> but both will produce errors if a "nextval" was not called in the
> current session.  Does writing a record that automatically updates a
> sequence count as calling nextval?

Yes. You don't need call nextval.

You can do something like :

test=# create table t1(id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey"
for table "t1"
CREATE TABLE
test=*# create table t2(id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "t2_id_seq" for serial column "t2.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey"
for table "t2"
CREATE TABLE
test=*# create table brige (i1 int references t1, i2 int references t2);
CREATE TABLE
test=*# insert into t1 values(default);
INSERT 0 1
test=*# insert into t2 values(default);
INSERT 0 1
test=*# insert into brige values (currval('t1_id_seq'), currval('t2_id_seq'));
INSERT 0 1
test=*# select * from brige ;
 i1 | i2
----+----
  1 |  1
(1 Zeile)


--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: currval, lastval, nextvar?

From
Tom Lane
Date:
"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> Yes. You don't need call nextval.

Well, you do, but the point is that the default expression for a
serial column includes the nextval() call.  Look at the table with
\d, eg

postgres=# create table t1(id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
postgres=# \d t1
                         Table "public.t1"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t1_id_seq'::regclass)
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)

postgres=#

So if you do an insert that doesn't provide a value for that column,
the nextval is done implicitly.

            regards, tom lane

Re: currval, lastval, nextvar?

From
Andreas Kretschmer
Date:
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> > Yes. You don't need call nextval.
>
> Well, you do, but the point is that the default expression for a
> serial column includes the nextval() call.  Look at the table with
> \d, eg
>
> So if you do an insert that doesn't provide a value for that column,
> the nextval is done implicitly.

Right, i know.

You don't need call nextval _explicit_, okay?



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: currval, lastval, nextvar?

From
Tom Lane
Date:
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> So if you do an insert that doesn't provide a value for that column,
>> the nextval is done implicitly.

> Right, i know.

I know *you* know, Andreas, but this is the -novice list and I figured
a bit more explanation wouldn't be a bad thing.  There isn't actually
any hidden magic in this, if you know where to look, but newbies
probably don't know where to look.

            regards, tom lane

Re: currval, lastval, nextvar?

From
johnf
Date:
On Thursday 23 April 2009 07:24:26 am Carol Walter wrote:
>  Does writing a record that automatically updates a  
> sequence count as calling nextval?

Yes if the data type is serial.

--
John Fabiani

Re: currval, lastval, nextvar?

From
Carol Walter
Date:
Thank you all for the good information.  The three "insert" lines are
all I need.

Carol

On Apr 23, 2009, at 11:59 AM, A. Kretschmer wrote:

> In response to Carol Walter :
>> Hello,
>>
>> I want write a query that will insert a record in a person table,
>> insert a record in a presentation table, and then insert a record
>> into
>> a bridge table that contains the keys of both of the newly created
>> records from the other two tables.  According to the documentation,
>> takes the next number in the sequence and reports it.  Both currval
>> and lastval report the last value that was taken from the sequence,
>> but both will produce errors if a "nextval" was not called in the
>> current session.  Does writing a record that automatically updates a
>> sequence count as calling nextval?
>
> Yes. You don't need call nextval.
>
> You can do something like :
>
> test=# create table t1(id serial primary key);
> NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for
> serial column "t1.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "t1_pkey"
> for table "t1"
> CREATE TABLE
> test=*# create table t2(id serial primary key);
> NOTICE:  CREATE TABLE will create implicit sequence "t2_id_seq" for
> serial column "t2.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "t2_pkey"
> for table "t2"
> CREATE TABLE
> test=*# create table brige (i1 int references t1, i2 int references
> t2);
> CREATE TABLE
> test=*# insert into t1 values(default);
> INSERT 0 1
> test=*# insert into t2 values(default);
> INSERT 0 1
> test=*# insert into brige values (currval('t1_id_seq'),
> currval('t2_id_seq'));
> INSERT 0 1
> test=*# select * from brige ;
> i1 | i2
> ----+----
>  1 |  1
> (1 Zeile)
>
>
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

Re: currval, lastval, nextvar?

From
Andreas Wenk
Date:
johnf schrieb:
> On Thursday 23 April 2009 07:24:26 am Carol Walter wrote:
>>  Does writing a record that automatically updates a
>> sequence count as calling nextval?
>
> Yes if the data type is serial.
>

don't want to make a big discussion out of it - but serial is not really
a datatype but more an alias ... correct? As I remember correctly it was
not possible to use serial in psql in older versions because of that.
Please correct me if i am wrong ...

Cheers

Andy