Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql - Using subqueries with alter sequence expressions

Is it possible to use subqueries within alter expressions in PostgreSQL?

I want to alter a sequence value based on a primary key column value.

I tried using the following expression, but it wouldn't execute.

alter sequence public.sequenceX restart with (select max(table_id)+1 from table) 
like image 786
Danmaxis Avatar asked Jan 07 '10 18:01

Danmaxis


People also ask

Can we use subqueries in alter statement?

You cannot. SQL and DDL are basically two separated languages. Your solution is correct.

How subquery works in PostgreSQL?

A subquery or Inner query or Nested query is a query within another PostgreSQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.


2 Answers

I don't believe you can do it like that but you should be able to use the setval function direction which is what the alter does.

select setval('sequenceX', (select max(table_id)+1 from table), false) 

The false will make it return the next sequence number as exactly what is given.

like image 117
Arthur Thomas Avatar answered Sep 27 '22 18:09

Arthur Thomas


In addition if you have mixed case object names and you're getting an error like this:

ERROR: relation "public.mytable_id_seq" does not exist 

... the following version using regclass should be useful:

select setval('"public"."MyTable_Id_seq"'::regclass, (select MAX("Id") FROM "public"."MyTable")) 
like image 45
Duncan Smart Avatar answered Sep 27 '22 18:09

Duncan Smart