Обсуждение: 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.