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.
27
questions
1
vote
1
answer
76
views
Why does VALIDATE CONSTRAINT on PG 14.7 take a ShareLock?
I'm following this answer to add a NOT NULL constraint to an existing DB table with ~100M rows. However, when I try running the backend process takes out a ShareLock.
ALTER TABLE mytable VALIDATE ...
- 283
0
votes
1
answer
38
views
Set Operation Exclusion Constraint for Multiple Tables All at Once
In multiple of my tables, I have a deleted column for tracking things which have been deleted, instead of directly deleting them from the database.
As such, I would like to create a constraint for ...
- 121
0
votes
1
answer
63
views
Why does my GiST index not trigger a conflict? [duplicate]
I have a table with user subscriptions in my Postgres 13 DB. I added a GiST index to disallow overlapping time ranges for the same user:
CREATE INDEX user_sub_exclusion_index ON public.user_sub USING ...
- 755
0
votes
2
answers
258
views
How to define constraint having two columns with boolean column to be true only once
Let the table to be considered is this:
create table users(
email varchar(50) not null,
is_verified boolean default false,
);
Now, how can I define the exclusion constraint here so that if the ...
- 103
1
vote
2
answers
267
views
Prohibit overlapping intervals in a cyclic (e.g. weekly) schedule
I'm designing a postgres table to record the intervals contained in a weekly schedule. It would hold schedules for multiple businesses, and a simple example dataset might look like:
business_id ...
- 552
1
vote
0
answers
1k
views
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 ...
- 111
2
votes
1
answer
155
views
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 ...
-1
votes
2
answers
247
views
How to conditionally pick a row based on status value?
I want to select all available and bought packs. An user can open two pages to buy one pack, this results in two rows in the payment table. One gets status 'Pending' and the one that is bought gets ...
- 11
1
vote
1
answer
293
views
Using postgresql, how to prevent new inserts on varchar column using ^@ / starts_with operator / function?
I'm trying to create a table with a path column. This table should only allow inserting a new path if the new path is neither an ancestor nor a descendant of any already inserted path. i.e. with '/foo/...
- 11
0
votes
1
answer
45
views
Exclusion constraint for text values [closed]
I have two tables. A client table and a vehicle table, the two are linked in a unit lookup table.
My constraint is that a vehicle cannot belong to a client if it already is associated with another ...
- 189
3
votes
0
answers
298
views
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). ...
- 33
1
vote
1
answer
303
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 ...
- 11
3
votes
1
answer
3k
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 ...
- 588
3
votes
1
answer
744
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 ...
- 62.1k
1
vote
1
answer
765
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 &&...
- 169
1
vote
0
answers
233
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 ...
- 62.1k
0
votes
1
answer
277
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 ...
- 885
11
votes
2
answers
3k
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:
...
- 113
3
votes
1
answer
2k
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 ...
- 133
5
votes
1
answer
3k
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 ...
4
votes
1
answer
1k
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 ...
- 885
18
votes
1
answer
11k
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'...
- 293
5
votes
1
answer
3k
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 ...
- 371
3
votes
1
answer
3k
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
(
...
- 133
5
votes
1
answer
527
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 ...
- 327
1
vote
1
answer
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 ...
- 11
60
votes
2
answers
38k
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 &&)
);
...
- 885