POSTGRESQL: How to set the last id of a tables as start value of a sequence

Question

For inserting values into a table, I need to read the last primary key value of that table and start my sequence from there. But I get an error. My query is as below:

    CREATE SEQUENCE serial START(
          SELECT cd.id + 1
          FROM cd
          ORDER BY cd.id DESC
          LIMIT 1);

    INSERT INTO cd(id, class)
      SELECT (nextval('serial'), (
                                   SELECT class_name 
                                   FROM another_table
                                   WHERE some_condition
                                 )
      FROM cr

DROP SEQUENCE IF EXISTS serial;

And the error is as below:

ERROR:  syntax error at or near "("
LINE 1: CREATE SEQUENCE serial START( SELECT cd.id + 1 FROM cd

How can I get the last value of the primary key and start my sequence from there?

I'm not allowed to alter table design, so I can not define a sequence for the primary key of the table.

score 0 · Answer 1 · answered Jun 07 '18 at 15:23

Well, I solved my problem:

BEGIN TRANSACTION;
CREATE SEQUENCE serial START 1;

    INSERT INTO cd(id, class)
      SELECT (nextval('serial') + (select max(id)+1 from cd), (
                                   SELECT class_name 
                                   FROM another_table
                                   WHERE some_condition
                                 )
      FROM cr

DROP SEQUENCE IF EXISTS serial;
COMMIT;

POSTGRESQL: How to set the last id of a tables as start value of a sequence

1 Answers1