Re: unique index on variable time

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> On Sun, Apr 16, 2006 at 07:07:11PM +0300, pgsql-general(at)list(dot)coretech(dot)ro wrote:
>> I am interested to know if I can define an unique index on a timestamp
>> column to reject values within one hour.

Perhaps
CREATE UNIQUE INDEX foo ON tab (date_trunc('hour', col));
It's not clear whether this does exactly what you wished for.

> Last month I posted an idea for enforcing unique constraints on
> date ranges by using a composite type and a custom comparison
> operator:
> http://archives.postgresql.org/pgsql-sql/2006-03/msg00055.php
> The warning about the code being only minimally tested still stands
> but maybe it'll give you some ideas.

I don't think that actually works; the problem is that its idea of
"equality" is not transitive, which is going to confuse btree terribly.

regards, tom lane