Adding serial to existing column in Postgres

Question

I have a small table   30 rows  in my Postgres 9 0 database with an integer ID field  the primary key  which currently contains unique sequential integers starting at 1  but which was not created using the  serial  keyword   How can I alter this table such that from now on inserts to this table will cause this field to behave as if it had been created with  serial  as a type

User · Accepted Answer

Look at the following commands  especially the commented block    DROP TABLE foo  DROP TABLE bar   CREATE TABLE foo  a int  b text   CREATE TABLE bar  a serial  b text    INSERT INTO foo  a  b  SELECT i   foo      i  text FROM generate series 1  5  i  INSERT INTO bar  b  SELECT  bar      i  text FROM generate series 1  5  i   -- blocks of commands to turn foo into bar CREATE SEQUENCE foo a seq  ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval  foo a seq    ALTER TABLE foo ALTER COLUMN a SET NOT NULL  ALTER SEQUENCE foo a seq OWNED BY foo a     -- 8 2 or later  SELECT MAX a  FROM foo  SELECT setval  foo a seq   5    -- replace 5 by SELECT MAX result  INSERT INTO foo  b  VALUES  teste    INSERT INTO bar  b  VALUES  teste     SELECT   FROM foo  SELECT   FROM bar

User · Answer

You can also use START WITH to start a sequence from a particular point  although setval accomplishes the same thing  as in Euler s answer  eg    SELECT MAX a    1 FROM foo  CREATE SEQUENCE foo a seq START WITH 12345  -- replace 12345 with max above ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval  foo a seq

User · Answer

TL DR Here s a version where you don t need a human to read a value and type it out themselves  CREATE SEQUENCE foo a seq OWNED BY foo a  SELECT setval  foo a seq   coalesce max a   0    1  false  FROM foo  ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval  foo a seq      Another option would be to employ the reusable Function shared at the end of this answer   A non-interactive solution Just adding to the other two answers  for those of us who need to have these Sequences created by a non-interactive script  while patching a live-ish DB for instance  That is  when you don t wanna SELECT the value manually and type it yourself into a subsequent CREATE statement  In short  you can not do  CREATE SEQUENCE foo a seq     START WITH   SELECT max a    1 FROM foo         since the START  WITH  clause in CREATE SEQUENCE expects a value  not a subquery   Note  As a rule of thumb  that applies to all non-CRUD  i e   anything other than INSERT  SELECT  UPDATE  DELETE  statements in pgSQL AFAIK   However  setval   does  Thus  the following is absolutely fine  SELECT setval  foo a seq   max a   FROM foo   If there s no data and you don t  want to  know about it  use coalesce   to set the default value  SELECT setval  foo a seq   coalesce max a   0   FROM foo  --                                            --                       defaults to        0  However  having the current sequence value set to 0 is clumsy  if not illegal  Using the three-parameter form of setval would be more appropriate  --                                             vvv SELECT setval  foo a seq   coalesce max a   0    1  false  FROM foo  --                                                        --                                                is called  Setting the optional third parameter of setval to false will prevent the next nextval from advancing the sequence before returning a value  and thus   the next nextval will return exactly the specified value  and sequence advancement commences with the following nextval       from this entry in the documentation On an unrelated note  you also can specify the column owning the Sequence directly with CREATE  you don t have to alter it later  CREATE SEQUENCE foo a seq OWNED BY foo a   In summary  CREATE SEQUENCE foo a seq OWNED BY foo a  SELECT setval  foo a seq   coalesce max a   0    1  false  FROM foo  ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval  foo a seq       Using a Function Alternatively  if you re planning on doing this for multiple columns  you could opt for using an actual Function  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  INSERT INTO foo  data  VALUES   asdf    -- ERROR  null value in column  quot a quot  violates not-null constraint  SELECT make into serial  foo    a    INSERT INTO foo  data  VALUES   asdf    -- OK  1 row s  affected

[postgresql] Adding 'serial' to existing column in Postgres

Examples related to postgresql