There are quite a few reasons why you might want deferrable constraints, but not all the reasons follow traditional best practices. This is the setting we used above in our example. UNIQUE (number) DEFERRABLE INITIALLY DEFERREDĬonstraints are validated at transaction commit time and this setting can change per transaction. INITIALLY DEFERRED CREATE TABLE numbers ( I would not recommend this setting as I consider per-connection, per-transaction, and per-query settings an anti-pattern that are likely covering up a root issue. UNIQUE (number) DEFERRABLE INITIALLY IMMEDIATEĬonstraints are validated immediately, but this setting can change per transaction. This is the default for both primary keys and unique indexes.ĭEFERRABLE CREATE TABLE numbers ( NOT DEFERRABLE (default)Ĭonstraints are validated immediately and this setting can't change per transaction. This gives us three different combinations of settings we can create constraints with: Under the ALTER TABLE documentation synopsis we can see that the legal syntax for ALTER TABLE name ALTER CONSTRAINT is:ĪLTER CONSTRAINT constraint_name Public | numbers | numbers_number_key | t | tĪnd if we try that UPDATE statement again we can see it now succeeds: UPDATE numbers Which means we have to drop the constraint entirely and recreate it, but thankfully we can do that in one statement: ALTER TABLE numbersĪDD CONSTRAINT numbers_number_key UNIQUE (number) DEFERRABLE INITIALLY DEFERRED Īnd if we run the above pg_constraint query we can see the change take place: schema | table | constraint | deferrable | deferred We got an unhelpful error! That's because buried under ALTER CONSTRAINT in the ALTER TABLE documentation we can find:Ĭurrently only foreign key constraints may be altered. ERROR: constraint "numbers_number_key" of relation "numbers" is not a foreign key constraint Let's try changing the existing constraint on our table: ALTER TABLE numbersĪLTER CONSTRAINT numbers_number_key DEFERRABLE 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. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |