All we need is an easy explanation of the problem, so here it is.
I’m on Postgres 13.5.
Records always have a
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
… finding the two records "to the right" of a given record.
SELECT * FROM tbl WHERE (number, letter) > (10, '') ORDER BY number, letter LIMIT 2;
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.
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:
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