PostgreSQL - Create a new primary key auto-increment column
Method 1: Using the SERIAL
data type (Pre-PostgreSQL 10):
-- Assuming you have an existing table called "your_table"
-- Add an auto-increment column named "id" using SERIAL
ALTER TABLE your_table
ADD COLUMN id SERIAL PRIMARY KEY;
This command adds a new column named "id" to the existing table and sets it as the primary key with an auto-increment feature using the SERIAL
data type.
Method 2: Using the GENERATED
column feature (PostgreSQL 12 and later):
-- Assuming you have an existing table called "your_table"
-- Add an auto-increment column named "id" using GENERATED ALWAYS AS IDENTITY
ALTER TABLE your_table
ADD COLUMN id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY;
Last updated