Thread: unique index on variable time

hello,

I am interested to know if I can define an unique index on a timestamp
column to reject values within one hour.

insert into table(timestamp_col) values(LOCALTIMESTAMP);
insert into table(timestamp_col) values(LOCALTIMESTAMP + '5
minutes'::INTERVAL);
I want the second insert to fail with "unique violation".

is it possible to do something like that ? I am not interested in
"date_trunc" because I want it to work and for minute 59.

thanks,
Razvan Radu

On Sun, Apr 16, 2006 at 07:07:11PM +0300, pgsql-general@list.coretech.ro wrote:
> I am interested to know if I can define an unique index on a timestamp
> column to reject values within one hour.

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.

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Sun, Apr 16, 2006 at 07:07:11PM +0300, pgsql-general@list.coretech.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