在 postgresql 中的 TIMESTAMP 列上应用日期的唯一约束 [英] Applying unique constraint of date on TIMESTAMP column in postgresql

问题描述

我有一个 postgresql 表

I have a postgresql table as

CREATE TABLE IF NOT EXISTS table_name
(
    expiry_date DATE NOT NULL,
    created_at TIMESTAMP with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    CONSTRAINT user_review_uniq_key UNIQUE (expiry_date, created_at::date) -- my wrong attempt of using ::
)

我想在这个表上放置 uniue 约束，这样 expiry_date 和 created_at 的日期应该是唯一的.问题是 created_at 列是时间戳而不是日期.

I want to put uniue constraint on this table in such a way that expiry_date and date of created_at should be unique. Problem is created_at column is timestamp not date.

那么有什么方法可以设置唯一约束，使 expire_date 和 created_at::date 应该是唯一的?

so is there any way to put unique constraint such that expire_date and created_at::date should be unique?

我的尝试是使用
CONSTRAINT user_review_uniq_key UNIQUE (expiry_date, created_at::date) 无效.

My attempt was to use
CONSTRAINT user_review_uniq_key UNIQUE (expiry_date, created_at::date) which is not valid.

推荐答案

如果您的创建日期不需要时区:创建唯一索引如下:

If you do not need a time zone for your created date : create a unique index has follows :

create unique index idx_user_review_uniq_key on  table_name (expiry_date, cast(created_at as date));

如果您非常需要时区，那么您需要使用一个小技巧(https://gist.github.com/cobusc/5875282) :

If you need that badly to have a time zone then you need to use a little trick (https://gist.github.com/cobusc/5875282) :

create unique index idx_user_review_uniq_key on  table_name (expiry_date, date(created_at at TIME zone 'UTC'));

这篇关于在 postgresql 中的 TIMESTAMP 列上应用日期的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！