WikiGalaxy
PostgreSQL Identity Column
Introduction to Identity Columns
Identity columns in PostgreSQL are used to automatically generate unique numeric values for a column. They are similar to auto-increment fields in other databases.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50)
);
Using GENERATED ALWAYS AS IDENTITY
The GENERATED ALWAYS AS IDENTITY option ensures that the identity column is always populated with a unique value generated by the system.
CREATE TABLE books (
book_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title VARCHAR(100)
);
Using GENERATED BY DEFAULT AS IDENTITY
This option allows manual insertion of values into the identity column, but defaults to system-generated values if none are provided.
CREATE TABLE orders (
order_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_date DATE
);
Customizing Sequence Options
You can customize the behavior of the identity column's sequence, including its starting value and increment.
CREATE TABLE products (
product_id INT GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10) PRIMARY KEY,
product_name VARCHAR(100)
);
Altering an Identity Column
You can alter an existing table to add an identity column, which will automatically generate unique values.
ALTER TABLE employees
ADD COLUMN employee_id INT GENERATED ALWAYS AS IDENTITY;
Resetting the Sequence
You can reset the sequence associated with an identity column to restart numbering from a specified value.
ALTER SEQUENCE employees_employee_id_seq RESTART WITH 1;
Using Identity Columns in Joins
Identity columns can be used in join operations to uniquely identify records across tables.
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
Identity Columns with Constraints
Identity columns can be combined with other constraints like UNIQUE and NOT NULL to ensure data integrity.
CREATE TABLE customers (
customer_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(100) UNIQUE NOT NULL
);
Dropping an Identity Column
You can drop an identity column from a table if it is no longer needed.
ALTER TABLE customers DROP COLUMN customer_id;
