errors.rst

Errors

PostgREST error messages follow the PostgreSQL error structure. It includes MESSAGE, DETAIL, HINT, ERRCODE and will add an HTTP status code to the response.

Errors from PostgreSQL

PostgREST will forward errors coming from PostgreSQL. For instance, on a failed constraint:

POST /projects HTTP/1.1

HTTP/1.1 400 Bad Request
Content-Type: application/json; charset=utf-8

{
    "code": "23502",
    "details": "Failing row contains (null, foo, null).",
    "hint": null,
    "message": "null value in column \"id\" of relation \"projects\" violates not-null constraint"
}

HTTP Status Codes

PostgREST translates PostgreSQL error codes into HTTP status as follows:

PostgreSQL error code(s)	HTTP status	Error description
08*	503	pg connection err
09*	500	triggered action exception
0L*	403	invalid grantor
0P*	403	invalid role specification
23503	409	foreign key violation
23505	409	uniqueness violation
25006	405	read only sql transaction
25*	500	invalid transaction state
28*	403	invalid auth specification
2D*	500	invalid transaction termination
38*	500	external routine exception
39*	500	external routine invocation
3B*	500	savepoint exception
40*	500	transaction rollback
53400	500	config limit exceeded
53*	503	insufficient resources
54*	500	too complex
55*	500	obj not in prerequisite state
57*	500	operator intervention
58*	500	system error
F0*	500	config file error
HV*	500	foreign data wrapper error
P0001	400	default code for "raise"
P0*	500	PL/pgSQL error
XX*	500	internal error
42883	404	undefined function
42P01	404	undefined table
42P17	500	infinite recursion
42501	if authenticated 403, else 401	insufficient privileges
other	400

Errors from PostgREST

Errors that come from PostgREST itself maintain the same structure but differ in the PGRST prefix in the code field. For instance, when querying a function that does not exist in the :doc:`schema cache <schema_cache>`:

POST /rpc/nonexistent_function HTTP/1.1

HTTP/1.1 404 Not Found
Content-Type: application/json; charset=utf-8

{
  "hint": "...",
  "details": null
  "code": "PGRST202",
  "message": "Could not find the api.nonexistent_function() function in the schema cache"
}

PostgREST Error Codes

PostgREST error codes have the form PGRSTgxx.

• PGRST is the prefix that differentiates the error from a PostgreSQL error.
• g is the error group
• xx is the error identifier in the group.

Group 0 - Connection

Related to the connection with the database.

Code	HTTP status	Description
PGRST000	503	Could not connect with the database due to an incorrect :ref:`db-uri` or due to the PostgreSQL service not running.
PGRST001	503	Could not connect with the database due to an internal error.
PGRST002	503	Could not connect with the database when building the :doc:`Schema Cache <schema_cache>` due to the PostgreSQL service not running.
PGRST003	504	The request timed out waiting for a pool connection to be available. See :ref:`db-pool-acquisition-timeout`.

Group 1 - Api Request

Related to the HTTP request elements.

Code	HTTP status	Description
PGRST100	400	Parsing error in the query string parameter. See :ref:`h_filter`, :ref:`operators` and :ref:`ordering`.
PGRST101	405	For :ref:`functions <functions>`, only GET and POST verbs are allowed. Any other verb will throw this error.
PGRST102	400	An invalid request body was sent(e.g. an empty body or malformed JSON).
PGRST103	416	An invalid range was specified for :ref:`limits`.
PGRST105	405	An invalid :ref:`PUT <upsert_put>` request was done
PGRST106	406	The schema specified when :ref:`switching schemas <multiple-schemas>` is not present in the :ref:`db-schemas` configuration variable.
PGRST107	415	The Content-Type sent in the request is invalid.
PGRST108	400	The filter is applied to a embedded resource that is not specified in the select part of the query string. See :ref:`embed_filters`.
PGRST109	400	Restricting a Deletion or an Update using limits must include the ordering of a unique column. See :ref:`limited_update_delete`.
PGRST110	400	When restricting a Deletion or an Update using limits modifies more rows than the maximum specified in the limit. See :ref:`limited_update_delete`.
PGRST111	500	An invalid response.headers was set. See :ref:`guc_resp_hdrs`.
PGRST112	500	The status code must be a positive integer. See :ref:`guc_resp_status`.
PGRST114	400	For an :ref:`UPSERT using PUT <upsert_put>`, when :ref:`limits and offsets <limits>` are used.
PGRST115	400	For an :ref:`UPSERT using PUT <upsert_put>`, when the primary key in the query string and the body are different.
PGRST116	406	More than 1 or no items where returned when requesting a singular response. See :ref:`singular_plural`.
PGRST117	405	The HTTP verb used in the request in not supported.
PGRST118	400	Could not order the result using the related table because there is no many-to-one or one-to-one relationship between them.
PGRST119	400	Could not use the spread operator on the related table because there is no many-to-one or one-to-one relationship between them.
PGRST120	400	An embedded resource can only be filtered using the is.null or not.is.null :ref:`operators <operators>`.
PGRST121	500	PostgREST can't parse the JSON objects in RAISE PGRST error. See :ref:`raise headers <raise_headers>`.
PGRST122	400	Invalid preferences found in Prefer header with Prefer: handling=strict. See :ref:`prefer_handling`.
PGRST123	400	Aggregate functions are disabled. See :ref:`db-aggregates-enabled`.

