ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Even the simpler INSERT statement

INSERT INTO stats(uid, single_q_left)
SELECT player1, COUNT(*)
FROM games
WHERE hand1 = '{Q}'
GROUP BY player1
ON CONFLICT(uid) DO NOTHING;

produces the same error.

On Sat, May 5, 2018 at 4:49 PM, Alexander Farber <alexander.farber@gmail.com

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

Hi Adrian,

On Sat, May 5, 2018 at 7:03 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 05/05/2018 07:49 AM, Alexander Farber wrote:

http://sqlfiddle.com/#!17/7e929/13

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? :-)

Regards
Alex

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

Thanks, now I see the difference