Stack Overflow
Stack Overflow

Questions tagged [exclusion-constraint]

An exclusion constraint is a special type of constraint for PostgreSQL. It is essentially a generalization of a unique constraint that allows a more fine grained definition of "unique" This can be used to e.g. define constraints that prevent overlapping date ranges.

30 questions
72
votes
1 answer

What does exclusion constraint `EXCLUDE USING gist (c WITH &&)` mean?

From PostgreSQL document 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. The syntax…
user3284469
12
votes
3 answers

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

I am creating a database which stores arbitrary date/time ranges in PostgreSQL 9.2.4. I want to place a constraint on this database which forces the date/time ranges to be non-overlapping, and non-adjacent (since two adjacent ranges can be expressed…
CmdrMoozy
  • 3,870
  • 3
  • 19
  • 31
5
votes
1 answer

Postgresql EXCLUDE constraint not triggering ON CONFLICT when INSERT

I have defined this table: CREATE TABLE market.price_history ( item_id bigint NOT NULL, valid_time tstzrange DEFAULT tstzrange(now(), 'infinity'), sale_price money NOT NULL, promo_code bool NOT NULL DEFAULT false, EXCLUDE USING…
madtyn
  • 1,469
  • 27
  • 55
5
votes
1 answer

Prevent overlapping values on CIDR column in PostgreSQL

Is there a constraint or some other PostgreSQL feature that prevents CIDR columns from having values that overlap? For example: 192.168.1.0/24 and 192.168.1.1/32 These could not exist together because 192.168.1.1/32 is contained in the…
Edmond
  • 615
  • 1
  • 5
  • 15
4
votes
4 answers

Recursive SQL Query with Postgres Ranges To Find Availability

I followed this blogpost: https://info.crunchydata.com/blog/range-types-recursion-how-to-search-availability-with-postgresql CREATE TABLE travels ( id serial PRIMARY KEY, travel_dates daterange NOT NULL, EXCLUDE USING spgist…
mike james
  • 8,840
  • 3
  • 18
  • 21
4
votes
1 answer

How to use ON CONFLICT with Exclusion Constraint?

for example: index by userid, sdate, edate userid sdate edate 001 2019-01-01 2019-01-30 if I insert new data like: userid sdate edate 001 2019-01-03 2019-01-20 or 001 2019-01-13 2019-02-10 or 001 …
Donald
  • 551
  • 2
  • 6
  • 22
3
votes
1 answer

Postgres trigger to check date overlap with already existing records

I have a table which have 2 column, start date and end date. start date is required end date is optional (so it's a period that basically never ends) I'm creating a trigger which ensure that no record overlap with the other ones, and so far I made…
Deviling Master
  • 3,033
  • 5
  • 34
  • 59
3
votes
1 answer

What is exclusion constraint in PostgreSQL? What is the according term (and sample SQL script) in Microsoft SQL Server?

I read a book Exclusion constraint CREATE TABLE movies ( Title TEXT, Copies INTEGER ); ALTER TABLE movies ADD EXCLUDE (title WITH=, copies WITH=); What is the meaning of Exclusion constraints? What is the according term (and sample SQL…
Vy Do
  • 46,709
  • 59
  • 215
  • 313
3
votes
1 answer

PostgreSQL exclusion constraints in a bitemporal setting?

I am currently working with a bitemporal application, which stores data entries using 4 timestamps: Valid_from, Valid_to Registration_from, Registration_to The first two state when the given entry is valid_from and valid_to, and the other two are…
kafka
  • 573
  • 1
  • 11
  • 28
2
votes
1 answer

Django Postgres Exclusion Constraint with ManyToManyField

I would like to use Django Exclusion Constraint with ManyToManyField. Unfortunatelly, so far my efforts were futile. This is my appointment model: from django.contrib.postgres.constraints import ExclusionConstraint from…
kenshin
  • 165
  • 3
  • 10
2
votes
1 answer

How to ensure entries with non-overlapping time ranges?

I need to ensure my database only contains entries where two or more of its columns are unique. This can easily be achieved with a UNIQUE constraint over those columns. In my case, I need to forbid duplication only for overlapping time ranges. The…
kafka
  • 573
  • 1
  • 11
  • 28
2
votes
1 answer

Postgresql Exclusion constraint with soft-deleted rows

I am trying to accomplish a PostgreSQL constraint on te following table: CREATE TABLE contracts ( id bigint NOT NULL, startdate date NOT NULL, enddate date NOT NULL, price numeric(19,2) NOT NULL, deleted boolean NOT NULL, …
Stijnvdk
  • 556
  • 4
  • 7
  • 20
1
vote
0 answers

How to exclude some fields of subclasses from Apache Commons-> HashCodeBuilder.reflectionHashCode

I am trying to exclude some fields for a subclass during the hashcode generation, and it seems that either I have the option to exclude it entirely or none, but not the specific fields even after specifying it in the exclusion list For e.g. if I…
user1416932
  • 257
  • 3
  • 6
1
vote
0 answers

How to use a postgres exclude constraint to enforce adjacency

Let's say I have a table relaxed_schedule, with a contestant_id and a timeslot of type daterange (or any other range, really) I know how to use an exclude constraint to prevent dateranges from being directly adjacent, like so: alter table…
Paul S
  • 434
  • 4
  • 13
1
vote
1 answer

how to handle updates on a table with exclusion constraint?

I have a table keep track of hotel reservations with exclusion constraint like below. Currently, I allow guests to update their reservations. So if guest_id 1 change his reservation from 3 day reservation to a single day from 2010-01-03, 2010-01-03,…
weiklr
  • 159
  • 1
  • 7
1
2