4

I am building a node app using node's pg client, and since it's a simple app I am not using app validations and resorting only to postgres constraints to protect data integrity and display insert and update errors.

Using the pg client I only get one constraint violation at once even though multiple violations occur.

Is it possible to set postgres to emit all constrait violations on an insert error?

Improve this question

1 Answer 1

Reset to default
6

Is it possible to set postgres to emit all constrait violations on an insert error?

Not with PostgreSQL, no, because the first violation throws an error that aborts the transaction.

Making PostgreSQL emit all violations would involve major surgery to the core PostgreSQL engine.

Validate in-app to provide user-friendly error messages and guidance; use database constraints and validations to enforce data integrity.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, I usually do in-app validations but I was experimenting with a really simple app to see if could cut some corners, some ORMs such as sequel for Ruby do table inspection to "autovalidate" based on constraints.
But an insert might reference multiple foreign key constraints (which enforce data integrity) and it's double work to first check those constraints in the application (using a query).
@malthe Yes, it is. A useful option can be to use named constraints in your foreign key constraints, e.g. CONSTRAINT blah FOREIGN KEY ... . Then match the constraint name when you get the error from the server. You can get the violated constraint name in the detailed fields of the error structure returned by the server - you shouldn't need to parse the error messages.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.