Custom error message for postgresql check constrint

Question

I have the "valid_id" check constraint on my requests table. But when it violates the constraint it shows following error

ERROR: new row for relation "requests" violates check constraint "valid_name" DETAIL: Failing row contains ....

But instead of that I want to show message like "Failed to insert record. name is required".

Is there any way to show the custom error message in PostgreSQL?

My bad. Its "valid_name" Whenever I try to insert the null in the name field then it gives the error but instead of that I want to show my custom error message — Rohan Patil
– Rohan Patil, Commented Jan 18, 2013 at 12:42
You could name it Failed_to_insert_record_name_is_required instead of valid_name. — regilero
– regilero, Commented Jan 18, 2013 at 12:46

Chris Travers · Accepted Answer · 2013-04-21 10:06:48Z

1

This is kind of advanced territory here because you want to be pretty familiar with SQL states as well as existing error messages before you get started. Note that you want to re-used existing sql states as appropriate so that the application doesn't know you have overridden your check constraint.

But what you can do is create a function which runs the check and issues a raise exception if the check fails. Something like:

CREATE FUNCTION check_is_not_null(value text, column_name text) RETURNS BOOL 
LANGUAGE plpgsql AS $$
begin
   IF $1 IS NULL THEN
      RAISE EXCEPTION 'Error:  % is required', $2;
   END IF;
   RETURN TRUE;
END;
$$;

If you are using 8.4 or higher, you can specify an SQL state.

answered Apr 21, 2013 at 10:06

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

4 Comments

Craig Ringer Over a year ago

Note that you still can't control the whole error message produced by psql or most other PostgreSQL clients. For example, psql will always prefix ERROR: or the appropriate translation for the currently active language.

Chris Travers Over a year ago

@CraigRinger More specifically, psql prefixes such messages with the error level and a colon, right? FATAL:, WARNING:, and NOTICE:

Craig Ringer Over a year ago

Yes, at the default error verbosity level. The error level is IIRC a translatable string, but should be easy to strip by dropping everything before the colon. Other clients may behave differently, eg PgJDBC.

thunder Over a year ago

Yes. I'm not sure where the prefix creeps in but in Java at least, if you cast the SQLException up to PSQLException (the postgres specialization) then you can get the raw message without any 'ERROR:' prefix using psqle.getServerErrorMessage().getMessage();

Collectives™ on Stack Overflow

Custom error message for postgresql check constrint

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related