Geek asks and answers

Overlapping Exclusion Limit

I would like to have the equivalent of this:

create table t (
    fromts timestamptz, 
    tots timestamptz,
    exclude using gist ((fromts, tots) with overlaps)
);
ERROR:  syntax error at or near ","
LINE 4:         exclude using gist ((fromts, tots) with overlaps)

I assume that in addition to the syntax error, the problem of a non-commutative operator also arises.

What is the easiest way to make it work?

+3
source share
2 answers

PostgreSQLa named type is needed, and an operator class is needed for exception constraints. In 9.1, RANGEnot yet implemented.

You can define your own, but this requires a super privilege (that is, it will not work with hosted databases, etc.).

You can try putting timestamps in the fields and define the limit using the intersects ( &&) operator :

CREATE FUNCTION ts_to_box(TIMESTAMPTZ, TIMESTAMPTZ)
RETURNS BOX
AS
$$
    SELECT  BOX(POINT(DATE_PART('epoch', $1), -1), POINT(DATE_PART('epoch', $2), 1))
$$
LANGUAGE 'sql'
IMMUTABLE;

CREATE TABLE t (
    fromts timestamptz, 
    tots timestamptz,
    EXCLUDE USING GIST (ts_to_box(fromts, tots) WITH &&)
);
+3
source

, ( ):

@Quassnoi, timestamp, timestamptz:

CREATE TABLE t (
    fromts timestamp, 
    tots   timestamp,
    exclude using GIST (box(point(EXTRACT(EPOCH FROM fromts), 0)
                          , point(EXTRACT(EPOCH FROM tots),   0)) WITH &&)
);

timestamp - , . EXTRACT IMMUTABLE timestamp, timestamptz. @Quassnoi , .

, timestamp with time zone IMMUTABLE , @Quassnoi, .

Postgres 9.2

... , tstzrange. < > .

+4

More articles:


All Articles