Postgresql - Using subqueries with alter sequence expressions

sql postgresql subquery sequence alter
Danmaxis picture Danmaxis · Jan 7, 2010 · Viewed 16.1k times · Source

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)

Answer

Arthur Thomas picture Arthur Thomas · Jan 7, 2010

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.

Related questions

updating table rows in postgres using subquery

Using postgres 8.4, My goal is to update existing table: CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, partner boolean ) WITH ( OIDS=FALSE ); Initially i tested my query …

Read More
How to do a Postgresql subquery in select clause with join in from clause like SQL Server?

I am trying to write the following query on postgresql: select name, author_id, count(1), (select count(1) from names as n2 where n2.id = n1.id and t2.author_id = t1.author_id ) from names as n1 group by name, …

Read More
subquery in FROM must have an alias

I have this query I have written in PostgreSQL that returns an error saying: [Err] ERROR: LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge This is the whole query: SELECT COUNT (made_only_recharge) AS made_only_recharge FROM ( …

Read More