Working with Upsert in Postgres

Intro

Postgresql doesn't provide an Upsert keyword, however, we can do upserts using the ON CONFLICT keyword. We add this keyword when inserting to specify how to handle upsert operations. In this article, we will learn how to use Upser in Postgresql.

The Syntax

The basic syntax of a Upsert or ON CONFLICT is as follows:

INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;

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.

Creating a DB

In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.

We will be using the sample db provided here: https://dev.mysql.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.

Next, let's create an film table. This is a slightly simplified version of the sakila database.

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date   DATE,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16),
    gender      VARCHAR(1),
    hire_date   DATE,
    PRIMARY KEY (emp_no)
);

Now, let's enter a few rows

INSERT INTO employees VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');

An Example

Let's start by trying to insert an employee where we already have an existing emp_no.

INSERT INTO employees(emp_no, first_name) 
VALUES(10001, 'George');

Because we are using an emp_no that already exists, we will get the following error.

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "employees_pkey"
  Detail: Key (emp_no)=(10001) already exists.

Our first option is to ignore the error and use DO NOTHING with the ON CONFLICT statement.

insert into employees(emp_no, first_name) 
values(10001, 'George')
on conflict do nothing;

This will simply skip our insert and not add our update.

Our next option is to use the SET keyword to tell psql how to handle our conflict. For our example, we will try to increment the index, although if this is not an insert at the end of the sequence, we will still get an error.

insert into employees(emp_no, first_name) 
values(10001, 'George')
on conflict (emp_no) do update set emp_no=excluded.emp_no + 1;

Notice that we use an excluded variable which is provided by the ON CONFLICT logic.

Our final example will conduct an upsert. We can use similar syntax to the above to use the excluded emp_no. This will allow us to update the row if it already exists.

insert into employees(emp_no, first_name) 
values(10001, 'George')
on conflict (emp_no) 
do update set emp_no=excluded.emp_no, first_name=excluded.first_name;

Now, we should have updated the first name on our existing record. Let's check.

select * from employees e where emp_no = 10001;