Обсуждение: going crazy with serial type

OK.  My background is in mysql, and I've been converting over to psql.  Just
by way of background.

I do !NOT! understand how the SERIAL type works.  I want something
like I had in mysql that would generate new, unique numbers, each time
I added a new record.  I want something that sits down, shuts up, and
just GIVES me the number on demand.  (I also want a guarantee that the
unique number is consecutive, and is never zero or negative.)  In short,
I want the AUTO_INCREMENT behavior.

But so far it's been one giant headache.  Tell me, how do I insert
new records into a table *without* specifying an actual number?  In
mysql it's just an empty field.  I have been unable to determine how
to do this in psql other than to ascertain it certainly isn't through
the same way.

I've been through the documentation, but for some reason, no one seems
to think a programmer would ever want functionality like mysql's
AUTO_INCREMENT, so as far as I can tell, it's not even addressed.

I'd appreciate any help.  I basically have a table:

create table mytable (mytable_id serial, a int, b int);

and

insert into mytable ('', 1, 2); is accepted but then following
insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"


Thanks,
--Cindy
--
ctmoore@uci.edu

Cindy <ctmoore@uci.edu> writes:

> OK.  My background is in mysql, and I've been converting over to psql.  Just
> by way of background.

We like to see that.  ;)

>                                      (I also want a guarantee that the
> unique number is consecutive, and is never zero or negative.)

Consecutive you're not going to get (rollbacks will cause gaps in the
sequence).  There are good reasone laid out in the docs as to why this
happens.  If you have to have consecutive values in all cases, there
are ways to do it that involve a little more work--see the mailing
list archives; it's been discussed several times.

> create table mytable (mytable_id serial, a int, b int);
>
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"

INSERT INTO mytable (a, b) VALUES (1,2);
INSERT INTO mytable (a, b) VALUES (5,6);

SELECT * FROM mytable;

In other words, a non-specified value for a column will cause the
DEFAULT value to be inserted.  Since SERIAL uses the DEFAULT
mechanism, it "just works".

Good luck!

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

> I've been through the documentation, but for some reason, no one seems
> to think a programmer would ever want functionality like mysql's
> AUTO_INCREMENT, so as far as I can tell, it's not even addressed.

Actually, it has it's own section in the manual:

http://www.us.postgresql.org/users-lounge/docs/7.1/user/datatype.html#DATATY
PE-SERIAL

As well as a two questions in the FAQ:

http://www.us.postgresql.org/docs/faq-english.html#4.15.1
http://www.us.postgresql.org/docs/faq-english.html#4.15.2

And even more information in the online book:

http://www.ca.postgresql.org/docs/aw_pgsql_book/node75.html
http://www.ca.postgresql.org/docs/aw_pgsql_book/node76.html

> I'd appreciate any help.  I basically have a table:
>
> create table mytable (mytable_id serial, a int, b int);
>
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"

Just a guess here, but it seems that '' is being cast into an integer as a
0, therefore your inserts end up as:

insert into mytable (0, 1, 2);
insert into mytable (0, 5, 6);

What you want to do is either leave out the serial field:

insert into mytable (a, b) values (1, 2);
insert into mytable (a, b) values (5, 6);

Or explicitly give the serial a NULL value:

insert into mytable (NULL, 1, 2);
insert into mytable (NULL, 5, 6);

Hope this helps,

Greg

I don't believe you have to enter the serial at all.

So instead of this.

insert into mytable ('', 1, 2);

Try this.

insert into mytable (a, b) VALUES (1, 2);

I think that will work the way you want. Although I am a beginner myself.

Good luck.


Cindy wrote:

>OK.  My background is in mysql, and I've been converting over to psql.  Just
>by way of background.
>
>I do !NOT! understand how the SERIAL type works.  I want something
>like I had in mysql that would generate new, unique numbers, each time
>I added a new record.  I want something that sits down, shuts up, and
>just GIVES me the number on demand.  (I also want a guarantee that the
>unique number is consecutive, and is never zero or negative.)  In short,
>I want the AUTO_INCREMENT behavior.
>
>But so far it's been one giant headache.  Tell me, how do I insert
>new records into a table *without* specifying an actual number?  In
>mysql it's just an empty field.  I have been unable to determine how
>to do this in psql other than to ascertain it certainly isn't through
>the same way.
>
>I've been through the documentation, but for some reason, no one seems
>to think a programmer would ever want functionality like mysql's
>AUTO_INCREMENT, so as far as I can tell, it's not even addressed.
>
>I'd appreciate any help.  I basically have a table:
>
>create table mytable (mytable_id serial, a int, b int);
>
>and
>
>insert into mytable ('', 1, 2); is accepted but then following
>insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"
>
>
>Thanks,
>--Cindy
>

