Working with Identity Columns in Postgres

Intro

Postgresql provides the GENERATED AS IDENTITY constraint to allow users a way to create unique keys on data. This is similar to the SERIAL data type, but has a few extra options. In this article, we will learn how to use GENERATED AS IDENTITY in postgresql.

The Syntax

The basic syntax of a Sequences is as follows:

[column_name] [type]
GENERATED { ALWAYS | BY DEFAULT }
AS IDENTITY
[ ( sequence_option ) ]

Getting Setup

We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.

version: '3'
 
services:
  db:
    image: 'postgres:latest'
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: default_database
    volumes:
      - psqldata:/var/lib/postgresql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  psqldata:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

Generated Always Example

There are two types of ways to use the GENERATED AS IDENTITY. The first is GENERATED ALWAYS, which means postgresql will create a new value for the id column always and not allow you to insert values into the column.

Let's create a simple table to show this as an example.

CREATE TABLE employees (
  emp_no INT GENERATED ALWAYS AS IDENTITY,
  first_name VARCHAR(14) NOT NULL
);

Now let's insert a single row.

INSERT INTO employees (first_name)
VALUES ('jane');

Now we can select the data and see that Postgresql inserted a id for our row automatically.

SELECT * FROM employees e ;

If we attempt to insert our own ID, we will receive an error message.

insert into employees (emp_no, first_name)
values (2, 'jane');

SQL Error [428C9]: ERROR: cannot insert a non-DEFAULT value into column "emp_no"
  Detail: Column "emp_no" is an identity column defined as GENERATED ALWAYS.
  Hint: Use OVERRIDING SYSTEM VALUE to override.

Generated by Default Example

Let's redo the example above, except by using the GENERATED BY DEFAULT option.

First drop the previous table, then recreate using the following.

CREATE TABLE employees (
  emp_no INT GENERATED BY DEFAULT AS IDENTITY,
  first_name VARCHAR(14) NOT NULL
);

The difference here is that postgresql will allow us to insert our own values into the identity column.

Now let's insert a single row.

INSERT INTO employees (first_name)
VALUES ('jane');

Now we can select the data and see that Postgresql inserted a id for our row automatically.

SELECT * FROM employees e ;

If we attempt to insert our own ID, we will now be allowed.

INSERT INTO employees (emp_no, first_name)
VALUES (2, 'jane');