Unless you missed something and the migration could be processed once more (with different parameters? I have never used AWS Migration Service but I presume it should preserve the serial
-iness of your columns...) you'll need to re-create the sequences too.
I encountered a similar situation a year or so ago, and wrote this answer on SO.
Here's the gist of it:
CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0)) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
This would create a sequence foo_a_seq
, with its nextval
being one higher than the max
value in foo.a
(or 1
if there is no foo
record).
I also went ahead and set up a Function
to quickly apply to all the tables/columns in need:
CREATE OR REPLACE FUNCTION make_into_serial(table_name TEXT, column_name TEXT) RETURNS INTEGER AS $$
DECLARE
start_with INTEGER;
sequence_name TEXT;
BEGIN
sequence_name := table_name || '_' || column_name || '_seq';
EXECUTE 'SELECT coalesce(max(' || column_name || '), 0) + 1 FROM ' || table_name
INTO start_with;
EXECUTE 'CREATE SEQUENCE ' || sequence_name ||
' START WITH ' || start_with ||
' OWNED BY ' || table_name || '.' || column_name;
EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name ||
' SET DEFAULT nextVal(''' || sequence_name || ''')';
RETURN start_with;
END;
$$ LANGUAGE plpgsql VOLATILE;
Use it like so:
SELECT make_into_serial('foo', 'a');