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:

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.

Group 1 - Api Request

Related to the HTTP request elements.

Group 2 - Schema Cache

Related to a Schema Cache. Most of the time, these errors are solved by Schema Cache Reloading.

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.

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.

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.

Proxy-Status Header

For error cases, the standard Proxy-Status header is returned with the error code. The error code comes from either PostgREST, PostgreSQL or Custom errors. This is useful when doing HEAD requests where the HTTP status is not descriptive enough.

For example, doing a request on a table with high count (say 30_000_000), we get:

HEAD /table HTTP/1.1
Prefer: count=exact

HTTP/1.1 500 Internal Server Error
Proxy-Status: PostgREST; error=57014

The PostgreSQL error code 57014 (ref) reveals that the error is due to a short statement_timeout value.