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

53*

503

insufficient resources

54*

413

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

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

400

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 stale schema cache. Most of the time, these errors are solved by reloading the schema cache.

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.