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.
Questions tagged [exclusion-constraint]
30 questions
1
vote
0 answers
Conditional UNIQUE constraint on table?
I have this table:
CREATE TABLE mitg.tbl_gch_customers (
pe character varying(10)NOT NULL,
name character varying NOT NULL,
city character varying,
address character varying NOT NULL,
state character varying,
zip character…
Mike Jarvis
- 11
- 1
1
vote
1 answer
How to impose this exclusion constraint?
I have a key-value table.
CREATE TABLE keyvalues (
key TEXT NOT NULL,
value TEXT
)
I want to impose a constraint that if a key has an entry with NULL value, it cannot have any other entries.
How do I do that?
To clarify:
I want to allow…
Craig
- 161
- 7
1
vote
1 answer
Cap on number of table rows matching a certain condition using Postgres exclusion constraint?
If I have a Postgresql db schema for a tires table like this (a user has many tires):
user_id integer
description text
size integer
color text
created_at timestamp
and I want to enforce a constraint that says "a user can only have 4 tires".
A…
Nona
- 5,302
- 7
- 41
- 79
0
votes
1 answer
Logical operators in a Postgres EXCLUDE constraint
Consider a following schema with a date range that must not overlap between two arbitrary rows:
CREATE TABLE dummy (
duration tsrange,
active bool,
EXCLUDE USING gist (duration WITH &&)
);
How would I limit this constraint to rows…
Heap Underflow
- 377
- 1
- 12
0
votes
1 answer
ERROR: could not create exclusion constraint
I have a table:
CREATE TABLE attendances
(
id_attendance serial PRIMARY KEY,
id_user integer NOT NULL
REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE,
entry_date timestamp with time zone DEFAULT NULL,
…
0
votes
0 answers
Exclude local csv from SQL query in Workbench
I don't know if this is possible or if it's a stupid question, so please feel free to re-direct my efforts.
I have a SQL query grabbing transaction data from a database. Some of the locations are test locations that need to be excluded.…
Dr Xorile
- 967
- 1
- 7
- 20
0
votes
3 answers
Constraint on a group of rows
For a simple example, let's say I have a list table and a list_entry table:
CREATE TABLE list
(
id SERIAL PRIMARY KEY,
);
CREATE TABLE list_entry
(
id SERIAL PRIMARY KEY,
list_id INTEGER NOT NULL
REFERENCES list(id)
ON…
User1291
- 7,664
- 8
- 51
- 108
0
votes
2 answers
Postgres exclusion constraint on insert/update
I have a table defined like so
Table "public.foo"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+-------------------------------------
…
dekpoke
- 3
- 2
0
votes
0 answers
Constraint for ensuring no overlaps, but only under certain circumstances, and with conflict resolution?
I have a table in a PostgreSQL database in which I store bookings that span periods of time. There must be at least 45 minutes between such bookings. I currently have this constraint on the table:
EXCLUDE USING GIST (
tsrange(start_time AT TIME…
Emil Fors
- 110
- 6
0
votes
1 answer
Create a new column based on conditional inclusion fulfilling conditions in R
My dataset looks like this:
df <- data.frame(PatientID = c("3454","345","5","345","567","79"), sex = c(Female, Female, Female, Male, Male, Male)
waist = c(60, 89, 90, 110, 200, 150), tryglicerides = c(100, 150, 170, 105, 200,…
Lili
- 547
- 6
- 19
0
votes
1 answer
Postgres: best way to avoid overlapping ranges with other conditions
Simplified M-N join table
CREATE TABLE dummy (
fkey1 int, /* omitting FK clause */
fkey2 int,
/* could also separate begin and end dates */
effective_dates_of_assignment daterange,
EXCLUDE /* WHAT GOES HERE?? */
)
I want the…
Andrew Lazarus
- 18,205
- 3
- 35
- 53
0
votes
1 answer
Exclusion constraint that allows overlapping at the boundaries
I tried to have a PostgreSQL constraint so that there will be no overlap between two date intervals. My requirement is that the date c_from for one entry can be the same as c_until for another date.
Eg: "01/12/2019 12/12/2019" and "12/12/2019…
son hoang
- 3
- 2
0
votes
1 answer
Exclude constraint across many tables?
Consider the following table:
todos:
id | floor_start | floor_end
----+-------------+-------------
1 | 10 | 20
2 | 20 | 30
3 | 30 | 40
4 | 35 | 45
to prevent 2 elevators on the same floor I can go…
dfens
- 5,413
- 4
- 35
- 50
0
votes
1 answer
Unable to add exclude constraint to table
I'm unable to create an excludes index with this schema.
create table foo_bar(
foo_id text primary key,
bar char(3) not null,
created_at timestamptz not null default current_timestamp,
system_period tstzrange not null default…
Tim
- 4,471
- 5
- 36
- 42
-1
votes
2 answers
How to get unique values from 2 columns in PostgreSQL
i have a column "id_a" and a column "id_b".
"id_a" is an integer[] type.
"id_b" is an int type.
I have to find a way to verify that there are no more values of id_a with the same id_b
For Example:
id_a {3,4,5} id_b 18
Other…
user13354649