PostgreSQL assign sequence number from SELECT

Question

I want to restart sequence with max+1 of certain table.

SELECT max(id)+1 
    INTO testVal
FROM project;

ALTER SEQUENCE project_id_seq RESTART testVal;

This gives syntax error at testVal. Can someone please explain me what is a problem, propose alternative solution?

Lucas · Accepted Answer · 2018-12-10 10:04:13Z

2

for sequences you should be using setval

SELECT SETVAL('project_id_seq', (SELECT max(id)+1 FROM project))

https://www.postgresql.org/docs/current/functions-sequence.html

answered Dec 10, 2018 at 10:04

Lucas

6301 gold badge4 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eXPRESS Over a year ago

Oh, thats way cleaner, tested and seems to be working properly. Thank you. BTW would you know why my solution was not working? Just to take a lesson from it.

Lucas Over a year ago

Your ALTER SEQUENCE referred only to the table name, however it would require a subquery to select that column and I do not believe you could do that - also SELECT [columns] INTO [tablename] creates a table with that data inside but since you did not give max(id)+1 an alias it would default a name to something like ?column?

eXPRESS Over a year ago

Okay, I think I understand. Thanks

Collectives™ on Stack Overflow

PostgreSQL assign sequence number from SELECT

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related