PostgreSQL Primary Key

Summary: in this tutorial, you will learn about primary keys and how to use the PostgreSQL primary key constraint to create a primary key for a table.

Defining primary key

A primary key is a column or a set of columns uniquely identifying each row in a table. It ensures that every row has a distinct value in the primary key columns.

For example, the brand_id column is the primary key of the following brands table:

In this brands table, the brand_id 1 identifies the brand name Apple, 2 indicates the Samsung and 3 means Google. There are no duplicate numbers in the brand_id column.

In PostgreSQL, you use the PRIMARY KEY constraint to define a primary key for a table.

If a primary key is a single column, you define the PRIMARY KEY constraint as a column constraint by adding PRIMARY KEY keywords after the primary key column:

CREATE TABLE table_name(
   column1 data_type PRIMARY KEY,
   column2 data_type,
   column3 data_type,
   ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, column1 is the primary key column of the table.

When a primary key column has two or more columns, you can define the primary key as a table constraint:

CREATE TABLE table_name(
   column1 data_type,
   column2 data_type,
   column3 data_type,
   ...
   PRIMARY KEY (column1, column2)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the primary key includes column1 and column2. In other words, no two rows will have the same values in column1 and column2. When a primary key consists of two or more columns, it is called a composite primary key.

Adding a primary key to a table

If you have a table that does not have a primary key, you can add one using the following ALTER TABLE statement:

ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2, ...);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Auto-increment primary key

An auto-increment column, a popular choice for a primary key, due to its simplicity and efficiency. It automatically generates a unique number for each new row inserted into the table, eliminating the need for manual input and ensuring data uniqueness.

To define an auto-increment column in PostgreSQL, you use the GENERATED ALWAYS AS IDENTITY attribute as follows:

id INT GENERATED ALWAYS AS IDENTITYCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To define an auto-increment column as a primary key column, you add the PRIMARY KEY constraint:

id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEYCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note the use of SERIAL is less recommended due to permission and lack of integrity issues.

Creating PostgreSQL primary key examples

Let’s take some examples of defining primary keys.

Defining a primary key column for a table

The following example creates a table called with the brand_id as the primary key column:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(50)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since the brand_id column is auto-incremented, you don’t need to provide a value for that column.

The following statement inserts a new row into the brands table:

INSERT INTO brands(name)
VALUES ('Apple');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The statement inserts one row into the brands table with the id 1.

 brand_id | name
----------+-------
        1 | AppleCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you insert a new row, its id will be 2:

INSERT INTO brands(name)
VALUES ('Samsung');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

 brand_id |  name
----------+---------
        1 | Apple
        2 | SamsungCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating composite primary key example

A primary key may consist of two or more columns. For example, the following CREATE TABLE statement creates a new table product_tags whose primary key includes the product_id and tag_id columns:

CREATE TABLE product_tags (
  product_id INT,
  tag_id INT,
  PRIMARY KEY (product_id, tag_id)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

• A primary key is a column or a group of column that uniquely identify a row in a table.
• Use PostgreSQL PRIMARY KEY constraint to define a primary key for a table.
• Use the GENERATED ALWAYS AS IDENTITY PRIMARY KEY to define an auto-increment primary key.