Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Appearance settings

ORDER BY: zeroes positions #4366

Copy link
Copy link
@JFGHT

Description

@JFGHT
Issue body actions

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    ideaNeeds of discussion to become an enhancement, not ready for implementationNeeds of discussion to become an enhancement, not ready for implementation

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions

      Morty Proxy This is a proxified and sanitized view of the page, visit original site.