Re: no results for nextval() query?

--- Steve Lefevre <lefevre(dot)10(at)osu(dot)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.