Thread: ERROR: there is no unique or exclusion constraint matching the ONCONFLICT specification

On 05/05/2018 07:49 AM, Alexander Farber wrote:
> Hello,
> 
> I am struggling with an UPSERT in PostgreSQL 10.3 and have prepared a 
> simple test case showing my 2 problems (at 
> http://sqlfiddle.com/#!17/7e929/13 and also below) -
> 
> There is a two-player word game:
> 
> CREATE TABLE players (
>      uid SERIAL PRIMARY KEY,
>      name text NOT NULL
> );
> 
> CREATE TABLE games (
>      gid SERIAL PRIMARY KEY,
>      player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
>      player2 integer NOT NULL REFERENCES players ON DELETE CASCADE,
>      hand1   char[7]   NOT NULL,
>      hand2   char[7]   NOT NULL
> );
> 
> INSERT INTO players (name) VALUES ('Alice'), ('Bob'), ('Carol');
> 
> INSERT INTO games (player1, player2, hand1, hand2) VALUES
>      (1, 2, '{A,B,C,D,E,F,G}', '{A,B,C,D,E,F,G}'),
>      (1, 3, '{}', '{Q}'),
>      (3, 2, '{A,Q}', '{A,B,C}'),
>      (1, 2, '{Q}', '{A,B,C,D,E,F,G}'),
>      (2, 3, '{Q}', '{A,B,C,D,E,F,G}'),
>      (2, 3, '{Q}', '{X,Y,Z}'),
>      (1, 2, '{Q}', '{A,B,C,D,E,F,G}');
> 
> I am trying to set up a daily cronjob, which would calculate player 
> statistics and store them into a table for faster access from web scripts:
> 
> CREATE TABLE stats (
>      uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
>      single_q_left INTEGER NOT NULL DEFAULT 0
> );
> 
> Here I have just one statistic: when a player has only the "difficult" 
> letter "Q" left in her hand.
> 
> Below I am trying to calculate such situations per user and store them 
> into the stats table:
> 
> INSERT INTO stats(uid, single_q_left)
> SELECT player1, COUNT(*)
> FROM games
> WHERE hand1 = '{Q}'
> GROUP BY player1
> ON CONFLICT(uid) DO UPDATE SET
> single_q_left = EXCLUDED.single_q_left;
> 
> Unfortunately, this gives me the error
> "here is no unique or exclusion constraint matching the ON CONFLICT 
> specification"
> and I can not understand it despite rereading
> https://www.postgresql.org/docs/9.5/static/sql-insert.html

The uid column in the stats table has neither a unique or exclusion 
constraint on it.

> 
> And my second problem is: the above query only calculates "half the 
> picture", when a player is stored in the player1 column.
> 
> How to add "the second half", when the player had a single Q left, while 
> she was player2?
> 
> Should I use SELECT UNION or maybe CASE WHEN ... END?
> 
> Thank you
> Alex
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 05/05/2018 10:41 AM, Alexander Farber wrote:
> Hi Adrian,

>         There is a two-player word game:
> 
>         CREATE TABLE players (
>               uid SERIAL PRIMARY KEY,
>               name text NOT NULL
>         );
> 
>         CREATE TABLE games (
>               gid SERIAL PRIMARY KEY,
>               player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
>               player2 integer NOT NULL REFERENCES players ON DELETE CASCADE,
>               hand1   char[7]   NOT NULL,
>               hand2   char[7]   NOT NULL
>         );
> 
>         CREATE TABLE stats (
>               uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
>               single_q_left INTEGER NOT NULL DEFAULT 0
>         );
> 
> 
>     The uid column in the stats table has neither a unique or exclusion
>     constraint on it.
> 
> 
>   The uid column in stats refers to uid column in players, where it is 
> PRIMARY KEY, why doesn't PostgreSQL "see" that? :-)

Because the potential conflict it may have to deal with is the INSERT to 
the table stats, not the table players. As it stands now the uid column 
can have duplicate values so there is no way for Postgres to know what 
row is in conflict.

To have ON CONFLICT work you need to trip the following:

https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT

"The optional ON CONFLICT clause specifies an alternative action to 
raising a unique violation or exclusion constraint violation error."


> 
> Regards
> Alex


-- 
Adrian Klaver
adrian.klaver@aklaver.com