Nulls in timestamps

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 un order 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]