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.

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:

GET /projects HTTP/1.1
Prefer: handling=strict, foo, bar

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.

GET /projects HTTP/1.1
Prefer: handling=lenient, foo, bar

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 timezones in pg_timezone_names.

GET /timestamps HTTP/1.1
Prefer: timezone=America/Los_Angeles

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 timezone, PostgREST returns values with the default timezone (configured on postgresql.conf or as a setting on the authenticator).

GET /timestamps HTTP/1.1
Prefer: timezone=Jupiter/Red_Spot

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 timezone preference will throw an error.

GET /timestamps HTTP/1.1
Prefer: handling=strict, timezone=Jupiter/Red_Spot

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.

POST /projects HTTP/1.1
Prefer: return=headers-only

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

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.

POST /projects HTTP/1.1
Content-Type: application/json; charset=utf-8
Prefer: return=representation

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

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"
    }
]