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');
Best Answer
If that is too much boilerplate code, then create a function that does the job:
You can remove the
set not null
statement if you know that all your columns are already defined asNOT NULL
.To synchronize the sequence with the existing values, the above code essentially runs:
(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: