This error occurs when you attempt to use GET STACKED DIAGNOSTICS outside of an exception handler block in PL/pgSQL. GET STACKED DIAGNOSTICS can only be called within an EXCEPTION WHEN clause to retrieve diagnostic information about the current exception.
PostgreSQL's GET STACKED DIAGNOSTICS is a PL/pgSQL command designed specifically to retrieve detailed information about exceptions that have been raised. It works by accessing the exception context stack, which only exists when an exception is actively being handled within an EXCEPTION WHEN block. When you try to call GET STACKED DIAGNOSTICS outside of an exception handler—either in normal code flow or in other blocks—PostgreSQL raises error 0Z002 because there is no active exception context to access.
Ensure GET STACKED DIAGNOSTICS is only called within an EXCEPTION WHEN clause. Here's the correct structure:
BEGIN
-- Your code that might raise an exception
SELECT 1 / 0; -- This will raise an exception
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
v_state = RETURNED_SQLSTATE,
v_msg = MESSAGE_TEXT;
-- Now you can use these variables
END;The GET STACKED DIAGNOSTICS statement must be inside the EXCEPTION WHEN block to access the exception context.
When inside an exception handler, you can retrieve different types of diagnostic information:
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
v_sqlstate = RETURNED_SQLSTATE, -- The error code (e.g., '23505')
v_message = MESSAGE_TEXT, -- The error message
v_detail = PG_EXCEPTION_DETAIL, -- Additional error details
v_hint = PG_EXCEPTION_HINT, -- Suggested fix
v_context = PG_EXCEPTION_CONTEXT, -- Stack trace
v_table = TABLE_NAME, -- Affected table (constraint errors)
v_column = COLUMN_NAME; -- Affected column (constraint errors)
END;Select only the diagnostic items you need. If not set by the exception, they return empty strings.
If you need diagnostic information outside an exception handler (like row counts after INSERT/UPDATE), use GET DIAGNOSTICS without the STACKED keyword:
BEGIN
INSERT INTO users (name) VALUES ('Alice');
GET DIAGNOSTICS v_row_count = ROW_COUNT;
RAISE NOTICE 'Inserted % rows', v_row_count;
EXCEPTION WHEN OTHERS THEN
-- For exception info, use GET STACKED DIAGNOSTICS here
END;GET DIAGNOSTICS works in the main code flow, while GET STACKED DIAGNOSTICS only works in exception handlers.
Create a test case that actually raises an exception to verify your handler works:
DO $$
DECLARE
v_msg TEXT;
BEGIN
-- Intentionally cause a division by zero
PERFORM 1 / 0;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS v_msg = MESSAGE_TEXT;
RAISE NOTICE 'Caught error: %', v_msg;
END;
$$;Run this in psql or your database tool to confirm the GET STACKED DIAGNOSTICS call works correctly within the exception handler.
The difference between GET DIAGNOSTICS and GET STACKED DIAGNOSTICS is important: GET DIAGNOSTICS returns information about the last non-exception statement (like number of rows affected), while GET STACKED DIAGNOSTICS retrieves information about the current exception being handled. GET STACKED DIAGNOSTICS is SQL standard compliant and was introduced in PostgreSQL 9.2 as a replacement for special variables like SQLSTATE and SQLERRM, though those variables still work. When using GET STACKED DIAGNOSTICS with constraint violations, you can access constraint-specific fields like TABLE_NAME, COLUMN_NAME, and CONSTRAINT_NAME. Note that accessing exception blocks has performance overhead due to savepoint creation, so avoid wrapping code in exception handlers unless necessary.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL