All Questions
Tagged with constraint postgresql
156 questions
0
votes
1
answer
31
views
How can I find all violations of a proposed foreign key on all columns with a specific name?
Let's say I have multiple tables with a column foobar. And I wish to make to make all these columns reference foobar.id as a FOREIGN KEY. The problem is I know there are violations in this schema from ...
1
vote
2
answers
90
views
Select unique rows based on condition stored in another table
I want to select rows based on conditions that is fetched from the database. The tables structure are mentioned below.
# Questions Table:
╔════════════╦═══════════════╦════════╗
║ id ║ ...
0
votes
0
answers
101
views
How to replace (drop and rename) a table without triggering constraints in postgres
I have a table (A) that I need to reprocess from time to time.
Doing that using update or upsert is really slow, so, to make things faster, I decided to create a new table (B), copy all the rows from ...
2
votes
2
answers
4k
views
CASCADE DELETE per statement
Can I execute something like this in Postgresql, that is, execute CASCADE DELETE for just one statement?
DELETE CASCADE FROM foo WHERE bar = 'baz';
My goal is to delete not just the selected record(s)...
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 ...
1
vote
1
answer
161
views
Lazily Evaluate Insert Arguments If Constraint Violated
create table a (
b text not null unique,
c text not null
);
-- Sleeps for 1 second as expected.
insert into a (b, c) values ('d', pg_sleep(1));
-- Sleeps for 1 second even though record is ...
1
vote
1
answer
241
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 ...
1
vote
1
answer
743
views
Slow deletes in Postgresql with foreign key constraints [duplicate]
I have a number of tables relating to an entity and delete them in a transaction to make sure all are removed simultaneously:
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
delete from table1 where entity_id = ...
0
votes
1
answer
63
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 ...
1
vote
2
answers
72
views
How to enforce entity existence across N-tables - postgres
Let's say we decided to split user table in two, one will have data related to authentication, another basic user description:
user_table
user_id | name
1 | Max
2 | Alex
3 | ...
0
votes
1
answer
791
views
Making use of ENUM in CONSTRAINT
I am creating a PostGIS database in which a lot of schemas should have the same constraints. Meaning in the future, similar tables should have the same limitations of values entered.
My constraint ...
1
vote
1
answer
82
views
is there unix timestamp range function in PostgreSQL 13
I have a table that stored user subscribe information, now I want to add a constraint to make the user did not subscribe the product multiple times in the same peroid of time. What I am tried to do ...
1
vote
1
answer
204
views
Constraint checking that foreign key matches with column in same table as primary key
I have the following SQL schemas.
-- name is primary key
Programs (name, abbr)
-- idnr is primary key
Students (idnr, name, login, program)
program → Programs.name
-- name is primary key
...
6
votes
2
answers
939
views
Make two columns unique both ways
I'm creating a database which stores football matches.
Each round, any given football club may only play once (home or away).
For example, after the insert of
INSERT INTO Match (Round, Home, Away) ...
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 ...
0
votes
1
answer
521
views
In PostgreSQL, what to do after VALIDATE CONSTRAINT succeeds on a constraint marked with NO VALID?
So, I get the rough gist of changing constraints on existing columns with PostgreSQL:
Use NO VALID to ensure the new constraint does not lock the table.
Migrate the old data at your leisure.
Run ...
1
vote
1
answer
371
views
How can I load a custom format dump from pg_dump into an RDS while ignoring constraint checks during the import?
I have a number of constraints:
I have binary custom format dump from Heroku that I need to load via pg_restore.
I need to load this onto an AWS RDS instance, where I don't have full superuser (only ...
3
votes
2
answers
990
views
Implement foreign key constraint when there are several parent tables
It might be the case that my initial design is flawed, but let's start from there and see whether there is a good enough approach.
I have two entities, say, A and B that are very similar but not ...
0
votes
1
answer
154
views
Make sure that such a field combination exists in another table when inserting
Table A:
id | name | user_id
Table B:
id | type
Table A_B
id | table_a_id | table_b_id | user_id
Table A_B is a standard linking table for a many-to-many relationship except that probably using a ...
2
votes
2
answers
1k
views
Self-referential record, potentially referencing the same row, with a uuid pkey and a not null and foreign key constraint?
Scenario
A table holds records that represent a chain (or, more accurate yet, any number of independent chains, respectively).
In addition to referencing the direct ancestor in the chain, a record ...
0
votes
1
answer
923
views
DEFERRABLE not working as expected in Postgres
My app has the requirement that every table has a "created_by" column that references to the id column of user table. Even the user table. That means the created_by column would be a ...
4
votes
1
answer
651
views
When exactly are IMMEDIATE constraints checked and what exactly is a "statement" in Postgres?
I'm currently confused on the exact timing of IMMEDIATE constraint checks. Hopefully, the following example captures my confusion:
create table a (
id int primary key
);
create table b (
id int ...
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 ...
0
votes
1
answer
102
views
Impose limits on number of records stored in a table
Assume we have a table with following schema:
favorites{
UserID
CategoryID
}
How can I enforce a policy which ensures each users has at least 3 categories and at most 5 categories. Is it ...
0
votes
1
answer
48
views
What constraint can't be written as a table constraint?
I am reading the Postgres 7.3 documentation to add a constraint to a PostgreSQL table.
It says:
To add a not-null constraint, which cannot be written as a table constraint, use this syntax:
ALTER ...
2
votes
1
answer
183
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 ...
2
votes
0
answers
57
views
How to enforce NOT NULL 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 ...
0
votes
1
answer
820
views
How to generate constraint sql?
I'm looking for a method that can generate the creating sql, like PgAdmin's SQL tab while having selected the constraint. (how pg admin does it?)
I want to implement a generic method that can drop and ...
6
votes
1
answer
10k
views
Column constraint based on values in another column
I have a PostgreSQL table phase_steps, with the following example rows:
phase_step_id|step_type|step_status|
-------------+---------+-----------+
1| RESEARCH| |
2| ...
2
votes
2
answers
222
views
Enforce that every company has at least one CEO
I've a requirement to ensure that every company has at least one CEO.
Example database model with requirements db<>fiddle.
Basically, a CEO can't be removed from a company without naming another ...
7
votes
1
answer
5k
views
Constraint in ON CONFLICT clause has no associated index
I have this simple setup using PostgreSQL 13, latest dockerized:
CREATE TABLE stop (
id INT UNIQUE,
name TEXT,
PRIMARY KEY(id)
);
CREATE TABLE line (
id INT UNIQUE,...
1
vote
1
answer
2k
views
How to resolve duplicate constraint names in Postgres?
On Postgres 9.6, I have a table where a foreign key constraint and a unique constraint have the same name (ultimately due to long distinct names being silently truncated to 63 chars). I'm trying to ...
-1
votes
1
answer
49
views
Why PG 13 does not allow deferring statement trigger constraints?
I noticed in PG 13 docs that only row constraint triggers can be deferrable.
Why not statement constraint triggers?
1
vote
2
answers
898
views
Restrict insertion of new row if a row exists with certain field values
Using a Postgres DB. I have a table let say t with columns txn_type, txn_id. I want to add a constraint where it restricts to insert new row if I have a row with particular values existing in table.
e....
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 ...
-1
votes
1
answer
721
views
Postgresql uses slower constraint over faster index
I have a few tables (25m records +) which have a very INSERT, UPDATE and DELETE biased workload (typically 50,000 to 100,000 operations a day) where the query planner seems to make an odd choice of ...
5
votes
1
answer
8k
views
Postgres: Unsafe use of new value of enum type
I want to alter a constraint after I have added a new value to an enum I use. So I originally have an enum called activity_state where I want to add a new value. But I also need to change the ...
3
votes
3
answers
9k
views
Do I need to VALIDATE CONSTRAINT?
I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check.
From the documentation:
But the database will not assume that the constraint ...
0
votes
1
answer
154
views
Postgres: find all FK constraints associated with a given PK
Given some input PK column, e.g. ani.animals.id, I want to find all FK constraints names associated with that PK.
I also need table and schema's names where each particular FK constraint is located.
...
1
vote
1
answer
4k
views
Why is ADD CONSTRAINT ... NOT VALID taking a long time?
I added NOT NULL constraints to some columns like this, expecting the command to be very fast because of NOT VALID. However, it took about 60-100 seconds, and seemed to lock the table for the whole ...
0
votes
0
answers
1k
views
Postgresql 12 Truncate partition with foreign key
I am building a datawarehouse using Postgresql (12) and think I should be using partitions on the most populated tables, for performance and maintanability reasons.
I have something along the lines ...
1
vote
1
answer
5k
views
View PostgreSQL column constraints
I applied a constraint but forgot its name. Now I want to drop it using ALTER TABLE [TABLENAME] DROP CONSTRAINT [CONSTRAINTNAME]. If there is a way to drop all constraint from a column that should ...
9
votes
1
answer
9k
views
PostgreSQL - How to list all foreign key link to Primary key of a table
I have a table res_users with primary key id
I would like to list all the foreign key link to id from all the tables in my database.
1
vote
0
answers
25
views
Only one truthy bool value for one foreign key in postgre [duplicate]
Lets assume we have 2 database tables (entites).
The first is named A and the second one is named B.
There are 1:N (where N > 0) relationship (so every record in table A is in relation with 1+ records ...
1
vote
1
answer
1k
views
How do I impose a CONSTRAINT on a TEXT column in PostgreSQL 12?
I want a column with a single text character as a classifier so it's not worth the overhead creating another whole table and relating to it.
How do I constrain the column to a list of acceptable ...
1
vote
2
answers
275
views
Why is this PostgreSQL not null constraint not catching this insert?
I have a table for users that includes two columns for contact information. A user can provide a phone number, an email, or both, but not neither.
I have the table set up with the following ...
0
votes
1
answer
3k
views
Postgres: foreign key constraint for components of a composite type?
In Postgres, is it possible to set up a foreign key contraint involving components of a composite type, in a situation like the following one?
Suppose I have
CREATE TABLE main (
-- a and b are ...
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),
...
0
votes
1
answer
415
views
Postgres: Can I associate index with FK constraint?
Postgres FK constraint and index are independent.
Recently I was looked for unused indexes and found trick to eliminate indexes needed for constraint enforcing by:
WHERE NOT EXISTS (SELECT 1
...
5
votes
1
answer
2k
views
Prevent drop column with index on it in PostgreSQL
In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in ...