Categories
Database Development

Add auto increment to already existing column PostgreSQL

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

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

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

Leave a Reply

Your email address will not be published. Required fields are marked *