Database Administrators Stack Exchange
Database Administrators 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.

Syntax

Exclusion constraints can be created like this in the CREATE TABLE ... CONSTRAINT ... EXCLUDE command

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

You can find more information on the CREATE TABLE ... CONSTRAINT ... EXCLUDE

Or, you can add them to existing tables using the same syntax with ALTER TABLE

ALTER TABLE circles
  ADD EXCLUDE
  USING gist (c WITH &&);
27 questions
62
votes
2 answers

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 &&) ); But I get this error: ERROR: data type integer has no…
Ian Timothy
  • 905
  • 1
  • 7
  • 10
19
votes
1 answer

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'd like the database to enforce the rule that a…
spike
  • 303
  • 1
  • 2
  • 7
11
votes
2 answers

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: different is_active values can't co-exist for the…
Andrii Skaliuk
  • 113
  • 1
  • 5
6
votes
1 answer

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 manufactured items, there may be rows where…
vektor
  • 411
  • 1
  • 5
  • 16
6
votes
1 answer

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 constrain it so that if I already have one entry…
Bradley Mecham
  • 101
  • 1
  • 9
5
votes
1 answer

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 not null, width double precision not null, …
Snowball
  • 327
  • 3
  • 8
4
votes
1 answer

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 allowed to overlap? I am trying to implement this…
srip
  • 143
  • 1
  • 5
4
votes
1 answer

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 NULL, max INTEGER NOT NULL ); INSERT INTO…
Nathan Long
  • 1,005
  • 2
  • 12
  • 21
3
votes
1 answer

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 ( example_id serial PRIMARY KEY, example_state…
Nathan Lippi
  • 133
  • 1
  • 6
3
votes
1 answer

Postgres constraints: EXCLUDE (name WITH =) vs partial unique index

I am refactoring my database constraint for such a table: CREATE TABLE products ( name text NOT NULL, status text NOT NULL ); A former engineer working on the project had such a constraint: ALTER TABLE products ADD CONSTRAINT…
Andrew Polukhin
  • 131
  • 2
3
votes
0 answers

Ordering of columns in a GIST index

I am modelling an application where events for factories can be registered. There are usually a small amount of factories (around 500), but each factory can register a large set of events (millions). It's impossible that two events overlap for the…
ItIsJustMe
  • 33
  • 3
3
votes
1 answer

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 ( subnet cidr, exclude (subnet with &&) ); But…
Wildcard
  • 587
  • 8
  • 18
3
votes
1 answer

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 operator family "range_ops" DETAIL: The exclusion operator must be…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
2
votes
1 answer

How to enforce UNIQUE constraint for sixth normal form in PostgreSQL

I have a table person which I am planing to normalize to the sixth normal form. The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the change history of these columns and adding…
Eerik Sven Puudist
  • 175
  • 4
1
vote
1 answer

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 is? My guess is that a constraint based engine…
PMay
  • 11
  • 1
  • 2
1
2