12

I've got a script which copies table data from one DB to another, however the ID column is backed by a sequence so when a new record is inserted into the destination table the nextval(seq) is returning the wrong value.

I need to update the sequence so that it starts at the next available ID.

I want to do something like:

ALTER SEQUENCE seq_id RESTART WITH
    (SELECT MAX(id) FROM tbl);

The above produces a syntax error however.

Can anyone suggest an alternative approach?

pstanton
  • 35,033
  • 24
  • 126
  • 168

1 Answers1

9
DO $$
  SELECT INTO m MAX(id) FROM tbl;
  EXECUTE 'ALTER SEQUENCE seq_id RESTART WITH ' || m;
END$$;

or, better yet, see this question:

Community
  • 1
  • 1
J-16 SDiZ
  • 26,473
  • 4
  • 65
  • 84
  • thanks for the answer, i'm running this as an echo piped into psql .. can you tell me how to escape the whole command into one ? – pstanton Jul 04 '11 at 01:22
  • you can use the `setval` function rather than having to dynamically build a `alter sequence` statement; simpler, but doesn't make any real difference if you're doing these one-by-one. – araqnid Jul 04 '11 at 13:36