Group 2 - Schema Cache

Related to a :ref:`schema_cache`. Most of the time, these errors are solved by :ref:`schema_reloading`.

Code	HTTP status	Description
PGRST200	400	Caused by stale foreign key relationships, otherwise any of the embedding resources or the relationship itself may not exist in the database.
PGRST201	300	An ambiguous embedding request was made. See :ref:`complex_rels`.
PGRST202	404	Caused by a stale function signature, otherwise the function may not exist in the database.
PGRST203	300	Caused by requesting overloaded functions with the same argument names but different types, or by using a POST verb to request overloaded functions with a JSON or JSONB type unnamed parameter. The solution is to rename the function or add/modify the names of the arguments.
PGRST204	400	Caused when the :ref:`column specified <specify_columns>` in the columns query parameter is not found.

Group 3 - JWT

Related to the authentication process using JWT. You can follow the :ref:`tut1` for an example on how to implement authentication and the :doc:`Authentication page <auth>` for more information on this process.

Code	HTTP status	Description
PGRST300	500	A :ref:`JWT secret <jwt-secret>` is missing from the configuration.
PGRST301	401	Any error related to the verification of the JWT, which means that the JWT provided is invalid in some way.
PGRST302	401	Attempted to do a request without :ref:`authentication <client_auth>` when the anonymous role is disabled by not setting it in :ref:`db-anon-role`.

Group X - Internal

Internal errors. If you encounter any of these, you may have stumbled on a PostgREST bug, please open an issue and we'll be glad to fix it.

Code	HTTP status	Description
PGRSTX00	500	Internal errors related to the library used for connecting to the database.

Custom Errors

You can customize the errors by using the RAISE statement on functions.

RAISE errors with HTTP Status Codes

Custom status codes can be done by raising SQL exceptions inside :ref:`functions <functions>`. For instance, here's a saucy function that always responds with an error:

CREATE OR REPLACE FUNCTION just_fail() RETURNS void
  LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'I refuse!'
    USING DETAIL = 'Pretty simple',
          HINT = 'There is nothing you can do.';
END
$$;

Calling the function returns HTTP 400 with the body

{
  "message":"I refuse!",
  "details":"Pretty simple",
  "hint":"There is nothing you can do.",
  "code":"P0001"
}

One way to customize the HTTP status code is by raising particular exceptions according to the PostgREST :ref:`error to status code mapping <status_codes>`. For example, RAISE insufficient_privilege will respond with HTTP 401/403 as appropriate.

For even greater control of the HTTP status code, raise an exception of the PTxyz type. For instance to respond with HTTP 402, raise PT402:

RAISE sqlstate 'PT402' using
  message = 'Payment Required',
  detail = 'Quota exceeded',
  hint = 'Upgrade your plan';

Returns:

HTTP/1.1 402 Payment Required
Content-Type: application/json; charset=utf-8

{
  "message": "Payment Required",
  "details": "Quota exceeded",
  "hint": "Upgrade your plan",
  "code": "PT402"
}

Add HTTP Headers with RAISE

For full control over headers and status you can raise a PGRST SQLSTATE error. You can achieve this by adding the code, message, detail and hint in the PostgreSQL error message field as a JSON object. Here, the details and hint are optional. Similarly, the status and headers must be added to the SQL error detail field as a JSON object. For instance:

RAISE sqlstate 'PGRST' USING
    message = '{"code":"123","message":"Payment Required","details":"Quota exceeded","hint":"Upgrade your plan"}',
    detail = '{"status":402,"headers":{"X-Powered-By":"Nerd Rage"}}';

Returns:

HTTP/1.1 402 Payment Required
Content-Type: application/json; charset=utf-8
X-Powered-By: Nerd Rage

{
  "message": "Payment Required",
  "details": "Quota exceeded",
  "hint": "Upgrade your plan",
  "code": "123"
}

For non standard HTTP status, you can optionally add status_text to describe the status code. For status code 419 the detail field may look like this:

detail = '{"status":419,"status_text":"Page Expired","headers":{"X-Powered-By":"Nerd Rage"}}';

If PostgREST can't parse the JSON objects message and detail, it will throw a PGRST121 error. See :ref:`Errors from PostgREST<pgrst1**>`.