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 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 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 Schema Cache due to the PostgreSQL service not running.
PGRST003	504	The request timed out waiting for a pool connection to be available. See 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 Horizontal Filtering, Operators and Ordering.
PGRST101	405	For 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 Limits and Pagination.
PGRST105	405	An invalid PUT request was done
PGRST106	406	The schema specified when switching schemas is not present in the 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 Embedded Filters.
PGRST109	400	Restricting a Deletion or an Update using limits must include the ordering of a unique column. See 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 Limited Update/Delete.
PGRST111	500	An invalid `response.headers` was set. See Response Headers.
PGRST112	500	The status code must be a positive integer. See Response Status Code.
PGRST114	400	For an UPSERT using PUT, when limits and offsets are used.
PGRST115	400	For an UPSERT using 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 Singular or 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` operators.
PGRST121	500	PostgREST can’t parse the JSON objects in RAISE `PGRST` error. See raise headers.
PGRST122	400	Invalid preferences found in `Prefer` header with `Prefer: handling=strict`. See Strict or Lenient Handling.

Group 2 - Schema Cache

Related to a Schema Cache. Most of the time, these errors are solved by Schema Cache 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 Foreign Key Joins on Multiple Foreign Key Relationships.
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 column specified in the `columns` query parameter is not found.

Group 3 - JWT

Related to the authentication process using JWT. You can follow the Tutorial 1 - The Golden Key for an example on how to implement authentication and the Authentication page for more information on this process.

Code	HTTP status	Description
PGRST300	500	A 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 authentication when the anonymous role is disabled by not setting it in 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 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 error to status code mapping. 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 Errors from PostgREST.