How to raise an error with PostgreSQL select statement?

Question

I need to create a data patching script and I'd like to rollback the transaction when some condition occurs.

I could do something like this in the end of the script:

select t.id / 0
from some_table t
where t.state = 'undersirable state'

but I'd rather have a more appropriate error message than "division by zero".

Is there a generic function for generating errors in PostgreSQL? I'd like to do it without PL/SQL, if possible.

"Is there a generic function for generating errors in PostgreSQL? I'd like to do it without PL/SQL, if possible." No there isn't anny for plain queries like these.. — Raymond Nijland
– Raymond Nijland, Commented Jun 14, 2019 at 9:22
"I'd like to do it without PL/SQL" - How come? If you just want to avoid creating a function, you can run it from a DO block. — Nick Barnes
– Nick Barnes, Commented Jun 14, 2019 at 9:35
@NickBarnes within a script, like the OP here was writing, yeah, that's a valid approach. I've still sometimes specifically wanted an expression that throws an error, though, for use as e.g. the default value of a CASE expression used within a SELECT or UPDATE. — Mark Amery
– Mark Amery, Commented Apr 18, 2021 at 9:01

Laurenz Albe · Accepted Answer · 2019-06-14 09:23:49Z

7

Write a little function that raises the error for you and use that in your SELECT statement.

CREATE FUNCTION raise_error() RETURNS integer
   LANGUAGE plpgsql AS
$$BEGIN
   RAISE EXCEPTION /* whatever you want */;
   RETURN 42;
END;$$;

answered Jun 14, 2019 at 9:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

That is brilliant! It is so obvious solution, yet I did not find it from any other place.