UPSERT Postgres 9.5

Question

I am using Postgres 9.5 on CentOS 6. I have the following table (DDL).

CREATE TABLE core_table.user_list(
  user_id SMALLSERIAL DEFAULT nextval('core_table.user_list_user_id_seq'::regclass) NOT NULL,
  user_name VARCHAR(50) NOT NULL,
  full_name VARCHAR(255),
  role_id INTEGER DEFAULT 1,
  is_accessible INTEGER DEFAULT 1,
  remarks VARCHAR(255),
  password VARCHAR(50),
  customer_schema VARCHAR(50),
  core_schema VARCHAR(50),
  CONSTRAINT user_list_pkey PRIMARY KEY(user_id, user_name),
  CONSTRAINT user_list_user_id_key UNIQUE(user_id)
) 
WITH (oids = false);

with 4 existing rows of data.

Row 1:
user_id: 1
user_name: kelly
full_name: kelly petz
role_id: 1
is_accessible: 1
remarks: <null>
password: test123
customer_schema: kelly_db1
core_schema: kelly_db1

Row 2:
user_id: 2
user_name: kelly
full_name: kelly petz
role_id: 1
is_accessible: 1
remarks: <null>
password: test123
customer_schema: petz_db1
core_schema: kelly_db1

Row 3:
user_id: 3
user_name: sam
full_name: sam howiz
role_id: 1
is_accessible: 1
remarks: <null>
password: test456
customer_schema: kelly_db1
core_schema: kelly_db1

Row 4:
user_id: 4
user_name: jon
full_name: jon lam
role_id: 1
is_accessible: 1
remarks: <null>
password: test789
customer_schema: lam_db1
core_schema: lam_db1

I hit an error when I tried to do the following UPSERT.

INSERT INTO core_table.user_list 
(user_name, full_name, remarks, password, customer_schema, core_schema, role_id, is_accessible) 
VALUES 
('kelly', 'kelly petz', 'The Boss', 'test123', 'snoppy_db1', 'snoppy_db1', 1, 1) 
ON CONFLICT (user_name) 
DO UPDATE SET 
    password = 'test123', 
    remarks = 'The Boss';

The error is "ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification".

Can someone please point out my error for me and how to resolve it?

I am sorry i forgot to mention what I want to achieve. Basically, i want to check if the user and customer_schema exist in the records. If it exists, it updates the password and remarks fields. Otherwise it adds in a new record. — Christopher Lim, Jun 02 '16 at 07:54

Denys Séguret · Answer 1 · 2016-06-02T08:02:50.733

If you don't have any constraint on the user_name column, no conflict can happen, the on conflict (user_name) makes no sense.

You need some kind of unicity constraint here on the user_name column.

For example

CREATE UNIQUE INDEX user_list_name_index on  user_list (user_name);

EDIT following comment:

It looks like you want this table schema:

CREATE TABLE core_table.user_list(
  user_id SMALLSERIAL DEFAULT nextval('core_table.user_list_user_id_seq'::regclass) NOT NULL,
  user_name VARCHAR(50) NOT NULL,
  full_name VARCHAR(255),
  role_id INTEGER DEFAULT 1,
  is_accessible INTEGER DEFAULT 1,
  remarks VARCHAR(255),
  password VARCHAR(50),
  customer_schema VARCHAR(50),
  core_schema VARCHAR(50),
  CONSTRAINT user_list_pkey PRIMARY KEY(user_id, user_name),
  CONSTRAINT user_list_user_id_key UNIQUE(user_id),
  CONSTRAINT user_list_name_schema PRIMARY KEY(user_name, customer_schema),
) 
WITH (oids = false);

and then this on conflict clause:

INSERT INTO core_table.user_list 
(user_name, full_name, remarks, password, customer_schema, core_schema, role_id, is_accessible) 
VALUES 
('kelly', 'kelly petz', 'The Boss', 'test123', 'snoppy_db1', 'snoppy_db1', 1, 1) 
ON CONFLICT (user_list_name_schema) 
DO UPDATE SET 
    password = 'test123', 
    remarks = 'The Boss';

Thank you for the speedy response. The focus is on user_name and customer_schema. No same pair can exist. — Christopher Lim, Jun 02 '16 at 07:57
@ChristopherLim Then add this constraint in your table creation, name it, and replace user_name with this constraint's name in your `on conflict` clause — Denys Séguret, Jun 02 '16 at 08:00
Thank you. Now I got it. Thank you for your help Mureinik and Denys. When I read this thread, http://stackoverflow.com/questions/36799104/how-to-correctly-do-upsert-in-postgres-9-5 I understood what you meant. — Christopher Lim, Jun 02 '16 at 08:02

score 0 · Answer 2 · answered Jun 02 '16 at 07:52

0

The on conflict clause is invoked when a unique constraint is violated. In the given table, user_name is not unique (the combination of user_id and user_name is).

If you want to use such a clause, you'd have to make the user_name unique in some way, e.g., by adding a constraint:

ALTER TABLE user_list ADD CONSTRAINT user_name_unique UNIQUE (user_name);

answered Jun 02 '16 at 07:52

Mureinik

297,002
52
306
350

Thank you for the speedy response. The focus is on user_name and customer_schema. No same pair can exist. – Christopher Lim Jun 02 '16 at 07:57
@ChristopherLim so you need a unique constraint on the combination and have the `on conflict` clause relate to it too, not just the `user_name`. – Mureinik Jun 02 '16 at 07:59
1

Thank you. Now I got it. Thank you for your help Mureinik and Denys. When I read this thread, http://stackoverflow.com/questions/36799104/how-to-correctly-do-upsert-in-postgres-9-5 I understood what you meant. – Christopher Lim Jun 02 '16 at 08:02

UPSERT Postgres 9.5

2 Answers2

Linked