CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
Jens Mühlenhoff
Jens Mühlenhoff

Reputation: 14873

Is the order of a view guaranteed?

PostgreSQL allows ORDER BY in views, so for example I can write a view like this:

CREATE VIEW people_overview AS
SELECT
    id
  , name1
  , name2
FROM person
ORDER BY
    name2
  , name1

Let's say I have an application where I now use this view like this:

SELECT * FROM people_overview

The app then reads all the data and displays it to the user in some way, like with a grid.

Is it guaranteed that in this situation the ordering specified in the view is maintained when returing the rows to the application?

Or would I be better off to code the ORDER BY into the application?

Upvotes: 4

Views: 108

Answers (1)

Caius Jard
Caius Jard

Reputation: 74625

As per the comments; define the view without an ORDER BY (unless some subquery needs it for TOP N ROWS type purposes) and let the ultimate user of the view determine the sorting order they want (as it's then guaranteed to be what they want and there is less risk that the data will be sorted twice, once needlessly - the optimizer should realise that the in-view ordering is redundant if the select * from view order by x is applied, but there's not much point in taking the risk/putting the extra code clutter in).

I'd possibly also extend this philosophy to things like data conversion/formatting - leave the data in as-stored form so it remains useful for as long as possible, and lets the calling application decide on formatting (i.e. don't format all your dates to a yyyyMMdd string in your view, if the calling app will then have to parse it again to do some math on it etc)

Upvotes: 3

Related Questions