Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [exclusion-constraint]

Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

1
vote
1answer
56 views

Include constraint for daterange type

Here is the table CREATE TABLE date_ranges ( id SERIAL PRIMARY KEY, user_id INTEGER, start_at DATE, end_at DATE, is_exception BOOLEAN ); ALTER TABLE date_ranges ADD CONSTRAINT ...
1
vote
1answer
502 views

How to find out what operator class and access method should be used for an exclusion constraint?

Original goal: create a constraint to ensure that only non-overlapping subnets exist in a particular Postgres table. From reading the docs carefully, I could get as far as this: create table subnets ...
2
votes
1answer
177 views

Exclusion constraint generates: ERROR: operator &&(anyrange,anyrange) is not a member of operator family “range_ops”

How come when I try to create an exclusion constraint with GIST, CREATE TABLE foo ( a tsrange, EXCLUDE (a WITH &&) ); ERROR: operator &&(anyrange,anyrange) is not a member of ...
1
vote
1answer
257 views

Composite constraint of numrange and boolean

I have a current constraint like this: ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH &&...
1
vote
0answers
109 views

Non-commutative exclusion constraint?

If I create the following table.. CREATE TABLE foo ( x int ); I can EXCLUDE the following, WHERE x = <a duplicate value> (generic unique constraint) WHERE x = <specific value> (type ...
0
votes
0answers
85 views

Performance of table locks vs exclude constraints

Suppose I have a system for reserving vacation properties. One of the tables is reservations. Each reservation has a property_id, a start_date, and an end_date. I want to prevent conflicting ...
8
votes
2answers
1k views

Create a PostgreSQL constraint to prevent unique combination rows

Imagine you have a simple table: name | is_active ---------------- A | 0 A | 0 B | 0 C | 1 ... | ... I need to create a special unique constraint which fails on following situation: ...
2
votes
1answer
504 views

Placing a uniqueness constraint on a date range

I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate). How do I place a constraint on the table such that bookings are not ...
3
votes
1answer
803 views

Constraint on row value based on values in other rows

Is it possible to have a table with four columns: CREATE TABLE accrual ( add_date date NOT NULL, user_id integer NOT NULL, rate integer NOT NULL, amount numeric(7,3) ); and then ...
3
votes
1answer
656 views

How can I exclude range overlaps if one range may be empty?

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table: CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT ...
13
votes
1answer
5k views

Uniqueness constraint with date range

Consider a prices table with these columns: id integer primary key product_id integer -- foreign key start_date date not null end_date date not null quantity integer price numeric I'...
5
votes
1answer
2k views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
1
vote
1answer
1k views

Restrict two specific column values from existing at the same time

I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed. I would appreciate any help creating a constraint that will enforce this. CREATE TABLE example ( ...
4
votes
1answer
323 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
1
vote
1answer
3k views

Constraint based rules engine

I am evaluating software systems which configure products that tout constraint based versus rules based engines. Can someone explain (with maybe an example or two) to a non-expert what the difference ...
35
votes
2answers
16k views

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); ...