Re: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT 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(dot)klaver(at)aklaver(dot)com