--
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
cd /pub
more beer

On Thu, 31 Jan 2002, Cindy wrote:

> I do !NOT! understand how the SERIAL type works.  I want something
> like I had in mysql that would generate new, unique numbers, each time
> I added a new record.  I want something that sits down, shuts up, and
> just GIVES me the number on demand.  (I also want a guarantee that the
> unique number is consecutive, and is never zero or negative.)  In short,
> I want the AUTO_INCREMENT behavior.

You won't get that.  serial (and sequences) are guaranteed to give numbers
that haven't shown up in the sequence (note: you can still get duplicates
if you set values yourself, you can get around this with triggers - there
was a recent example on one of the mailing lists I believe) not
consecutive numbers due to concurrency concerns (if one transaction asks
for a number and then a second also asks for a number, you need to wait
for transaction one to commit or rollback before you can give the second a
number if you want to guarantee consecutive numbers).

> But so far it's been one giant headache.  Tell me, how do I insert
> new records into a table *without* specifying an actual number?  In
> mysql it's just an empty field.  I have been unable to determine how
> to do this in psql other than to ascertain it certainly isn't through
> the same way.
You don't insert a value into the field (see below).  There's a difference
between inserting an empty value or even a NULL and not inserting a value.

> I'd appreciate any help.  I basically have a table:
> create table mytable (mytable_id serial, a int, b int);
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"
insert into mytable (a,b) values (1,2);
insert into mytable (a,b) values (5,6);

"Gregory Wood" writes:
 >> I've been through the documentation, but for some reason, no one seems
 >> to think a programmer would ever want functionality like mysql's
 >> AUTO_INCREMENT, so as far as I can tell, it's not even addressed.
 >
 >Actually, it has it's own section in the manual:

Of course, making a statement like I did is about the fastest way to
find out what you missed :).  However:

 >http://www.us.postgresql.org/users-lounge/docs/7.1/user/datatype.html#DATATY
 >PE-SERIAL

Yes, but this does not give an example of how to use it.  Ie, what
I need to do to insert properly.  I found this, but it was not helpful,
other than to know I could (in theory) use SERIAL rather than AUTO_INCREMENT.

 >As well as a two questions in the FAQ:
 >
 >http://www.us.postgresql.org/docs/faq-english.html#4.15.1
 >http://www.us.postgresql.org/docs/faq-english.html#4.15.2

OK.  These are helpful, but didn't answer my question of how to make
the insert call unless you're trying to tell me I should call next val
in order to get the value to assign into the record myself.  That
seems to me a very backwards, two step, way to do it, and it doesn't
suprise me I didn't manage to find this.

 >And even more information in the online book:
 >
 >http://www.ca.postgresql.org/docs/aw_pgsql_book/node75.html
 >http://www.ca.postgresql.org/docs/aw_pgsql_book/node76.html

Aha.  Yes, that would have helped.  I never did stumble on the
www.ca.postgresql.org site, though.  Thanks for the reference.

 >Just a guess here, but it seems that '' is being cast into an integer
 >as a 0,

Looks like it.

 >What you want to do is either leave out the serial field:
...
 >Or explicitly give the serial a NULL value:

I'll try the null value!  I have rather more than two fields in
my actual table, of course, which would make listing them tedious.

Thanks!
--
Cindy
ctmoore@uci.edu

Le Jeudi 31 Janvier 2002 18:40, Cindy a écrit :

> create table mytable (mytable_id serial, a int, b int);
>
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"
>

insert into mytable (a,b) values (1,2);
insert into mytable (a,b) values (5,6);

>
> Thanks,
> --Cindy

"Gregory Wood" <gregw@com-stock.com> writes:
> [ lots of good advice snipped ]

