Skip to content
All glossary terms

Primary key

What is a primary key in PostgreSQL?

A primary key is a column or set of columns that uniquely identifies each row. PostgreSQL enforces it with a unique B-tree index and NOT NULL.

A primary key constraint marks a column, or a group of columns, as the identifier for the rows of a table: its values must be unique and not null. A table can have at most one primary key.

Adding a primary key automatically creates a unique B-tree index on its columns and marks them NOT NULL. Foreign keys in other tables usually reference it, and many tools expect every table to have one. Common choices are an identity column or a natural key such as a product code.

Example

The example creates a table keyed on sku, inserts a duplicate key with ON CONFLICT DO NOTHING so it is skipped instead of failing, and lists the index PostgreSQL created for the key.

CREATE TABLE products (
  sku text PRIMARY KEY,
  name text NOT NULL
);
INSERT INTO products VALUES ('P-1', 'Lamp');
INSERT INTO products VALUES ('P-1', 'Desk') ON CONFLICT DO NOTHING;

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'products';

Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.

Sources

Official PostgreSQL documentation (in English).

Related terms