Add auto increment to already existing primary key column PostgreSQL

Question

I have a database schema of the following table:

database=# \d person
              Table "public.person"
   Column    |         Type          | Modifiers
-------------+-----------------------+-----------
 person_id   | smallint              | not null
 fname       | character varying(20) |
 lname       | character varying(20) |
 eye_color   | color_enum            |
 birth_date  | date                  |
 street      | character varying(30) |
 city        | character varying(20) |
 state       | character varying(20) |
 country     | character varying(20) |
 postal_code | character varying(20) |

I want to add AUTO_INCREMENT in one ALTER statement the way we can do in MySQL

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

I have tried this in Postgres but I am getting this error:

ALTER TABLE person ALTER COLUMN person_id SERIAL;
ERROR:  syntax error at or near "SERIAL"

I have seen we can create a sequence in the following fashion

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
CREATE SEQUENCE test_id_seq OWNED BY test1.id;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
  UPDATE test1 SET id = nextval('test_id_seq');

But this is too much boilerplate code. Is there a one-line statement to add AUTO_INCREMENT to an existing column in Postgres?

Postgres Version: 9.6.16

Update

After doing the boilerplate code, I am trying to INSERT using the following query:

 INSERT INTO person
 (person_id, fname, lname, eye_color, birth_date)
 VALUES (null, 'William','Turner', 'BR', '1972-05-27');

ERROR:  null value in column "person_id" violates not-null constraint
DETAIL:  Failing row contains (null, William, Turner, BR, 1972-05-27, null, null, null, null, null).

Is there a workaround by which I can pass null values to the primary key where the value of that column is from the sequence?

Answer

Was able to insert using the following error:

INSERT INTO person(person_id, fname, lname, eye_color, birth_date)
       VALUES (nextval('person_id_seq'), 'William','Turner', 'BR', '1972-05-27');

Answer 1

If that is too much boilerplate code, then create a function that does the job:

create or replace function make_serial(p_table_schema text, p_table_name text, p_column_name text)
  returns void
as
$$
declare
  l_sql text;
  l_seq_name text;
  l_full_name text;
begin
  l_seq_name := concat(p_table_name, '_', p_column_name, '_seq');
  l_full_name := quote_ident(p_table_schema)||'.'||quote_ident(l_seq_name);
  
  execute format('create sequence %I.%I', p_table_schema, l_seq_name);
  execute format('alter table %I.%I alter column %I set default nextval(%L)', p_table_schema, p_table_name, p_column_name, l_seq_name);
  execute format('alter table %I.%I alter column %I set not null', p_table_schema, p_table_name, p_column_name);
  execute format('alter sequence %I.%I owned by %I.%I', p_table_schema, l_seq_name, p_table_name, p_column_name);
  execute format('select setval(%L, coalesce(max(%I),0)) from %I', l_full_name, p_column_name, p_table_name);
end;
$$
language plpgsql;

You can remove the set not null statement if you know that all your columns are already defined as NOT NULL.

To synchronize the sequence with the existing values, the above code essentially runs:

select setval('person_id_seq', max(person_id))
from person;

(Updating all rows and possibly changing their primary key values is the wrong approach to sync the sequence).

Then to change a table, all you need to do is:

select make_serial('public', 'person', 'person_id');

Answer 2

You have 2 options:

1. Before insert trigger to set person_id column with some sequence.

2. Set default value to sequence next value. Example is below.

   create table t1 (id int primary key , x int);
   
   insert into t1 values (1,1);
   
   create sequence t1_Seq start with 2;
   
   alter table t1 alter column id set  default nextval('t1_seq');
   
   insert into t1 (x) values (2);