> Or explicitly give the serial a NULL value:

> insert into mytable (NULL, 1, 2);
> insert into mytable (NULL, 5, 6);

Oops, struck out on your last at-bat :-(.  The above will insert NULLs.

The *only* way to get the default value inserted is not to specify
the column at all in the insert.  The way you suggested works:

> insert into mytable (a, b) VALUES (1, 2);

Another option is to arrange the columns of the table so that the
one(s) you typically default are at the end, and then you can leave
off the column name list in INSERT:

create table mytable (a int, b int, id serial);

insert into mytable values (1, 2);

However, a lot of people consider it good practice to explicitly write
out a column name list in every INSERT anyway.  The above shortcut will
come back to bite you if you ever rearrange the columns again.  An
INSERT with column names listed is relatively impervious to schema
rearrangements.

            regards, tom lane

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> You won't get that.  serial (and sequences) are guaranteed to give numbers
> that haven't shown up in the sequence (note: you can still get duplicates
> if you set values yourself, you can get around this with triggers -

The SERIAL type implicitly adds a UNIQUE index, so you don't need to
worry about uniqueness even if you sometimes manually insert values.

Stephan's correct that sequences do not guarantee consecutive numbers,
only distinct numbers (because numbers obtained by transactions that
later fail won't get recycled).  We consider that we have good and
sufficient reasons for doing it that way.

Cindy also mentioned a requirement that the numbers never be <= 0.
While the sequence won't generate such values (at least not with default
parameters), there wouldn't be anything stopping a manual insertion of
a value <= 0.  If it's really important to prevent that, she could add
something like CHECK (id > 0) to the column definition.

            regards, tom lane

OK, next question.  I'm trying to use nextval/currval and I'm getting
this:

search_info=# select currval('state_vectors_id_seq');
ERROR:  state_vectors_id_seq.currval is not yet defined in this session
search_info=# select id from state_vectors;
 id
----
  1
(1 row)


shouldn't the first select have returned 1?  The state_vectors_id_seq
is the sequence created by declaring id SERIAL in the table
state_vectors, and I've done one INSERT INTO state_vectors VALUES
(nextval('state_vectors_id_seq'), ... ); which worked fine and is
where the 1 comes from.  (I'm interested in finding out the value used
for the most recent insert, and currval seemed like a pretty painless
way of doing so.)

(I'm basing this on 4.15.2 of the postgresql faq kindly supplied
earlier.)

--CIndy

CREATE SEQUENCE test_seq

CREATE TABLE test
(
    test_id INTEGER DEFAULT NEXTVAL('test_seq'),
    test_name VARCHAR(64),
    CONSTRAINT test_pk PRIMARY KEY(test_id)
);

INSERT INTO test (test_name) VALUES ('Test Name');
INSERT INTO test (test_name) VALUES ('Test Name2');

This is what you are looking for i believe

The sequence number

Note i believe SERIAL just creates a sequence with the table name _seq and
then defaults that field to that value

Darren Ferguson

On Thu, 31 Jan 2002, Cindy wrote:

>
> OK.  My background is in mysql, and I've been converting over to psql.  Just
> by way of background.
>
> I do !NOT! understand how the SERIAL type works.  I want something
> like I had in mysql that would generate new, unique numbers, each time
> I added a new record.  I want something that sits down, shuts up, and
> just GIVES me the number on demand.  (I also want a guarantee that the
> unique number is consecutive, and is never zero or negative.)  In short,
> I want the AUTO_INCREMENT behavior.
>
> But so far it's been one giant headache.  Tell me, how do I insert
> new records into a table *without* specifying an actual number?  In
> mysql it's just an empty field.  I have been unable to determine how
> to do this in psql other than to ascertain it certainly isn't through
> the same way.
>
> I've been through the documentation, but for some reason, no one seems
> to think a programmer would ever want functionality like mysql's
> AUTO_INCREMENT, so as far as I can tell, it's not even addressed.
>
> I'd appreciate any help.  I basically have a table:
>
> create table mytable (mytable_id serial, a int, b int);
>
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"
>
>
> Thanks,
> --Cindy
> --
> ctmoore@uci.edu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Cindy,

You are quite close to the solution.  You already have the correct
datatype, now all you need is the correct syntax.

Here's an example:

CREATE TABLE foo (
    bar        SERIAL PRIMARY KEY,
    a        int,
    b        int,
);

Now insert into this table like this:

INSERT INTO foo (a, b) VALUES (1, 2);
INSERT INTO foo (a, b) VALUES (3, 4);

To verify that it works do this:

processdata=> SELECT * FROM foo;
 bar | a | b
-----+---+---
   1 | 1 | 2
   2 | 3 | 4
(2 rows)

Notice that I didn't specify a value for the bar column when I inserted
my values, this causes PostgreSQL to choose the default value (which in
this case is the nextval() from the sequence created by the serial
type).

Now, integers provided in this fashion are guaranteed to be unique each
time, but they aren't guaranteed to be consecutive.  PostgreSQL doesn't
try and reuse sequence numbers on failure so if you have a lot of failed
inserts you *will* see holes in your sequences.  This means that the
numbers might be (1, 2, 4, 5, 7) or something instead of (1, 2, 3, 4,
5).  Personally, I think that it is an acceptable price to pay for a
database where inserts don't lock up the entire table, but if your
numbers have to be consecutive it is something to think about.

Hope this was helpful,
Jason

On Thu, 2002-01-31 at 12:40, Cindy wrote:
>
> OK.  My background is in mysql, and I've been converting over to psql.  Just
> by way of background.
>
> I do !NOT! understand how the SERIAL type works.  I want something
> like I had in mysql that would generate new, unique numbers, each time
> I added a new record.  I want something that sits down, shuts up, and
> just GIVES me the number on demand.  (I also want a guarantee that the
> unique number is consecutive, and is never zero or negative.)  In short,
> I want the AUTO_INCREMENT behavior.
>
> But so far it's been one giant headache.  Tell me, how do I insert
> new records into a table *without* specifying an actual number?  In
> mysql it's just an empty field.  I have been unable to determine how
> to do this in psql other than to ascertain it certainly isn't through
> the same way.
>
> I've been through the documentation, but for some reason, no one seems
> to think a programmer would ever want functionality like mysql's
> AUTO_INCREMENT, so as far as I can tell, it's not even addressed.
>
> I'd appreciate any help.  I basically have a table:
>
> create table mytable (mytable_id serial, a int, b int);
>
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"
>
>
> Thanks,
> --Cindy
> --
> ctmoore@uci.edu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

For some reason unknown to myself if you have not initiated a nextval in
the same psql command line before calling currval then that is the error
msg you will get.

If you do nextval then a currval after it then that error will disappear

Darren Ferguson

On Thu, 31 Jan 2002, Cindy wrote:

>
>
> OK, next question.  I'm trying to use nextval/currval and I'm getting
> this:
>
> search_info=# select currval('state_vectors_id_seq');
> ERROR:  state_vectors_id_seq.currval is not yet defined in this session
> search_info=# select id from state_vectors;
>  id
> ----
>   1
> (1 row)
>
>
> shouldn't the first select have returned 1?  The state_vectors_id_seq
> is the sequence created by declaring id SERIAL in the table
> state_vectors, and I've done one INSERT INTO state_vectors VALUES
> (nextval('state_vectors_id_seq'), ... ); which worked fine and is
> where the 1 comes from.  (I'm interested in finding out the value used
> for the most recent insert, and currval seemed like a pretty painless
> way of doing so.)
>
> (I'm basing this on 4.15.2 of the postgresql faq kindly supplied
> earlier.)
>
> --CIndy
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

On Thu, 31 Jan 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > You won't get that.  serial (and sequences) are guaranteed to give numbers
> > that haven't shown up in the sequence (note: you can still get duplicates
> > if you set values yourself, you can get around this with triggers -
>
> The SERIAL type implicitly adds a UNIQUE index, so you don't need to
> worry about uniqueness even if you sometimes manually insert values.

Right, but you get the intermittent errors in that case since the serial
doesn't skip the values that would error (which you might expect if you're
guaranteeing distinct numbers in a column).  When looked at as what it is
(an integer with a default) it makes sense, but if you don't know that
it's a bit unexpected.

