本文介绍了SQL:在唯一索引中进行类型转换的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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)
尝试:
-
语法无效,但是我要达到的目的:
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:在唯一索引中进行类型转换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!