How to create a Postgres table with a unique combined primary key?

Question

I have two tables named playersand matchesin Postgres DB as follows:

CREATE TABLE players (
    name text NOT NULL,
    id serial PRIMARY KEY
);

CREATE TABLE matches (
    winner int REFERENCES players (id),
    loser int REFERENCES players (id),
    -- to prevent rematch btw players
    CONSTRAINT unique_matches
    PRIMARY KEY (winner, loser)
);

How can I guarantee that matchesonly a unique combination is used for the primary key (winner, loser)or (loser, winner), so that the table matcheswill not allow insertion:

INSERT INTO matches VALUES (2, 1);

If it already has a string containing VALUES (1, 2), for example:

 winner | loser
--------+-------
      1 |     2

The goal is to avoid entering matches between the same players.

Answer 1

Create a unique index:

CREATE UNIQUE INDEX matches_uni_idx ON matches
   (greatest(winner, loser), least(winner, loser));

UNIQUE PRIMARY KEY , , .

serial, PK, , PK (. ). NOT NULL . (Else, NOT NULL.)

CHECK, , :

CHECK (winner <> loser)

: ( , ), , :

SELECT * FROM matches
WHERE  greatest(winner, loser) = 3  -- the greater value, obviously
AND    least(winner, loser) = 1;

, , :

WITH input AS (SELECT $id1 AS _id1, $id2 AS _id2)  -- input once
SELECT * FROM matches, input
WHERE  greatest(winner, loser) = greatest(_id1, _id2)
AND    least(winner, loser) = least(_id1, _id2);

CTE .

Answer 2

Postgres , . , , - , ( ), , , 1 id :

CREATE TABLE matches (
    p1 int REFERENCES players (id),
    p2 int REFERENCES players (id),
    p1winner boolean,

    CONSTRAINT matches_pk PRIMARY KEY (p1, p2),
    CONSTRAINT matches_players_order CHECK (p1 < p2)
);