PostgreSQL: starting a sequence at MAX(the_column)+1

Question

I want to add a sequence to a column that might already have data, so I'm trying to start it beyond whatever's already there. Assuming there already is data, I would like to have done it this way:

CREATE SEQUENCE my_sequence MINVALUE 1000000 START
    (SELECT MAX(id_column) FROM my_table) OWNED BY my_table.id_column;

but it keeps dying at ( claiming syntax error. It's like the start value has to be cold hard numbers--nothing symbolic.

Of course, an even better solution would be if the sequence could be intelligent enough to avoid duplicate values, since id_column has a unique constraint on it--that's why I'm doing this. But from what I can tell, that's not possible.

I also tried skipping the START and then doing:

ALTER SEQUENCE my_sequence RESTART WITH (SELECT max(id_column)+1 FROM my_table);

but, again, it doesn't seem like to symbolic start values.

I'm running PostgreSQL 9.4 but some of our customers are using stuff as primitive as 8.3.

Answer 1

You can't specify a dynamic value for the start value.

But you can set the value once the sequence is created:

CREATE SEQUENCE my_sequence MINVALUE 1000000 OWNED BY my_table.id_column;
select setval('my_sequence',  (SELECT MAX(id_column) FROM my_table));

Answer 2

You can restore you sequence by request:

select setval('my_sequence', (SELECT MAX(id_column) FROM my_table));

Applicable for Postgres 9.2.