Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables:

psql -U usernamefoo databasenamefoo -f dml_script.sql

The table constraints have been created with the DEFERRABLE option and the script itself (file dml_script.sql above) issues the following before an offending INSERT:


I've checked that the specific constraint on the table in question has been created with the DEFRRABLE option:


Yet, running the DML script fails:

psql:./schema-dml-test.sql:8: ERROR:  insert or update on table "km_transaction_groups" violates foreign key constraint "trgr_trgr_fk"
DETAIL:  Key (trgr_parent_id)=(2) is not present in table "km_transaction_groups".

My guess is that psql treats each INSERT line in the dml_script.sql as a separate transaction. If that's the reason, is there a way to group INSERT statements in the dml_script.sql file into one longer transaction (or perhaps treat the whole file as one single transaction) so that at its end (when the scope of the SET CONSTRAINTS ALL DEFERRED; terminates), no constraints are violated?

Otherwise some other method (other then changing the order of the INSERT statements which I have reasons I don't want to) to allow me to load the table with a series of INSERT statements so that constraints can be temporarily deactivated ?

share|improve this question
add comment

migrated from serverfault.com Oct 8 '12 at 18:49

This question came from our site for professional system and network administrators.

1 Answer

up vote 5 down vote accepted

The -1 option to psql causes it to wrap a file specified by -f in a BEGIN..COMMIT block, making it a transaction.

Otherwise, add the BEGIN and COMMIT commands to your script so that it becomes a single transaction.

share|improve this answer
Yup - by default interactive psql sessions are "every line is a transaction": You need explicit BEGIN/ROLLBACK/COMMIT statements if you want a multi-statement transaction... –  voretaq7 Oct 5 '12 at 14:08
See the documentation: Transactions –  ypercube Oct 8 '12 at 18:53
You can also turn off that dreaded autocommit mode using: \set AUTOCOMMIT off (I have that in my psqlrc file) –  a_horse_with_no_name Oct 8 '12 at 21:46
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.