Thread: sequence

What is wrong here?

insert into table_name (field_name) values (select
setval('sequence_name')-1) as currval);

On Fri, 15 Aug 2003, cristi wrote:

> What is wrong here?
> 
> insert into table_name (field_name) values (select
> setval('sequence_name')-1) as currval);

Your probably want this instead:
 insert into table_name (field_name) values (nextval('sequence_name'));

The reason why your insert fail above is that setval() should have more 
parameters, but even if it had worked it does not make sense to call 
setval() there. See
 http://www.postgresql.org/docs/7.3/static/functions-sequence.html

Also, it's easier to use a serial column:
 http://www.postgresql.org/docs/7.3/static/datatype.html#DATATYPE-SERIAL

then you can do
 insert into table_name (field_name) values (DEFAULT);

-- 
/Dennis

On Fri, Aug 15, 2003 at 12:32:36PM +0300, cristi wrote:
> What is wrong here?
> 
> insert into table_name (field_name) values (select
> setval('sequence_name')-1) as currval);
This should be better:
INSERT INTO table_name (field_name) VALUES (nextval('sequence_name'));

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage

"cristi" <cristi@dmhi.ct.ro> writes:
> What is wrong here?
> insert into table_name (field_name) values (select
> setval('sequence_name')-1) as currval);

Either too few parentheses, or too many ;-)

You could write this as an INSERT/SELECT:

insert into table_name (field_name) select setval('sequence_name')-1 as currval;

or you could write it as an INSERT/VALUES with scalar subquery
expression:

insert into table_name (field_name) values ((select setval('sequence_name')-1 as currval));

(all the parentheses are required here).  But really you do not need
a subquery for this at all; VALUES is perfectly content with scalar
expressions:

insert into table_name (field_name) values (setval('sequence_name')-1);
        regards, tom lane