-
-
Notifications
You must be signed in to change notification settings - Fork 1.1k
Description
Summary
Support more advanced ordering options in PostgREST by allowing ordering expressions (not just simple column asc/desc). This would enable use cases such as pushing specific values (e.g., 0
or all non-positives) to the end of the result set while preserving natural ordering for the rest.
Problem
Currently, PostgREST only supports ordering by simple columns with ascending or descending direction, e.g.:
GET /items?order=col.asc
This does not allow common SQL patterns such as treating certain values as “special” and pushing them to the end. In raw SQL, this is often done with boolean expressions:
-- push only zeros last
ORDER BY (col = 0), col ASC;
-- push zeros and negatives last
ORDER BY (col <= 0), col ASC;
-- push only negatives last, keep zeros with positives
ORDER BY (col < 0), col ASC;
At present, the only way to achieve this in PostgREST is to resort to database views or stored procedures, which adds friction for what is essentially a presentation-layer ordering need.
Use Cases
- Treat
0
as a “default/empty” value that should appear after all positive values. - Group all non-positive values (
<= 0
) at the end of results, keeping positives first. - Push only negatives after positives and zeros.
- Implement custom ordering logic for UI lists or reports, without requiring schema changes.
Proposed Solution
Option 1 – Extended order syntax
Allow order
to accept boolean expressions, not only raw column names. For example:
GET /items?order=(col.eq.0),col.asc
GET /items?order=(col.lte.0),col.asc
GET /items?order=(col.lt.0),col.asc
This would translate to SQL:
ORDER BY (col = 0), col ASC
ORDER BY (col <= 0), col ASC
ORDER BY (col < 0), col ASC
Option 2 – Dedicated flags
Introduce optional flags to push special values last:
GET /items?order=col.asc.zeroslast
GET /items?order=col.asc.nonpositiveslast
GET /items?order=col.asc.negativeslast
Option 3 – Expression passthrough
Allow SQL expressions in the order parameter, e.g.:
GET /items?order=(col=0),col.asc
Rationale
- Keeps presentation logic close to queries: avoids unnecessary database views or functions.
- Covers a common real-world need: many datasets use sentinel/default values like
0
or negative numbers that should be deprioritized. - Consistent with SQL semantics: PostgREST already maps closely to PostgreSQL; supporting expression ordering aligns naturally with PostgreSQL’s
ORDER BY
.