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 keys #

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)

Try it

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')
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

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')
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 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)

Try it

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.

Quiz #