All Questions
15 questions
3
votes
1
answer
89
views
Enforce unique constraint without denormalization
Option 1
I have a database schema that looks something like this:
https://dbfiddle.uk/JKCTjadD
A Task belongs to a Service (like in ECS)
A Task can be reassigned to another Service.
TaskConfig tracks ...
- 133
4
votes
1
answer
771
views
UPSERT based on unique combination of (INTEGER, NULL), (NULL, INTEGER)
My table, for simplicity, looks like this:
id | foreign_key_1 | foreign_key_2 | value
As clear from the names, the fields foreign_key_1 and foreign_key_2 refer to PKs in two other tables.
The tricky ...
- 209
3
votes
1
answer
2k
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 ...
- 131
1
vote
1
answer
396
views
How can I define a one to many constraint between columns of the same table?
I'm creating an IDs table in PostgreSQL to represent the relationship between multiple foreign IDs - call them a, b and c.
I happen to know that the foreign IDs have one-to-many relationships between ...
- 187
3
votes
1
answer
4k
views
Compound unique constraint with NULL values
With the following table structure
CREATE TABLE favorite_comments(
id SERIAL PRIMARY KEY NOT NULL,
opinion_id BIGINT REFERENCES opinions(id),
review_id BIGINT REFERENCES reviews(id),
...
- 785
3
votes
1
answer
1k
views
Remove extra email characters to enforce uniqueness
Gmail ignores . inside an email address e.g [email protected] and [email protected] are same
Gmail also considers same account if account has any number after + symbol. e.g xyz@gmail and [email protected] are ...
- 147
2
votes
2
answers
233
views
Unique constraint on different columns
I'm trying to make a table to store the edges of a directed graph, but want to avoid duplicate edges in both directions.
I can of course make it one direction :
CREATE TABLE edge (parent integer, ...
- 23
3
votes
1
answer
408
views
Constraint to prevent 'duplicates' only when column > 0
I have a table with a fk column that should be unique when another int column is > 0. If that sentence doesn't make sense, here is an example of valid rows:
CREATE TABLE foo
AS
SELECT fk,int
...
- 155
18
votes
2
answers
21k
views
Why do composite foreign keys need a separate unique constraint?
Here is a simple table where records may reference parent records in the same table:
CREATE TABLE foo (
id SERIAL PRIMARY KEY,
parent_id INT NULL,
num INT NOT ...
- 1,131
4
votes
1
answer
455
views
Can column uniqueness be commutative?
I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be ...
- 1,119
2
votes
1
answer
278
views
Multi-column uniqueness constraint, checking a field has one of two values
I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example
create table builders (
id text serial
);
create table ...
- 825
5
votes
2
answers
7k
views
Unique across tables
I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users).
However, each user can also have additional emails.
...
- 655
33
votes
2
answers
17k
views
Custom unique constraint, only enforced if one column has a specific value
Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is 'true', then I want the ...
- 1,039
36
votes
1
answer
106k
views
Optimal way to ignore duplicate inserts? [duplicate]
Background
This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code:
-- Ignores duplicates.
INSERT INTO
db_table (...
- 833
160
votes
3
answers
177k
views
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
...
- 1,711