Using result of select inside alter table statement in postgres

Question

I have a postgres table defined as below:

CREATE TABLE public.Table_1
(
  id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 
  START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 )
)

Due to data migration, the id column is messed up and the value for id that is being generated on INSERT is not unique. Hence, I need to reset the id column as below

SELECT MAX(id) + 1 From Table_1;
ALTER TABLE Table_1 ALTER COLUMN id RESTART WITH 935074;

Right now I run the first query to get the Max(id) + 1 value and then I need to substitute it in the ALTER query. Is there a way to store the result of SELECT and just use the variable inside ALTER statement?

I'm a bit lost. Why do you want to set the value of an `id` column? Can you better describe the problem you are trying to solve? — Gordon Linoff, Jan 19 '20 at 14:44
So, I am migrating data into postgres and Id column is defined as an identity which is always generated. After the migration, Id column got messed up and it is not auto-generating unique values. So, as a fix I am trying to restart the sequence using the above 2 queries and I have to do this for about 20 tables. — Anshul Prakash, Jan 19 '20 at 14:47
If I understand your problem, you are trying to use a sub-query as part of an ALTER statement, which I don't believe is possible. However if the ID you are trying to adjust is a postgres sequence, you should be able to change the value. See this answer: https://stackoverflow.com/questions/2022559/postgresql-using-subqueries-with-alter-sequence-expressions — Barry Piccinni, Jan 19 '20 at 14:59
Thanks. That was useful. id field was defined as: id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ) So, I had to first get the sequence name using pg_get_serial_sequence function and then set the value using: select setval('table_1_id_seq', (select max(id)+1 from notificationrequest), false) — Anshul Prakash, Jan 19 '20 at 15:20

score 3 · Accepted Answer · answered Jan 19 '20 at 15:21

Here is one way to do it:

select setval(pg_get_serial_sequence('Table_1', 'id'), coalesce(max(id),0) + 1, false) 
from Table_1;

Rationale:

pg_get_serial_sequence() returns the name of the sequence for the given table and column
set_val() can be used to reset the sequence
this can be wrapped in a select that gives you the current maximum value of id in the table (or 1 if the table is empty)

Using result of select inside alter table statement in postgres

1 Answers1