Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux