10.0.0
Features
XML/SOAP support for RPC
RPC now understands the text/xml
media type, allowing SQL functions to send XML output(Accept: text/xml
) and receive XML input(Content-Type: text/xml
). This makes SOAP endpoints possible, check the Create a SOAP endpoint how-to and the Scalar Function Response Format reference for more details.
GeoJSON support
GeoJSON is supported across the board(reads, writes, RPC) with the Accept: application/geo+json
header, this depends on PostGIS from the versions 3.0.0 and up. The working with PostGIS section has an example to get you started.
Execution Plan
The execution plan of a request is now obtainable with the Accept: application/vnd.pgrst.plan
header. The result can be in text
or json
formats and is compatible with EXPLAIN vizualizers like explain.depesz.com or explain.dalibo.com.
Resource Embedding
A one-to-one relationship is now detected when a foreign key is unique.
Using Computed Relationships, you can add custom relationships or override automatically detected ones. This makes Resource Embedding possible on Foreign Data Wrappers and complex SQL views.
Horizontal/Vertical Filtering
Accessing fields of a Composite type or elements of an Array type is now possible with the arrow operators(
->
,->>
) in the same way you would access a JSON type fields.Pattern Matching operators for POSIX regular expressions are now available:
match
andimatch
, equivalent in PostgreSQL to~
and~*
respectively.
Insertions/Updates
limit
can now affect the number of updated/deleted rows. See Limited Update/Delete.
OpenAPI
You can now activate the “Authorize” button in SwaggerUI by enabling the openapi-security-active configuration. Add your JWT token prepending Bearer
to it and you’ll be able to request protected resources.
Administration
Two health check endpoints are now exposed in a secondary port.
Logging now shows the database user.
It is now possible to execute PostgREST without specifying any configuration variable. The three that were mandatory on the previous versions, are no longer so.
If db-uri is not set, PostgREST will use the libpq environment variables for the database connection.
If db-schemas is not set, it will use the database
public
schema.If db-anon-role is not set, it will not allow anonymous requests.
Error messages
To increase consistency, all the errors messages are now normalized. The
hint
,details
,code
andmessage
fields will always be present in the body, each one defaulting to anull
value. In the same way, the errors that were raised withSQLSTATE
now include themessage
andcode
in the body.To further clarify the source of an error, we now add a
PGRST
prefix to the error code of all the errors that are PostgREST-specific and don’t come from the database. These errors have unique codes that identify them and are documented in the PostgREST Error Codes section.
Documentation improvements
Added a Working with PostgreSQL data types how-to, which contains explanations and examples on how to work with different PostgreSQL data types such as timestamps, ranges or PostGIS types, among others.
Added in-database and environment variable settings for each configuration variable.
Added the File Descriptors subsection.
Added a reference page for Errors.
Moved the Errors and the HTTP Status Codes sections to the errors reference page.
Moved the Casting type to custom JSON how-to to the Casting a Range to a JSON Object subsection.
Removed direct links for PostgREST versions older than 8.0 from the versions menu.
Removed the Embedding table from another schema how-to.
Restructured the Resource Embedding section:
Added a One-to-many relationships and Many-to-one relationships subsections.
Renamed the Embedding through join tables subsection to Many-to-many relationships.
Split up the Insertions/Updates section into Insert and Update.
Breaking changes
Many-to-many relationships now require that foreign key columns be part of the join table composite key
This was needed to reduce Foreign Key Joins on Multiple Foreign Key Relationships errors in complex schemas(#2070).
For migrating to this version, the less invasive method is to use Computed Relationships to replace the previous many-to-many relationships.
Otherwise you can change your join table primary key. For example with
alter table permission_user drop constraint permission_user_pkey, add primary key (id, user_id, permission_id);
Views now are not detected when embedding using Foreign Key Joins on Multiple Foreign Key Relationships.
This embedding form was easily made ambiguous whenever a new view was added(#2277).
For migrating to this version, you can use Computed Relationships to replace the previous view relationships.
Foreign Key Joins on Multiple Foreign Key Relationships works as usual on views.
limit/offset
now limits the affected rows onUPDATE
/DELETE
Previously,
limit
/offset
only limited the returned rows but not the actual updated rows(#2156)
max-rows
is no longer applied onPOST
,PATCH
,PUT
andDELETE
returned rowsThis was misleading because the affected rows were not really affected by
max-rows
, only the returned rows were limited(#2155)
Return
204 No Content
withoutContent-Type
for RPCs returningVOID
Previously, those RPCs would return
null
as a body withContent-Type: application/json
(#2001).
Using
Prefer: return=representation
no longer returns aLocation
headerThis reduces unnecessary computing for all insertions (#2312)
Bug fixes
Return
204 No Content
withoutContent-Type
forPUT
(#2058)Clarify error for failed schema cache load. (#2107)
From
Database connection lost. Retrying the connection
toCould not query the database for the schema cache. Retrying.
Fix silently ignoring filter on a non-existent embedded resource (#1771)
Remove functions, which are not callable due to unnamed arguments, from schema cache and OpenAPI output. (#2152)
Fix accessing JSON array fields with
->
and->>
in?select=
and?order=
. (#2145)Ignore
max-rows
onPOST
,PATCH
,PUT
andDELETE
(#2155)Fix inferring a foreign key column as a primary key column on views (#2254)
Restrict generated many-to-many relationships (#2070)
Only adds many-to-many relationships when a table has foreign keys to two other tables and these foreign key columns are part of the table’s primary key columns.
Allow casting to types with underscores and numbers (e.g.
select=oid_array::_int4
) (#2278)Prevent views from breaking one-to-many/many-to-one embeds when using column or foreign key as target (#2277, #2238, #1643)
When using a column or foreign key as target for embedding (
/tbl?select=*,col-or-fk(*)
), only tables are now detected and views are not.You can still use a column or an inferred foreign key on a view to embed a table (
/view?select=*,col-or-fk(*)
)
Increase the
db-pool-timeout
to 1 hour to prevent frequent high connection latency (#2317)The search path now correctly identifies schemas with uppercase and special characters in their names (regression) (#2341)
“404 Not Found” on nested routes and “405 Method Not Allowed” errors no longer start an empty database transaction (#2364)
Fix inaccurate result count when an inner embed was selected after a normal embed in the query string (#2342)
OPTIONS
requests no longer start an empty database transaction (#2376)Allow using columns with dollar sign ($) without double quoting in filters and
select
(#2395)Fix loop crash error on startup in PostgreSQL 15 beta 3.
Log: "UNION types \"char\" and text cannot be matched."
(#2410)Fix race conditions managing database connection helper (#2397)
Allow
limit=0
in the request query to return an empty array (#2269)
Thanks
Big thanks from the PostgREST team to our sponsors!
Evans Fernandes
Jan Sommer
Tsingson Qin
Michel Pelletier
Jay Hannah
Robert Stolarz
Nicholas DiBiase
Christopher Reid
Nathan Bouscal
Daniel Rafaj
David Fenko
Remo Rechkemmer
Severin Ibarluzea
Tom Saleeba
Pawel Tyll
If you like to join them please consider supporting PostgREST development.