Thread: no results for nextval() query?

Hello all -

I'm having problems with a primary key column that has a sequence. I'm
trying to reserve a value using nextval ( I've also tried currval() ),
but my query returns no results. I was originally confused because I was
doing "SELECT nextval(column_name) FROM table" instead of "SELECT
nextval(sequence_name) FROM table". Then, I had the sequence name wrong,
so I got a "does not exist" error. However, now that I have the name
right -- and I think it's right because I don't get an error -- I get an
empty result set. What am I doing wrong? The sequence seems to be
working, because the primary key field defaults to the next value in the
sequence when I do an insert.

Steve Lefevre

--- Steve Lefevre <lefevre.10@osu.edu> wrote:
> I'm having problems with a primary key column that has a sequence. I'm
> trying to reserve a value using nextval ( I've also tried currval() ),
> but my query returns no results. I was originally confused because I was
> doing "SELECT nextval(column_name) FROM table" instead of "SELECT
> nextval(sequence_name) FROM table".


nextval is a stand-alone function and does not belong to a table.

Also nextval() does not operate on a column.  i.e. nextval( column_name ) is incorrect.

nextval() operates on a PostgreSQL entity called a sequence, which "acts" like a public variable
that is used to hold an ever increasing number.

if you view your table definition, notice what its DEFAULT value is for the column in question.
It is automatically assigned the next increasing value from
nextval('your_system_generated_sequence_name').

Remember that the serial data-type is just shorthand notation assigning a default value from
nextval().

http://www.postgresql.org/docs/8.2/interactive/datatype-numeric.html#DATATYPE-SERIAL

And also notice how default values work:


DEFAULT default_expr

    The DEFAULT clause assigns a default data value for the column whose column definition it
appears within. The value is any variable-free expression (subqueries and cross-references to
other columns in the current table are not allowed). The data type of the default expression must
match the data type of the column.

    The default expression will be used in any insert operation that does not specify a value for
the column. If there is no default for a column, then the default is null.

from: http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html

Regards,
Richard Broersma Jr.

Steve Lefevre wrote:
> Richard Broersma Jr wrote:
>> nextval is a stand-alone function and does not belong to a table.
>>
>> Also nextval() does not operate on a column.  i.e. nextval(
>> column_name ) is incorrect.
>>
>> nextval() operates on a PostgreSQL entity called a sequence, which
>> "acts" like a public variable
>> that is used to hold an ever increasing number.
>>
> So I can do "SELECT nextval('sequence_name')" or "SELECT
> currval('sequence_name')" ?
>
> I thought I had tried that at work, and got some kind of error. I'll
> look at it tomorow and report back.
>
I works for me:

mydb=# \ds
                 List of relations
 Schema |        Name         |   Type   |  Owner
--------+---------------------+----------+----------
 public | apple_apple_nbr_seq | sequence | postgres
(1 row)

mydb=# select * from nextval('apple_apple_nbr_seq');
 nextval
---------
       4
(1 row)

mydb=# select nextval('apple_apple_nbr_seq');
 nextval
---------
       5
(1 row)

mydb=# INSERT INTO Apple( variety ) VALUES( 'Washington Apple' )
RETURNING apple_nbr ;
 apple_nbr
-----------
         6
(1 row)

INSERT 0 1
mydb=#
mydb=# insert into apple_orders( order_nbr, apple_nbr, qty ) values (
default, currval('apple_apple_nbr_seq'), 200);
INSERT 0 1
mydb=# select * from apple_orders;
 order_nbr | apple_nbr | qty
-----------+-----------+-----
         1 |         6 | 200
(1 row)

mydb=#

Regards,
Richard Broersma Jr.