Prefer Header

PostgREST honors the Prefer HTTP header specified on RFC 7240. It allows clients to specify required and optional behaviors for their requests.

The following preferences are supported.

Prefer: handling. See Strict or Lenient Handling.
Prefer: timezone. See Timezone.
Prefer: return. See Return Representation.
Prefer: count. See Counting.
Prefer: resolution. See Upsert.
Prefer: missing. See Bulk Insert with Default Values.
Prefer: max-affected, See Max Affected.
Prefer: tx. See Transaction End Preference.
Prefer: params. See Single JSON object as Function Parameter.

Strict or Lenient Handling

The server ignores unrecognized or unfulfillable preferences by default. You can control this behavior with the handling preference. It can take two values: lenient (the default) or strict.

handling=strict will throw an error if you specify invalid preferences. For instance:

curl -i "http://localhost:3000/projects" \
  -H "Prefer: handling=strict, foo, bar"

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

{
    "code": "PGRST122",
    "message": "Invalid preferences given with handling=strict",
    "details": "Invalid preferences: foo, bar",
    "hint": null
}

handling=lenient ignores invalid preferences.

curl -i "http://localhost:3000/projects" \
  -H "Prefer: handling=lenient, foo, bar"

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

Timezone

The timezone preference allows you to change the PostgreSQL timezone. It accepts all time zones in pg_timezone_names.

curl -i "http://localhost:3000/timestamps" \
  -H "Prefer: timezone=America/Los_Angeles"

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Preference-Applied: timezone=America/Los_Angeles

[
  {"t":"2023-10-18T05:37:59.611-07:00"},
  {"t":"2023-10-18T07:37:59.611-07:00"},
  {"t":"2023-10-18T09:37:59.611-07:00"}
]

For an invalid time zone, PostgREST returns values with the default time zone (configured on postgresql.conf or as a setting on the authenticator).

curl -i "http://localhost:3000/timestamps" \
  -H "Prefer: timezone=Jupiter/Red_Spot"

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

[
  {"t":"2023-10-18T12:37:59.611+00:00"},
  {"t":"2023-10-18T14:37:59.611+00:00"},
  {"t":"2023-10-18T16:37:59.611+00:00"}
]

Note that there’s no Preference-Applied in the response.

However, with handling=strict, an invalid time zone preference will throw an error.

curl -i "http://localhost:3000/timestamps" \
  -H "Prefer: handling=strict, timezone=Jupiter/Red_Spot"

HTTP/1.1 400 Bad Request

Return Representation

The return preference can be used to obtain information about affected resource when it’s inserted, updated or deleted. This helps avoid a subsequent GET request.

Minimal

With Prefer: return=minimal, no response body will be returned. This is the default mode for all write requests.

Headers Only

If the table has a primary key, the response can contain a Location header describing where to find the new object by including the header Prefer: return=headers-only in the request. Make sure that the table is not write-only, otherwise constructing the Location header will cause a permissions error.

curl -i "http://localhost:3000/projects" -X POST \
  -H "Content-Type: application/json" \
  -H "Prefer: return=headers-only" \
  -d '{"id":33, "name": "x"}'

HTTP/1.1 201 Created
Location: /projects?id=eq.34
Preference-Applied: return=headers-only

Full

On the other end of the spectrum you can get the full created object back in the response to your request by including the header Prefer: return=representation. That way you won’t have to make another HTTP call to discover properties that may have been filled in on the server side. You can also apply the standard Vertical Filtering to these results.

curl -i "http://localhost:3000/projects" -X POST \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"id":33, "name": "x"}'

HTTP/1.1 201 Created
Preference-Applied: return=representation

[
    {
        "id": 33,
        "name": "x"
    }
]

Transaction End Preference

The tx preference can be set to specify if the transaction will end in a COMMIT or ROLLBACK. This preference is not enabled by default but can be activated with db-tx-end.

curl -i "http://localhost:3000/projects" -X POST \
  -H "Content-Type: application/json" \
  -H "Prefer: tx=rollback, return=representation" \
  -d '{"name": "Project X"}'

HTTP/1.1 200 OK
Preference-Applied: tx=rollback, return=representation

{"id": 35, "name": "Project X"}

Max Affected

You can set a limit to the amount of resources affected in a request by sending max-affected preference. This feature works in combination with handling=strict preference. max-affected would be ignored with lenient handling. The “affected resources” are the number of rows returned by DELETE and PATCH requests. This is also supported through RPC calls.

To illustrate the use of this preference, consider the following scenario where the items table contains 14 rows.

curl -i "http://localhost:3000/items?id=lt.15 -X DELETE \
  -H "Content-Type: application/json" \
  -H "Prefer: handling=strict, max-affected=10"

HTTP/1.1 400 Bad Request

{
    "code": "PGRST124",
    "message": "Query result exceeds max-affected preference constraint",
    "details": "The query affects 14 rows",
    "hint": null
}

Single JSON object as Function Parameter

Warning

Using this preference is deprecated in favor of Functions with an array of JSON objects.

Prefer: params=single-object allows sending the JSON request body as the single argument of a function.

CREATE FUNCTION mult_them(param json) RETURNS int AS $$
  SELECT (param->>'x')::int * (param->>'y')::int
$$ LANGUAGE SQL;

curl "http://localhost:3000/rpc/mult_them" \
  -X POST -H "Content-Type: application/json" \
  -H "Prefer: params=single-object" \
  -d '{ "x": 4, "y": 2 }'