Re: 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