EXCEPTION WHEN OTHERS THEN NULL in Postgres

Question

I have an exception in Oracle PL/SQL that needs to be ported to PostgreSQL pl/pgsql. Below is the oracle variant

EXCEPTION 
WHEN OTHERS THEN 
NULL;

What is the PL/PGSQL variant?

(1) Please provide more details about the code (perhaps simplified). (2) The general topic of exception handling in Postgres or Oracle is probably too broad for a Stack Overflow question. — Gordon Linoff
– Gordon Linoff, Commented May 20, 2020 at 13:33

pifor · Accepted Answer · 2020-05-20 13:40:01Z

4

It's the same syntax. Compare the 2 following executions:

DO
$$
BEGIN
RAISE division_by_zero;
EXCEPTION 
WHEN OTHERS THEN 
NULL; 
END;
$$

DO

And:

DO
$$
BEGIN
RAISE division_by_zero;
END;
$$

ERROR:  division_by_zero
CONTEXT:  PL/pgSQL function inline_code_block line 3 at RAISE

answered May 20, 2020 at 13:40

pifor

7,9722 gold badges11 silver badges18 bronze badges

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

Comments

J. Chomel · Accepted Answer · 2020-05-20 13:43:58Z

1

First, avoid trapping "any exception"s like that with no specific behavior at all. Except for very simple functions, consider logging it somewhere, or rewrite the code in a more elegant manner.

I found it here: apparently, you don't need to put anything at all.

You should try the following:

EXCEPTION 
  WHEN OTHERS THEN 
  -- Do nothing

Hope it helps.

answered May 20, 2020 at 13:43

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Comments

Belayer · Accepted Answer · 2020-05-20 19:06:23Z

1

DO NOT port this as it is. It is a BUG ALWAYS. If you do nothing else at least log the error. If where it occurs is interactive, or data loading, or report generating, or a whole host of others then create some kind of message indicating the process failed. If users are depending on this data and it is not there it is your application that is broken, not the users expectations. I understand your are migrating a system but while doing so you should not carry forward obvious bugs. This is one.

answered May 20, 2020 at 19:06

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

1 Comment

Sampson Crowley Over a year ago

that's a blatantly overreaching statement. there are quite a few reasons to use "WHEN OTHERS THEN NULL"; for instance, PostgreSQL, doesn't have CREATE OR REPLACE for triggers, AND there is no need to recreate triggers when functions of the same name are replaced. using an OTHERS WHEN exception is a clean way to CREATE OR IGNORE if a trigger already exists, since there is absolutely no reason to create it again

Collectives™ on Stack Overflow

EXCEPTION WHEN OTHERS THEN NULL in Postgres

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related