Skip to main content

All Questions

Tagged with
Filter by
Sorted by
Tagged with
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 ...
Evan Carroll's user avatar
  • 64.7k
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 ║ ...
Rohit's user avatar
  • 11
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 ...
Sassa's user avatar
  • 135
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)...
Timur Shtatland's user avatar
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 ...
riwu's user avatar
  • 133
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 ...
Mario Ishac's user avatar
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 ...
Felipe's user avatar
  • 305
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 = ...
Will Newton's user avatar
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 ...
psygo's user avatar
  • 121
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 | ...
ZiiMakc's user avatar
  • 135
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 ...
KoGIS's user avatar
  • 3
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 ...
Dolphin's user avatar
  • 825
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 ...
NoName123's user avatar
  • 113
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) ...
pear's user avatar
  • 63
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 ...
Don Draper's user avatar
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 ...
rationaltiger24's user avatar
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 ...
DaleSwanson's user avatar
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 ...
Don Draper's user avatar
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 ...
Don Draper's user avatar
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 ...
Johannes Pille's user avatar
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 ...
Alex's user avatar
  • 165
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 ...
user2959071's user avatar
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 ...
Andrew Polukhin's user avatar
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 ...
milad's user avatar
  • 101
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 ...
Joey Yi Zhao's user avatar
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 ...
Eerik Sven Puudist's user avatar
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 ...
Eerik Sven Puudist's user avatar
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 ...
cdalxndr's user avatar
  • 141
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| ...
Rudy Stricklan's user avatar
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 ...
Anton Bessonov's user avatar
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,...
jlanza's user avatar
  • 171
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 ...
usethe4ce's user avatar
  • 121
-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?
geeko's user avatar
  • 31
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....
Arshad Munir's user avatar
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 ...
Robert Elliot's user avatar
-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 ...
F--S's user avatar
  • 1
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 ...
Pierre's user avatar
  • 153
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 ...
xehpuk's user avatar
  • 317
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. ...
weno's user avatar
  • 115
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 ...
John Bachir's user avatar
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 ...
alci's user avatar
  • 452
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 ...
No Name's user avatar
  • 15
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.
tuanngocptn's user avatar
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 ...
Johnczek's user avatar
  • 111
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 ...
skeetastax's user avatar
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 ...
Adam's user avatar
  • 123
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 ...
gernot's user avatar
  • 145
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), ...
Imanol Y.'s user avatar
  • 785
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 ...
gavenkoa's user avatar
  • 519
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 ...
Marat Safin's user avatar