如何在时间戳日期上创建唯一约束

Question

在PostgreSQL(9.2)中，我有一个表格：

  tservice         timestamp without time zone NOT NULL,
  patient_recid    integer NOT NULL

我希望在tservice的日期上创建一个唯一约束条件，类似于：

  constraint service_unique UNIQUE (patient_recid, tservice::date)

在进行日期转换时会产生语法错误。

在PostgreSQL中应该如何最好地解决这个问题？

TIA

- Alan Wayne

---

无法在表达式上创建约束，需要使用唯一的“索引”。 - user330315

@a_horse_with_no_name 会尝试。谢谢。 - Alan Wayne

Answer 1

奇怪的是，似乎PostgreSQL只允许在唯一约束中使用列，但不允许在此处使用表达式。

您可以创建一个唯一的函数索引（作为解决方法），从9.1版本开始支持表达式索引：

create unique index service_unique 
ON table_name(patient_recid, date_trunc('day',tservice));

- krokodilko

---

它在9.6版本上无法工作 错误：语法错误，附近为“（” 第2行：...ty_id_desc_date UNIQUE ( date_trunc('day',pwk...)` - Andrey

Answer 2

如前所述，UNIQUE INDEX 的效果非常好。 对于那些遇到“index expression中的函数必须标记为IMMUTABLE”错误的人，您可以在索引中修复时区：

CREATE UNIQUE INDEX service_unique ON table_name(patient_recid, date_trunc('day',tservice AT TIME ZONE 'GMT'));

- jcare44

Answer 3

在创建约束时，无法将tservice强制转换。

如果该约束应用于类型为“日期”的tservice，则最好将tservice列直接创建为“日期”类型。

您可以按照以下方式更新列的类型：

ALTER TABLE your_table ALTER COLUMN tservice TYPE date;

然后像这样添加约束条件：

ALTER TABLE your_table ADD CONSTRAINT service_unique UNIQUE (patient_recid, tservice)

- Calle Bergström