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:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |