PostgreSQL强制实施列的唯一双向组合

Question

我正在尝试创建一个表格，可以强制执行相同类型的两列的唯一组合 - 在两个方向上都是如此。例如，以下情况将是非法的：

col1 col2
   1    2
   2    1

我想到了这个方案，但是它不起作用：

database=> \d+ friend;
                                Table "public.friend"
    Column    |           Type           | Modifiers | Storage  | Stats target | Description
--------------+--------------------------+-----------+----------+--------------+-------------
 user_id_from | text                     | not null  | extended |              |
 user_id_to   | text                     | not null  | extended |              |
 status       | text                     | not null  | extended |              |
 sent         | timestamp with time zone | not null  | plain    |              |
 updated      | timestamp with time zone |           | plain    |              |
Indexes:
    "friend_pkey" PRIMARY KEY, btree (user_id_from, user_id_to)
    "friend_user_id_to_user_id_from_key" UNIQUE CONSTRAINT, btree (user_id_to, user_id_from)
Foreign-key constraints:
    "friend_status_fkey" FOREIGN KEY (status) REFERENCES friend_status(name)
    "friend_user_id_from_fkey" FOREIGN KEY (user_id_from) REFERENCES user_account(login)
    "friend_user_id_to_fkey" FOREIGN KEY (user_id_to) REFERENCES user_account(login)
Has OIDs: no

不使用触发器或任何高级魔法，只使用约束条件是否可能编写此内容？

- tadeas

Answer 1

Neil的解决方案的一种变化，不需要扩展程序：

create table friendz (
  from_id int,
  to_id int
);

create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));

尼尔的解决方案允许您使用任意数量的列。

我们都依赖于使用表达式来构建索引，这在https://www.postgresql.org/docs/current/indexes-expressional.html中有记录。

- Gary - Stand with Ukraine

---

不错啊，伙计。非常简单。 - Neil McGuigan

谢谢。无论如何都找不到这个。 - parreirat

我该如何使用upsert使其工作？由于这是一个索引，因此ON CONFLICT（col1，col2）形式似乎不起作用。 - Votagus

对于ON CONFLICT DO NOTHING情况，您不需要指定任何内容。对于ON CONFLICT DO UPDATE情况，我认为您不需要给出列列表（col1，col2），但如果我记得正确，您可以直接指定索引名称。 - Flo

有没有办法使用两列的text类型来实现这个功能？还是需要通过触发器的方式来实现？ - undefined

@2pac - 最小值和最大值函数支持字符数据类型，所以可以正常工作。 - undefined

Answer 2

您是否认为intarray扩展是魔法？

但需要使用int键来代替文本名称...

以下是可能的解决方案：

create extension intarray;

create table friendz (
  from_id int,
  to_id int
);

create unique index on friendz ( sort( array[from_id, to_id ] ) );

insert into friendz values (1,2); -- good

insert into friendz values (2,1); -- bad

http://sqlfiddle.com/#!15/c84b7/1

- Neil McGuigan