What’s a good way of doing a > or < comparison that considers NULLs to be sorted first or last?

by

The question:

I’m on Postgres 13.5.

Records always have a number (type: integer) and optionally a single letter A-Z (type: varchar(1) – not char for framework reasons).

Records are typically sorted number ASC, letter ASC NULLS FIRST. The sequence may have gaps. Missing letters are represented as NULL.

For example, you might get this order: 1, 1A, 1B, 2, 10, 10A, 10C

Now, I want to do stuff like finding the two records “to the right” of a given record. So if the given record is number 2, I want to find 10 and 10A in the above example.

it would be convenient if I could query for a condition like (pseudo code): number > $given_number OR (number = $given_number AND letter > $given_letter NULLS FIRST)

This doesn’t work as written, of course. What are ways I could achieve this?

I’d prefer not to merge the columns or to add new columns.

Solutions I can think of:

  • Select a list of record IDs in SQL, use application logic outside the DB to find IDs of the next two records, then make a second query to find only those.
  • A longer condition that explicitly accounts for NULLs, something like WHERE number > $given_number OR (number = $given_number AND (($given_letter IS NULL AND letter IS NOT NULL) OR letter > $given_letter)) ORDER BY number ASC, letter ASC NULLS FIRST LIMIT 2
  • Coalescing NULLs, something like WHERE number > $given_number OR (number = $given_number AND letter > COALESCE($given_letter, '')) ORDER BY number ASC, letter ASC NULLS FIRST LIMIT 2

Any better ideas? Or any thoughts on these?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

If you can convert NULL values in letter to empty strings (''), everything just falls into place. The empty string sorts before any other value in default sort order.

That’s assuming you don’t have both empty strings and NULL values (which would be even more unfortunate).

UPDATE tbl SET letter = '' WHERE letter IS NULL;

ALTER TABLE tbl
  ALTER COLUMN letter SET NOT NULL
, ALTER COLUMN letter SET DEFAULT ''
, ADD PRIMARY KEY (number, letter)  -- optional
;

Your sort order:

number ASC, letter ASC NULLS FIRST

Becomes just:

number, letter

… finding the two records “to the right” of a given record.

SELECT *
FROM   tbl
WHERE  (number, letter) > (10, '')
ORDER  BY number, letter
LIMIT  2;

db<>fiddle here

Note the use of row-value comparison. See:

Back it up with a UNIQUE index or PK on (number, letter), and you are golden.

If you cannot sanitize the table definition, there is still a workaround:

SELECT *
FROM   tbl
WHERE  (number, COALESCE(letter, '')) > (10, COALESCE(NULL, ''))
ORDER  BY number, COALESCE(letter, '')
LIMIT  2;

Can even be supported with a matching multicolumn index. But rather keep it simple and convert your NULL values.

Aside:
For letters from A-Z the type "char" would be even a bit more efficient – decidedly distinct from char, which is never useful. (But your “framework reasons” probably stand against it.) See:

Method 2

For example, you might get this order: 1, 1A, 1B, 2, 10, 10A, 10C Now,
I want to do stuff like finding the two records “to the right” of a
given record. So if the given record is number 2, I want to find 10
and 10A in the above example.

The window function LEAD(column_name,1) OVER (ORDER BY ...) and LEAD(column_name,2) OVER (ORDER BY ...) might do what you want, if:

  • the fact that the values are one and two records away with the given sort order can be hard-wired in the query.
  • the condition to filter the given record can be added in an outer query, resulting in a potentially less efficient plan that your proposed solutions with a LIMIT clause.

The form of the query would be

SELECT * FROM (
   SELECT *,
   LEAD(number,1) OVER (ORDER BY number,letter nulls first) AS next_number_1,
   LEAD(letter,1) OVER (ORDER BY number,letter nulls first) AS next_letter_1,
   LEAD(number,2) OVER (ORDER BY number,letter nulls first) AS next_number_2,
   LEAD(letter,2) OVER (ORDER BY number,letter nulls first) AS next_letter_2
   FROM table
) s
WHERE number=$given_number AND letter is not distinct from $given_letter


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment