PostgreSQL – disabling constraints

by

Per previous comments, it should be a problem. That said, there is a command that may be what you’re looking to – it’ll set the constraints to deferred so they’re checked on COMMIT, not on every delete. If you’re doing just one big DELETE of all the rows, it won’t make a difference, but if you’re doing it in pieces, it will.

SET CONSTRAINTS ALL DEFERRED

is what you are looking for in that case. Note that constraints must be marked as DEFERRABLE before they can be deferred. For example:

ALTER TABLE table_name
  ADD CONSTRAINT constraint_uk UNIQUE(column_1, column_2)
  DEFERRABLE INITIALLY IMMEDIATE;

The constraint can then be deferred in a transaction or function as follows:

CREATE OR REPLACE FUNCTION f() RETURNS void AS
$BODY$
BEGIN
  SET CONSTRAINTS ALL DEFERRED;

  -- Code that temporarily violates the constraint...
  -- UPDATE table_name ...
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

More Related Contents:

  1. Postgresql: Conditionally unique constraint
  2. CONSTRAINT to check values from a remotely related table (via join etc.)
  3. How to add “on delete cascade” constraints?
  4. Why can I create a table with PRIMARY KEY on a nullable column?
  5. Does a Postgres UNIQUE constraint imply an index?
  6. Insert, on duplicate update in PostgreSQL?
  7. How does the search_path influence identifier resolution and the “current schema”
  8. How do you find the row count for all your tables in Postgres
  9. Run batch file with psql command without password
  10. invalid byte sequence for encoding “UTF8”
  11. How do you use variables in a simple PostgreSQL script?
  12. How to switch databases in psql?
  13. Postgresql: Scripting psql execution with password
  14. dynamic sql query in postgres
  15. Postgresql Truncation speed
  16. Buffers (Circle) in PostGIS
  17. docker postgres pgadmin local connection
  18. How to customize the configuration file of the official PostgreSQL Docker image?
  19. SELECT raises exception in PL/pgSQL function
  20. Dynamically generate columns for crosstab in PostgreSQL
  21. Postgresql insert trigger to set value
  22. postgres autoincrement not updated on explicit id inserts
  23. Retrieving Comments from a PostgreSQL DB
  24. Docker – How can run the psql command in the postgres container?
  25. Postgis installation: type “geometry” does not exist
  26. how to store PostgreSQL jsonb using SpringBoot + JPA?
  27. PostgreSQL how to concat interval value ‘2 days’
  28. Orbeon Forms Postgres DB connection
  29. now() default values are all showing same timestamp
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment