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 basic syntax of a Upsert or ON CONFLICT is as follows:
INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT target action;
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:
Now, navigate to
http://localhost:8081/ to access phpMyAdmin. Then log in with the username
root and pass
Click the SQL tab and you are ready to go.
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');
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 : 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;