Thread: Nulls in timestamps
Where does PostgreSQL rank nulls when sorting a column of timestamps, is this behaviour deterministic, and can I rely on it not changing in the future? Apologies if this shows up as a repost, I've had gateway problems at this end. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
markMLl.pgsql-general@telemetry.co.uk writes:
> Where does PostgreSQL rank nulls when sorting a column of timestamps, is this
> behaviour deterministic, and can I rely on it not changing in the future?
Nulls sort high (in any datatype, not only timestamps). It's possible
that we'd offer an option to make them sort low in the future, but I
can't imagine that we'd change the default behavior.
regression=# (select 1 as x union select null) order by x;
x
---
1
(2 rows)
regression=# (select 1 as x union select null) order by x desc;
x
---
1
(2 rows)
regression=#
regards, tom lane
Many thanks Tom. Inconvenient from the point of view of the application but still useful information. The situation is that I've got a query with numerous subselects, each of which has to return exactly one row so I was doing a union with a nulled record then selecting the most recent: obviously I need to see bona-fide data if it's there. What I'll do is teach the app or the users to ignore a specified early data, e.g. 1970-01-01. Again, many thanks (and boy, is it good to have that gateway working properly :-) Tom Lane wrote: > > markMLl.pgsql-general@telemetry.co.uk writes: > > Where does PostgreSQL rank nulls when sorting a column of timestamps, is this > > behaviour deterministic, and can I rely on it not changing in the future? > > Nulls sort high (in any datatype, not only timestamps). It's possible > that we'd offer an option to make them sort low in the future, but I > can't imagine that we'd change the default behavior. > > regression=# (select 1 as x union select null) order by x; > x > --- > 1 > > (2 rows) > > regression=# (select 1 as x union select null) order by x desc; > x > --- > > 1 > (2 rows) > > regression=# > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On Wed, 2005-07-13 at 12:41, Tom Lane wrote: > markMLl.pgsql-general@telemetry.co.uk writes: > > Where does PostgreSQL rank nulls when sorting a column of timestamps, is this > > behaviour deterministic, and can I rely on it not changing in the future? > > Nulls sort high (in any datatype, not only timestamps). It's possible > that we'd offer an option to make them sort low in the future, but I > can't imagine that we'd change the default behavior. Isn't this behaviour implementation dependent, i.e. other database could do it anyway they wanted? Just thinking of portability issues one might have if one were to rely on null sort order in an application.
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Wed, 2005-07-13 at 12:41, Tom Lane wrote:
>> Nulls sort high (in any datatype, not only timestamps). It's possible
>> that we'd offer an option to make them sort low in the future, but I
>> can't imagine that we'd change the default behavior.
> Isn't this behaviour implementation dependent, i.e. other database could
> do it anyway they wanted? Just thinking of portability issues one might
> have if one were to rely on null sort order in an application.
According to the SQL spec it's "implementation defined", which means
different DBs could do it differently but they have to tell you what
they will do. "Implementation dependent" effectively means "the
behavior is not specified at all".
For instance, in SQL92 13.1 <declare cursor> general rule 3(b):
Whether a sort key value that is null is considered greater
or less than a non-null value is implementation-defined, but
all sort key values that are null shall either be considered
greater than all non-null values or be considered less than
all non-null values.
regards, tom lane
Scott Marlowe wrote: > > On Wed, 2005-07-13 at 12:41, Tom Lane wrote: > > markMLl.pgsql-general@telemetry.co.uk writes: > > > Where does PostgreSQL rank nulls when sorting a column of timestamps, > > > is this behaviour deterministic, and can I rely on it not changing in > > > the future? > > > > Nulls sort high (in any datatype, not only timestamps). It's possible > > that we'd offer an option to make them sort low in the future, but I > > can't imagine that we'd change the default behavior. > > Isn't this behaviour implementation dependent, i.e. other database could > do it anyway they wanted? Just thinking of portability issues one might > have if one were to rely on null sort order in an application. Yes, I believe it is implementation defined, and might not be deterministic. However whilst the apps are currently written in Delphi on Win-32 (I'm hoping to be able to port them to Lazarus on SPARC) much of the "intelligence" is scripted, this includes almost all the SQL. Needless to say, the scripts are stored in a table: apart from ODBC setup etc. (which I'm hoping to eliminate) there's only a single .ini file per instance of the application suite, with several instances running per machine. Works nicely :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
Tom Lane wrote: > > According to the SQL spec it's "implementation defined", which means > different DBs could do it differently but they have to tell you what > they will do. "Implementation dependent" effectively means "the > behavior is not specified at all". One problem is that even if the server is supposed to tell you, the means it uses to do so can vary widely: paper manual, release notes, variable accessible by SHOW, value accessible by a special query (like the old-style PostgreSQL EXPLAIN output) value accessible by a standard query (provided you know the syntax in advance)... -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On Wed, Jul 13, 2005 at 18:15:12 +0000,
markMLl.pgsql-general@telemetry.co.uk wrote:
> Many thanks Tom. Inconvenient from the point of view of the application but
> still useful information.
>
> The situation is that I've got a query with numerous subselects, each of which
> has to return exactly one row so I was doing a union with a nulled record then
> selecting the most recent: obviously I need to see bona-fide data if it's there.
you can order by datecol is null, datecol desc to get the most recent
non null date. For example:
area=> select day from (select 'today'::date as day union select 'tomorrow'::date as day union select null as day) as
unorder by day is null, day desc;
day
------------
2005-07-16
2005-07-15
(3 rows)
Thanks, that looks useful. I'll investigate once I've got the servers upgraded and replication running. Bruno Wolff III wrote: > > On Wed, Jul 13, 2005 at 18:15:12 +0000, > markMLl.pgsql-general@telemetry.co.uk wrote: > > Many thanks Tom. Inconvenient from the point of view of the application > > but still useful information. > > > > The situation is that I've got a query with numerous subselects, each of > > which has to return exactly one row so I was doing a union with a nulled > > record then selecting the most recent: obviously I need to see bona-fide > > data if it's there. > > you can order by datecol is null, datecol desc to get the most recent > non null date. For example: > area=> select day from (select 'today'::date as day union select > 'tomorrow'::date as day union select null as day) as un order by day is null, > day desc; > day > ------------ > 2005-07-16 > 2005-07-15 > > (3 rows) > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]