Re: unique index on variable time

Поиск
Список
Период
Сортировка
Искать

Re: unique index on variable time

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Michael Fuhr  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

Re: unique index on variable time

От:
Michael Fuhr <mike@fuhr.org>
Дата:
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

unique index on variable time

От:
"pgsql-general@list.coretech.ro" <pgsql-general@list.coretech.ro>
Дата:

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
FAQ