Postgres unique index for a date on a date time field

Question

I have a table event_logs with two columns of interest for which I would like to create an index. The problem is one of the columns is a datetime field, which is fine for 99% of the things we use it for as it stores both the date and time for a given event.

However I would like to create a unique index which would make it impossible to create two events on the same date (not datetime, but just the date) with the same person.

quiz_master_id (integer)
start_at (datetime)

Is it even possible to do such a think to create such a unique index for a given date instead of datetime? Or is there another similar method to deal with this in Postgres?

Gordon Linoff · Accepted Answer · 2016-10-18 13:02:51Z

2

Yes, you just want an index on an expression:

create unique index idx_eventlogs_startatdate
    on event_logs(date_trunc('day', start_at))

answered Oct 18, 2016 at 13:02

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres unique index for a date on a date time field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related