> OK, next question.  I'm trying to use nextval/currval and I'm getting
> this:
>
> search_info=# select currval('state_vectors_id_seq');
> ERROR:  state_vectors_id_seq.currval is not yet defined in this session
> search_info=# select id from state_vectors;
>  id
> ----
>   1
> (1 row)
>
>
> shouldn't the first select have returned 1?

Yes and/or no. The way currval is handled is a bit... weird at first glance.
It's that way for a reason though:

Lets say you have two users, Ann and Bob. Ann inserts a row, which receives
a value of 1. Bob inserts a row with a value of 2. Ann then inserts a row
into another table, which references that first table. Because of the
reference, she wants to use the id value that was just inserted by her,
which is 1. If currval just grabbed the last sequence value, she would be
inserting a value of 2, which actually refers to Bob's insert. Bad.

So the way currval works is it grabs the last value of nextval, as executed
by the USER (or more specifically, that user's connection, i.e. session).
This means that when Ann does her insert, the nextval increments to 1 (the
initial value) and her currval is 1. When Bob does his insert, his nextval
increments to 2 and he gets a currval of 2, while Ann still retains her
currval of 1 (since it was Bob's session that incremented nextval to 2 and
not hers). That way Ann can use the sequence value that she just inserted
elsewhere, without fear of mistakenly using Bob's id value.

Now, to go back to the "state_vectors_id_seq.currval is not yet definted in
this session" error you received. Since the currval is populated by the last
value of nextval in that session, it can only be used after nextval has been
called (either explicitly, or through a default serial value). In other
words, you can only read currval after you perform an INSERT, or explicitly
call nextval.

Greg

Cindy <ctmoore@uci.edu> writes:

> OK, next question.  I'm trying to use nextval/currval and I'm getting
> this:
>
> search_info=# select currval('state_vectors_id_seq');
> ERROR:  state_vectors_id_seq.currval is not yet defined in this session

You need to do an explicit or implicit 'nextval' in a transaction
before 'currval' will work in that transaction.  So you can do:

BEGIN WORK;
INSERT INTO mytable (a, b) VALUES (1,2);
SELECT nextval('mytable_id_seq');
<do something with it>
COMMIT WORK;

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Cindy wrote:

>OK, next question.  I'm trying to use nextval/currval and I'm getting
>this:
>
>search_info=# select currval('state_vectors_id_seq');
>ERROR:  state_vectors_id_seq.currval is not yet defined in this session
>search_info=# select id from state_vectors;
> id
>----
>  1
>(1 row)
>
>
>shouldn't the first select have returned 1?  The state_vectors_id_seq
>
Nope.  currval() is per session, so if you haven't called nextval()
during the current connection, you'll see exactly what you saw.

test=# create sequence testseq;
CREATE
test=# select currval('testseq');
ERROR:  testseq.currval is not yet defined in this session
test=# select nextval('testseq');
 nextval
---------
       1
(1 row)

test=# select currval('testseq');
 currval
---------
       1
(1 row)

test=# \q

$ psql test

Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# select currval('testseq');
ERROR:  testseq.currval is not yet defined in this session
test=#

-Fran

Cindy <ctmoore@uci.edu> writes:
> OK, next question.  I'm trying to use nextval/currval and I'm getting
> this:
> search_info=# select currval('state_vectors_id_seq');
> ERROR:  state_vectors_id_seq.currval is not yet defined in this session

currval is defined to return the value most recently returned by nextval
in the current session.  Thus, without a prior nextval, you get an error.
The reason for this is to make it useful in a situation where multiple
clients are concurrently obtaining values from the same sequence.

The 7.2 documentation set has been improved (or at least I'd like to
think so) over 7.1's description of sequence stuff.  You may find that
reading

http://developer.postgresql.org/docs/postgres/datatype.html#DATATYPE-SERIAL
http://developer.postgresql.org/docs/postgres/functions-sequence.html
http://developer.postgresql.org/docs/postgres/sql-createsequence.html

reduces your level of confusion.  (Or not, but at least we tried...)
Note that 7.1 does not have the int8-based sequences described there,
but other than that I believe all the info carries back to 7.1.

            regards, tom lane

On Thu, 31 Jan 2002, Cindy wrote:

> OK, next question.  I'm trying to use nextval/currval and I'm getting
> this:
>
> search_info=# select currval('state_vectors_id_seq');
> ERROR:  state_vectors_id_seq.currval is not yet defined in this session
> search_info=# select id from state_vectors;
>  id
> ----
>   1
> (1 row)
>
>
> shouldn't the first select have returned 1?  The state_vectors_id_seq
> is the sequence created by declaring id SERIAL in the table
> state_vectors, and I've done one INSERT INTO state_vectors VALUES
> (nextval('state_vectors_id_seq'), ... ); which worked fine and is
> where the 1 comes from.  (I'm interested in finding out the value used
> for the most recent insert, and currval seemed like a pretty painless
> way of doing so.)
>
> (I'm basing this on 4.15.2 of the postgresql faq kindly supplied
> earlier.)

Well, as you've run into, it doesn't quite work like that.  currval gives
you the most recently assigned number to your session, not the database as
a whole.  What are you trying to do with the id once you get it?

"Gregory Wood" <gregw@com-stock.com> writes:
> [ excellent explanation ]

Somebody ought to pull out the better parts of this thread and put
together an article for the techdocs.postgresql.org site...

            regards, tom lane


> Yes and/or no. The way currval is handled is a bit... weird at first glance.
> It's that way for a reason though:

> Lets say you have two users, Ann and Bob. Ann inserts a row, which receives
> a value of 1. Bob inserts a row with a value of 2. Ann then inserts a row
> into another table, which references that first table. Because of the
> reference, she wants to use the id value that was just inserted by her,
> which is 1. If currval just grabbed the last sequence value, she would be
> inserting a value of 2, which actually refers to Bob's insert. Bad.

> So the way currval works is it grabs the last value of nextval, as executed
> by the USER (or more specifically, that user's connection, i.e. session).
> This means that when Ann does her insert, the nextval increments to 1 (the
> initial value) and her currval is 1. When Bob does his insert, his nextval
> increments to 2 and he gets a currval of 2, while Ann still retains her
> currval of 1 (since it was Bob's session that incremented nextval to 2 and
> not hers). That way Ann can use the sequence value that she just inserted
> elsewhere, without fear of mistakenly using Bob's id value.

> Now, to go back to the "state_vectors_id_seq.currval is not yet definted in
> this session" error you received. Since the currval is populated by the last
> value of nextval in that session, it can only be used after nextval has been
> called (either explicitly, or through a default serial value). In other
> words, you can only read currval after you perform an INSERT, or explicitly
> call nextval.

> Greg

Darren Ferguson wrote:
> For some reason unknown to myself if you have not initiated a nextval in
> the same psql command line before calling currval then that is the error
> msg you will get.

    Slightly  wrong. This is the correct behaviour if you haven't
    called NEXTVAL in that session before.

    The reason is that the SQL standard  defines  CURRVAL  to  be
    last value returned by NEXTVAL "in this session".


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Jan Wieck <janwieck@yahoo.com> writes:
>     The reason is that the SQL standard  defines  CURRVAL  to  be
>     last value returned by NEXTVAL "in this session".

Sequences are in the SQL standard?  I'm not finding 'em there...

            regards, tom lane

Hi Cindy,

I'd also recommend you take a look at :

http://techdocs.postgresql.org/college/001_sequences/

As this is a CBT which explains things pretty well.

:-)

Regards and best wishes,

Justin Clift


Cindy wrote:
>
> OK, next question.  I'm trying to use nextval/currval and I'm getting
> this:
>
> search_info=# select currval('state_vectors_id_seq');
> ERROR:  state_vectors_id_seq.currval is not yet defined in this session
> search_info=# select id from state_vectors;
>  id
> ----
>   1
> (1 row)
>
> shouldn't the first select have returned 1?  The state_vectors_id_seq
> is the sequence created by declaring id SERIAL in the table
> state_vectors, and I've done one INSERT INTO state_vectors VALUES
> (nextval('state_vectors_id_seq'), ... ); which worked fine and is
> where the 1 comes from.  (I'm interested in finding out the value used
> for the most recent insert, and currval seemed like a pretty painless
> way of doing so.)
>
> (I'm basing this on 4.15.2 of the postgresql faq kindly supplied
> earlier.)
>
> --CIndy
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi