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