SEO

#1 (**permalink**) 04-09-2008, 09:19 AM

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#2 (**permalink**) 04-09-2008, 09:19 AM

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...3/msg00055.php

The warning about the code being only minimally tested still stands
but maybe it'll give you some ideas.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#3 (**permalink**) 04-09-2008, 09:19 AM

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...3/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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

unique index on variable time