![]() ![]() PostgreSQL v9.4 (2014) added the ability to execute ALTER TABLE ALTER CONSTRAINT. ❌ The following types of constraints can't be deferred, which means PostgreSQL deviates from the SQL standard some: PRIMARY KEY (but I wouldn't do it, see the performance considerations below).✅ The following types of constraints can be deferred: The constraint can be temporarily DEFERRED if it is DEFERRABLE with one of these statements: SET CONSTRAINTS numbers_number_key DEFERRED The constraint can be created as INITIALLY DEFERRED which will set the constraint to DEFERRED by default. A constraint can be DEFERRED two different ways: When a constraint is DEFERRED it is not validated until the transaction commits. Public | numbers | numbers_number_key | f | f INNER JOIN pg_namespace ns ON ns.oid = class.relnamespaceĪND ns.nspname != 'pg_catalog' ORDER BY 1, 2, 3 Īnd we can see that indeed the unique constraint is NOT DEFERRABLE ( deferrable = false) and INITIALLY IMMEDIATE ( deferred = false): schema | table | constraint | deferrable | deferred INNER JOIN pg_class class ON class.oid = con.conrelid We can see every constraint in our database with this query: SELECT ns.nspname AS schema NOT DEFERRABLE means we can't change this setting in a transaction.INITIALLY IMMEDIATE means that the constraint is being validated after each row is updated individually by default.deferredĬonstraints are NOT DEFERRABLE INITIALLY IMMEDIATE by default in PostgreSQL. ERROR: duplicate key value violates unique constraint "numbers_number_key" - DETAIL: Key (number)=(2) already exists. If we try to update every row in the table, incrementing the value by 1, we run into a problem: UPDATE numbers Constraints in PostgreSQL are validated immediately row-by-row by default, which might be confusing when updating multiple values in columns that have a uniqueness constraint.Ĭonsider this contrived scenario of one table with one column that has a unique constraint (and index): CREATE TABLE numbers ( ![]()
0 Comments
Leave a Reply. |