Is there any way to see information about error in postgresql?

Question

I'm new using PostgreSQL, I'm looking for more information about errors, for example if I run this:

select top 10 * from TABLE

I'm getting this error:

ERROR:  syntax error at or near "10"
LINE 2: select top 10 * from TABLE
                   ^
SQL state: 42601
Character: 13

I'm expecting to get something more. So, I've tried this from here https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES but is not working (or I don't understand how to use it):

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
   SELECT TOP 10 * FROM tbl;  -- obviously incorrect

EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;

Well this, `select top 10 * ...` is an invalid query period, so I don't see what more information you want. Spend some time here [Select](https://www.postgresql.org/docs/current/sql-select.html). — Adrian Klaver, Mar 11 '23 at 23:29
the point is, I want to see information about errors, I know that "top 10" is not valid and should be replace with "limit" — Araceli Fernández, Mar 11 '23 at 23:36
You are not going to get that. There are too many possible combinations to have the server write queries for you. You have to put the time into studying SQL to take the messages and figure out how it applies in that particular situation. — Adrian Klaver, Mar 11 '23 at 23:46
well, thanks. If I didn't explain myself well, I'm new at PostgreSQL, I work with Microsoft SQL, which have more explanation about the errors and that is what I'm asking here. — Araceli Fernández, Mar 11 '23 at 23:59
Postgres offers explanations for errors it can make a reasonable guess about. In your example, possible errors include, this could have been 1) `select 'top',10, * from TABLE` 2) `select top, 10, * from TABLE, other_table` where `top`is a column name for a missing table 3) `select top(10), * from TABLE` and so on. Rather then making assumptions it just returned a minimal error. - — Adrian Klaver, Mar 12 '23 at 00:10
With a syntactically impossible statement like yours, the parser has no option but to report a syntax error pointing to the first token that makes the statement impossible. `top` could be a column or function name, but then it would have to be followed by `,`, an alias or `(`. The parser cannot guess what you intended to do. — Laurenz Albe, Mar 12 '23 at 12:38

Erwin Brandstetter · Answer 1 · 2023-03-12T00:17:25.127

The problem with your PL/pgSQL code block is that it fails early at the superficial syntax check. It already raises an exception before even executing any of your code.

To get your test running, you could sneak the broken statement past the superficial syntax check with dynamic SQL code in EXECUTE. Then it gets actually executed and raises the expected error that you can trap with the EXCEPTION clause. Like:

DO
$do$
DECLARE
   text_var1 text;
   text_var2 text;
   text_var3 text;
   text_var4 text;
BEGIN
   EXECUTE 'SELECT TOP 10 * FROM tbl';

EXCEPTION WHEN others THEN
   GET stacked DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                           text_var2 = PG_EXCEPTION_DETAIL,
                           text_var3 = PG_EXCEPTION_HINT,
                           text_var4 = PG_EXCEPTION_CONTEXT;
   RAISE EXCEPTION E'My custom err msg:\n%\n%\n%\n%'
                 , text_var1, text_var2, text_var3, text_var4;
   -- or whatever you want to do here ...
END
$do$

That said, you won't learn much more than what you already see from the original error msg you displayed.

Is there any way to see information about error in postgresql?

1 Answers1