Resource Representation

PostgREST uses proper HTTP content negotiation (RFC7231) to deliver a resource representation. That is to say the same API endpoint can respond in different formats like JSON or CSV depending on the request.

Response Format

Use the Accept request header to specify the acceptable format (or formats) for the response:

GET /people HTTP/1.1
Accept: application/json

For tables and views the current possibilities are:

  • */*

  • text/csv

  • application/json

  • application/openapi+json

  • application/geo+json

The server will default to JSON for API endpoints and OpenAPI on the root.

Singular or Plural

By default PostgREST returns all JSON results in an array, even when there is only one item. For example, requesting /items?id=eq.1 returns

[
  { "id": 1 }
]

This can be inconvenient for client code. To return the first result as an object unenclosed by an array, specify vnd.pgrst.object as part of the Accept header

GET /items?id=eq.1 HTTP/1.1
Accept: application/vnd.pgrst.object+json

This returns

{ "id": 1 }

with a Content-Type: application/vnd.pgrst.object+json.

When a singular response is requested but no entries are found, the server responds with an error message and 406 Not Acceptable status code rather than the usual empty array and 200 status:

{
  "message": "JSON object requested, multiple (or no) rows returned",
  "details": "Results contain 0 rows, application/vnd.pgrst.object+json requires 1 row",
  "hint": null,
  "code": "PGRST505"
}

Note

Many APIs distinguish plural and singular resources using a special nested URL convention e.g. /stories vs /stories/1. Why do we use /stories?id=eq.1? The answer is because a singular resource is (for us) a row determined by a primary key, and primary keys can be compound (meaning defined across more than one column). The more familiar nested urls consider only a degenerate case of simple and overwhelmingly numeric primary keys. These so-called artificial keys are often introduced automatically by Object Relational Mapping libraries.

Admittedly PostgREST could detect when there is an equality condition holding on all columns constituting the primary key and automatically convert to singular. However this could lead to a surprising change of format that breaks unwary client code just by filtering on an extra column. Instead we allow manually specifying singular vs plural to decouple that choice from the URL format.

Stripped Nulls

By default PostgREST returns all JSON null values. For example, requesting /projects?id=gt.10 returns

[
  { "id": 11, "name": "OSX",      "client_id": 1,    "another_col": "val" },
  { "id": 12, "name": "ProjectX", "client_id": null, "another_col": null },
  { "id": 13, "name": "Y",        "client_id": null, "another_col": null }
]

On large result sets, the unused keys with null values can waste bandwith unnecessarily. To remove them, specify nulls=stripped as a parameter of application/vnd.pgrst.array:

GET /projects?id=gt.10 HTTP/1.1
Accept: application/vnd.pgrst.array+json;nulls=stripped

This returns

[
  { "id": 11, "name": "OSX", "client_id": 1, "another_col": "val" },
  { "id": 12, "name": "ProjectX" },
  { "id": 13, "name": "Y"}
]

Scalar Function Response Format

In the special case of a Scalar functions there are three additional formats:

  • application/octet-stream

  • text/plain

  • text/xml

Example 1: If you want to return raw binary data from a bytea column, you must specify application/octet-stream as part of the Accept header and select a single column ?select=bin_data.

GET /items?select=bin_data&id=eq.1 HTTP/1.1
Accept: application/octet-stream

Example 2: You can request XML output when having a scalar function that returns a type of text/xml. You are not forced to use select for this case.

CREATE FUNCTION generate_xml_content(..) RETURNS xml ..

POST /rpc/generate_xml_content HTTP/1.1
Accept: text/xml

Example 3: If the stored procedure returns non-scalar values, you need to do a select in the same way as for GET binary output.

CREATE FUNCTION get_descriptions(..) RETURNS SETOF TABLE(id int, description text) ..

POST /rpc/get_descriptions?select=description HTTP/1.1
Accept: text/plain

Note

If more than one row would be returned the binary/plain-text/xml results will be concatenated with no delimiter.

Request Body

The server handles the following request body media types:

  • application/json

  • application/x-www-form-urlencoded

  • text/csv

For Tables and Views this works on POST, PATCH and PUT methods. For Stored Procedures, it works on POST methods.

For stored procedures there are three additional types:

  • application/octet-stream

  • text/plain

  • text/xml

See Functions with a single unnamed parameter.