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

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(dot)klaver(at)aklaver(dot)com