Thread: alter sequence + subqueries

The postgres-specific sql extension 'ALTER SEQUENCE' does not appear to support subqueries.<br /><br /> I have inserted
somedata into a table, including values in the primary key. I want to reset the associated sequence so that any further
recordsto be inserted into that table  resume numbering correctly. The statement I attempted was:<br /><br /> ALTER
SEQUENCEfoo_id_seq <br /> RESTART WITH (SELECT max(id) FROM foo);<br /><br /> Is this syntax correct and supported?<br
/><br/>  <br /> 

On Fri, Sep 16, 2005 at 06:36:48PM -0400, jeff sacksteder wrote:
> The postgres-specific sql extension 'ALTER SEQUENCE' does not appear to 
> support subqueries.
> 
> I have inserted some data into a table, including values in the primary key. 
> I want to reset the associated sequence so that any further records to be 
> inserted into that table resume numbering correctly. The statement I 
> attempted was:
> 
> ALTER SEQUENCE foo_id_seq 
> RESTART WITH (SELECT max(id) FROM foo);
> 
> Is this syntax correct and supported?

No, but you could call the setval() function instead ...

-- 
Alvaro Herrera -- Valdivia, Chile         Architect, www.EnterpriseDB.com
Officer Krupke, what are we to do?
Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")

On Fri, Sep 16, 2005 at 06:36:48PM -0400, jeff sacksteder wrote:
> ALTER SEQUENCE foo_id_seq 
> RESTART WITH (SELECT max(id) FROM foo);
> 
> Is this syntax correct and supported?

Not according to the documentation; for an alternative see the
example under "User Comments":

http://www.postgresql.org/docs/8.0/interactive/sql-altersequence.html

-- 
Michael Fuhr