Skip site navigation (1) Skip section navigation (2)

Re: no results for nextval() query?

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Steve Lefevre <lefevre(dot)10(at)osu(dot)edu>, pgsql-novice(at)postgresql(dot)org
Subject: Re: no results for nextval() query?
Date: 2007-06-12 23:37:08
Message-ID: 564436.83263.qm@web31807.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- 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.





In response to

Responses

pgsql-novice by date

Next:From: David MonarchiDate: 2007-06-13 00:04:01
Subject: unreasonable run time for vacuum analyze?
Previous:From: Andrew MacleanDate: 2007-06-12 23:32:32
Subject: Re: Joins within a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group