Обсуждение: Temporarily disable not null constraints
Hi,
Can we disable not null constraints temporarily in the session-based transaction, like we disable FK constraints?
SET session_replication_role = ‘replica’;
alter table table_name disable trigger user;”
above two options are working for unique constraints violation exception.
Thanks,
Rj
On Thu, Dec 3, 2020 at 1:00 PM Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
Hi,Can we disable not null constraints temporarily in the session-based transaction, like we disable FK constraints?SET session_replication_role = ‘replica’;alter table table_name disable trigger user;”above two options are working for unique constraints violation exception.Thanks,Rj
You can alter the column and remove the not null constraint, do your work, and then add it back, but it will have to verify all rows have that column set, that is, you can't leave some of them null.
generally, you shouldn't be disabling your constraints, especially if you are having multiple parallel processes accessing your db.
instead, you should create them DEFERRABLE and have them checked at the end of your transaction.
regarding your question about NOT NULL: it is not possible to have it deferred (please check this page: https://www.postgresql.org/docs/13/sql-set-constraints.html)
you may alter your column, remove it, and then get it back, but still all rows will have to be checked, which I doubt you would like to see on a large table.
regards, milos
On Thu, Dec 3, 2020 at 9:00 PM Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
Hi,Can we disable not null constraints temporarily in the session-based transaction, like we disable FK constraints?SET session_replication_role = ‘replica’;alter table table_name disable trigger user;”above two options are working for unique constraints violation exception.Thanks,Rj
On Thu, Dec 03, 2020 at 07:58:15PM +0000, Nagaraj Raj wrote: > Can we disable not null constraints temporarily in the session-based transaction, like we disable FK constraints? If you're trying to temporarily violate the not-null constraint.. I don't know if it's a good idea.. ..but maybe this feature in v12 helps you: https://www.postgresql.org/docs/12/sql-altertable.html | Ordinarily this is checked during the ALTER TABLE by scanning the entire table; however, if a valid CHECK constraint isfound which proves no NULL can exist, then the table scan is skipped. When you're done violating constraints, you can ALTER .. ADD CONSTRAINT .. CHECK (.. IS NOT NULL) NOT VALID, and then ALTER .. VALIDATE CONSTRAINT, and then ALTER column SET NOT NULL. -- Justin