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
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 primary key where the value of that coloumn is from the sequence?