SQL：在唯一索引中进行类型转换 [英] SQL: Typecast in a Unique Index

问题描述

CREATE TABLE foo (
    dt AS DATE,
    ts AS TIMESTAMP
);

在日期值 ts处创建唯一约束的正确方法是什么每 dt 发生一次；例如：

What's the proper way to create a unique constraint where the date value of ts occurs once per dt; example:

dt         | ts
2010-01-02 | 2010-01-02 17:19:08
2010-01-02 | 2011-11-11 01:01:01
2010-01-02 | 2011-11-11 17:19:08 -- would error on insert (already a 2011-11-11)

尝试：

1. 语法无效，但是我要达到的目的：

1. Invalid syntax, but what I'm trying to achieve:

CREATE UNIQUE INDEX unique_tsdate_per_dt ON foo(dt,ts::date);

尝试未完成-可能是子查询？

Incomplete attempt - possibly a subquery?

CREATE UNIQUE INDEX unique_tsdate_per_dt ON foo(dt) WHERE ts::date -- ?

推荐答案

我认为您正在寻找 :: date 强制转换的函数形式：

I think you're looking for the function form of the ::date cast:

CREATE UNIQUE INDEX unique_tsdate_per_dt ON foo(dt, date(ts));

然后您将获得如下结果：

Then you'll get results like this:

=> insert into foo (dt, ts) values ('2010-01-02', '2010-01-02 17:19:08');
=> insert into foo (dt, ts) values ('2010-01-02', '2011-11-11 01:01:01');
=> insert into foo (dt, ts) values ('2010-01-02', '2011-11-11 17:19:08');
ERROR:  duplicate key value violates unique constraint "unique_tsdate_per_dt"
DETAIL:  Key (dt, date(ts))=(2010-01-02, 2011-11-11) already exists.

这篇关于SQL：在唯一索引中进行类型转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！