ORDER BY: zeroes positions

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.

No source files or tests are named. Begin by tracing how the order query parameter is parsed and translated into SQL, then determine which proposed syntax is feasible and safe. Done means one agreed ordering design is implemented with coverage for the documented zero, non-positive, and negative-value cases.