0

I'm newbie in Postgres. I use Pgadmin III and I need to parameterize a value in a statament of 'alter sequence'. I have tried to execute this code:

select coalesce(MAX(ID)+1,1) as max into myTempTable from myTable;
EXECUTE immediate 'ALTER SEQUENCE mySequence INCREMENT BY 1 START WITH ' || max || ' MINVALUE 1 NO CYCLE';

but it doesn't work. What's wrong? I need to encapsulate it in a function? Thank you. D.

django
  • 153
  • 1
  • 5
  • 19

2 Answers2

1

For setting value to sequence manually, you can use ALTER SEQUENCE seq_name RESTART WITH value

Regarding to your example, you need something like this:

CREATE SEQUENCE testseq;

DO $$
DECLARE
    maxid INT;
BEGIN
    SELECT coalesce(MAX(ID)+1,1) FROM myTable INTO maxid;
    EXECUTE 'ALTER SEQUENCE testseq RESTART WITH '|| maxid;
END;
$$ LANGUAGE PLPGSQL
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
0

No need for dynamic SQL.

Just create the sequence and then change the current value:

create sequence mysequence increment by 1 minvalue 1 no cycle;
select setval('mysequence', (select coalesce(MAX(id)+1,1) from mytable));

You probably also want to make that sequence the default for that column: 

alter table mytable alter column id
   set default nextval('mysequence');  

alter sequence mysequence owned by mytable.id;