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
