Question 89441 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres use two column in the ON CONFLICT would occur error – Postgresql

I use the query string:

INSERT INTO public.owner (name, address, public_key) 
VALUES 
('name1', 'address4', 'publicKey1'),
('name1', 'address5', 'publicKey2'),
('name1', 'address6', 'publicKey3')

ON CONFLICT (address, public_key) DO NOTHING;

And I got an error message:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
SQL state: 42P10

I knew the answer from the other question similar like mine is adding UNIQUE CONSTRAINT in the table of column, but I had done that, and the error still here.

My create script is:

CREATE TABLE IF NOT EXISTS public.owner
(
    id integer NOT NULL DEFAULT nextval('owner_id_seq'::regclass),
    name character varying COLLATE pg_catalog."default" NOT NULL,
    address character varying COLLATE pg_catalog."default" NOT NULL,
    public_key character varying COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    updated_at timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT "PK_8e86b6b9f94aece7d12d465dc0c" PRIMARY KEY (id),
    CONSTRAINT "UQ_5e67355720891990b30c3079806" UNIQUE (address),
    CONSTRAINT "UQ_dbff2cddd177bff09af6381696d" UNIQUE (public_key)
)

CONSTRAINT "UQ_5e67355720891990b30c3079806" UNIQUE (address) and "UQ_dbff2cddd177bff09af6381696d" UNIQUE (public_key) have created in the table.

But the query string still doesn’t work.

Is there any mistake I haven’t fix?

Tags:

3

Answers


  1. 0

    The conflict_target can only handle one potential conflict. That means that you have to make a choice between a unique constraint on address or public_key or the combination of these two columns.

    You just can’t handle multiple constraint violations with the current implementation of the ON CONFLICT clause.

    Login or Signup to reply.
  2. 0

    You don’t need to unique keys with one column each.
    you need one unique key with two columns.

    Or alternative the on conflict clause should only mention one column.

    create table ownerTwoKeys(
    name varchar(30),
    address varchar(30),
    public_key varchar(30),
    CONSTRAINT UQ_address UNIQUE (address),
    CONSTRAINT UQ_public_key UNIQUE (public_key)
);


create table ownerOneKey (
    name varchar(30),
    address varchar(30),
    public_key varchar(30),
    CONSTRAINT UQ_address_public_key UNIQUE (address, public_key)
  
);

INSERT INTO ownerTwoKeys (name, address, public_key) 
VALUES 
('name1', 'address4', 'publicKey1'),
('name1', 'address5', 'publicKey2'),
('name1', 'address6', 'publicKey3'),
('name1', 'address7', 'publicKey4'),
('name1', 'address6', 'publicKey5')
ON CONFLICT (address) DO NOTHING;

select * from ownerTwoKeys;



INSERT INTO ownerOneKey (name, address, public_key) 
VALUES 
('name1', 'address4', 'publicKey1'),
('name1', 'address5', 'publicKey2'),
('name1', 'address6', 'publicKey3'),
('name1', 'address7', 'publicKey4'),
('name1', 'address6', 'publicKey4'),
('name2', 'address6', 'publicKey4')
ON CONFLICT (address, public_key) DO NOTHING;

select * from ownerOneKey;

    DB fiddle to play with.

    Login or Signup to reply.
  3. 0

    As already pointed out by Frank Heikens, you can’t handle multiple conflict scenarios in one on conflict clause.

    Luckily, there’s an exception:

    For ON CONFLICT DO NOTHING, it is optional to specify a conflict_target; when omitted, conflicts with all usable constraints (and unique indexes) are handled.

    So you can just skip the conflict target list: online demo

    INSERT INTO public.owner (name, address, public_key) 
VALUES 
('name1', 'address4', 'publicKey1'),
('name1', 'address5', 'publicKey2'),
('name1', 'address6', 'publicKey3')
ON CONFLICT /*no targets specified*/ DO NOTHING;

    This works for this particular case because there are only 3 constraints, 2 of which you wish to handle this way and the remaining 1 is respected by letting the target table generate the unique id on its own.

    • If you had more constraints and wanted to skip only these two, on conflict do nothing would still skip them all instead.
    • If you wanted to resolve conflicts in a specific way using on conflict do update, you’d have to specify the one single constraint that solution is meant for. To solve more, you’d need to "manually" avoid them, by first spotting conflicts in your incoming data, then filtering them out prior to the insert, as demonstrated here.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search