xxxxxxxxxx
CREATE TABLE table_name(
id SERIAL
);
xxxxxxxxxx
CREATE TABLE epictable
(
mytable_key serial primary key,
moobars VARCHAR(40) not null,
foobars DATE
);
xxxxxxxxxx
-- SERIAL keyword auto increments the value
CREATE TABLE Colleges (
college_id INT SERIAL,
college_code VARCHAR(20) NOT NULL,
college_name VARCHAR(50),
CONSTRAINT CollegePK PRIMARY KEY (college_id)
);
-- inserting record without college_id
INSERT INTO Colleges(college_code, college_name)
VALUES ("ARD13", "Star Public School");
xxxxxxxxxx
ALTER TABLE temp ALTER COLUMN id
ADD GENERATED BY DEFAULT AS IDENTITY
xxxxxxxxxx
By simply setting our id column as SERIAL with PRIMARY KEY attached, Postgres will handle all the complicated behind-the-scenes work and automatically increment our id column with a unique, primary key value for every INSERT .