Question 185014 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres keeps complaining about the EXCEPTION keyword

According to the 43.6.8. Trapping Errors topic in this Postgres Documentation, the correct syntax for handling exceptions is:

BEGIN;
-- SQL Statement
EXCEPTION WHEN Others THEN 
    -- Exception Handling Logic
END;

This is while when I use this syntax in pgAdmin, it keeps complaining about the keyword EXCEPTION.

My Query is:

BEGIN;
    INSERT INTO TestTable (id) VALUES(3);
    COMMIT;
    EXCEPTION WHEN Others THEN 
        ROLLBACK;
END;

And the error is:

ERROR:  syntax error at or near "EXCEPTION"
LINE 4:  EXCEPTION WHEN Others THEN 
         ^ 

SQL state: 42601
Character: 57

I don’t understand what could be possibly wrong with this SQL Code. If this is not the right way to handle exceptions, then how can I implement something similar?

UPDATE: This is NOT Aurora Postgres. I am using Standard Postgres.

Tags:

4

Answers


  1. 0

    What you quote is the PL/pgSQL documentation, but you have to enter SQL in the query tool. These are different languages, so what would be correct PL/pgSQL causes an error.

    If you want to use PL/pgSQL, you have to create a function or procedure, or you could use it in a DO statement in SQL.

    There are two more problems with your code:

    • there cannot be a semicolon after BEGIN, so you must have mis-quoted the documentation

    • you cannot use a transaction management statement like COMMIT in a block with an exception handler

    Login or Signup to reply.
  2. 0

    You can refer here to get an idea on how to handle exceptions in PostgreSQL.

    Here’s the pseudocode(from same link), it might give you an idea:

    select version() as postgresql_version
$Body$
declare
begin
    -- statements;
exception
    when condition [or condition...] then
       handle_exception;
   [when others then
       handle_other_exceptions;
   ]
end;
$Body$
    Login or Signup to reply.
  3. 0

    You say that you are using PgAdmin, so i presume that you are trying to run this code using a query tool window?

    In which case you need to place everything within a DO like this:

    do $$
BEGIN
    INSERT INTO testtable (id) VALUES(1);
    INSERT INTO testtable (id) VALUES(2);
    INSERT INTO testtable (id) VALUES(1);
    EXCEPTION 
    WHEN Others THEN 
    RAISE NOTICE 'error occurred';
END; $$

    Note that there is no need for a commit or a rollback. If you try this code, assuming id is a primary key, you will find that nothing has been inserted. PostgreSQL automatically treats all the statements as one transaction, and rolls them all back, when an exception occurs.

    Login or Signup to reply.
  4. 0 
    BEGIN
    -- SQL Statements
    BEGIN
        INSERT INTO TestTable (id) VALUES (3);
        COMMIT;
    EXCEPTION WHEN others THEN
        ROLLBACK;
        -- Exception Handling Logic
        RAISE NOTICE 'An error occurred: %', SQLERRM;
    END;
END;

    In this updated code:

    The BEGIN block encompasses all the SQL statements you want to execute.
    Inside the BEGIN block, you can have nested BEGIN … END blocks for more specific exception handling.
    The EXCEPTION WHEN others THEN block catches any exception that occurs within the nested block.
    Inside the exception block, you can perform exception handling logic, such as rolling back the transaction, logging the error, or raising a notice using RAISE NOTICE.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search