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