2

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?

2
  • 1
    At least in SQL Server, coalescing null's to something is going to be your best bet as far as simplicity and performance. Adding OR clauses usually slows down query performance, especially when it's the primary where clause. Apr 13, 2022 at 12:10
  • 1
    The actual, precise table definition as CREATE TABLE statement including all necessary constraints would beat any lengthy prose about it. And a fiddle to go with it, ideally. Apr 13, 2022 at 18:38

2 Answers 2

4

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:

1
  • 1
    Equally glorious as the previous answer you graced me with. Row-value comparisons seem like exactly what I was after. Thank you yet again, Erwin!
    – Henrik N
    Apr 13, 2022 at 21:45
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.