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