Viewing Query Error Information

[Deleted User]

This tip was authored by Jim Knicely.

The V_MONITOR.ERROR_MESSAGES system table tracks error and warning messages encountered while processing queries.

Example:

=> CREATE TABLE 123 (c1 INT);
ERROR 4856:  Syntax error at or near "123" at character 14
LINE 1: CREATE TABLE 123 (c1 INT);
                     ^

=> SELECT event_timestamp, user_name, message FROM error_messages ORDER BY event_timestamp DESC LIMIT 1;
        event_timestamp        | user_name |            message
-------------------------------+-----------+-------------------------------
2018-02-12 09:50:17.836661-05 | dbadmin   | Syntax error at or near "123"
(1 row)

If you need a bit more info, like the cursor position of a syntax error, you can query the data collector table DC_ERRORS.

Example:

=> SELECT time, user_name, message, cursor_position FROM dc_errors ORDER BY time DESC LIMIT 1;
             time              | user_name |            message            | cursor_position
-------------------------------+-----------+-------------------------------+-----------------
2018-02-12 09:50:17.836661-05 | dbadmin   | Syntax error at or near "123" |              14
(1 row)