Select the first entry if it is missing.
In PostgreSQL, I would like to select a row based on some criteria, but if no rows match the criteria, I would like to return the first row. The table actually contains an ordinal column, so the task should be simpler (the first row is ordinal 0). For example:
SELECT street, zip, city
FROM address
WHERE street LIKE 'Test%' OR ord = 0
LIMIT 1;
But in this case, there is no way to guarantee the order of the entries that match, and I have nothing to order. How can this be done with a single operator SELECT
?
source to share
I would like to select a row based on some criteria, but if no row matches the criteria, I would like to return the first row
Shorter (and correct)
You don't really need an offer WHERE
:
SELECT street, zip, city
FROM address
ORDER BY street !~~ 'Test%', ord
LIMIT 1;
!~~
is just a Postgres operator for NOT LIKE
. You can also use. Note that by inverting the logic ( NOT LIKE
instead of LIKE
), we can now use the default sort order ASC
and NULL sort last, which may be important. Read on.
It's shorter (but not necessarily faster). It's also subtly different (more reliable) than the currently accepted answer from @Gordon .
When sorting by expression,boolean
you should understand how it works:
The currently accepted answer uses ORDER BY <boolean expression> DESC
which sorts NULL first. In such a case, you usually add NULLS LAST
:
If street
defined NOT NULL
, it obviously doesn't matter, but it was not defined in the question. (Always provide a table definition.) The currently accepted answer avoids the problem by eliminating NULL values โโin the sentence WHERE
.
Some other DBMSs (MySQL, Oracle, ..) don't have the proper boolean
Postgres type, so we often see wrong advice from people coming from these products.
Your current request (as well as the currently accepted answer) requires a suggestion WHERE
- or at least NULLS LAST
. ORDER BY
No other expression is required.
More importantly , if multiple lines are matched street
(which is to be expected), the returned string will be arbitrary and may change between calls - usually an unwanted effect. This query selects the row with the smallest one ord
to break ties and gives a stable result.
This form is also more flexible as it does not rely on the existence of the string c ord = 0
. Instead, the row with the smallest ord
is fetched anyway.
Faster with index
(Still correct.) For large tables, the following index would significantly improve the performance of this query:
CREATE INDEX address_street_pattern_ops_idx ON address(street text_pattern_ops);
Detailed explanation:
Depending on undefined information, he might pay to add more columns to the index.
Fastest query using this index:
(
SELECT street, zip, city
FROM address
WHERE street LIKE 'Test%'
ORDER BY ord -- or something else?
-- LIMIT 1 -- you *could* add LIMIT 1 in each leg
)
UNION ALL
(
SELECT street, zip, city
FROM address
ORDER BY ord
-- LIMIT 1 -- .. but that not improving anything in *this* case
)
LIMIT 1
BTW, that's one statement.
This is more verbose, but allows for a simpler query plan. The second is SELECT
UNION ALL
never executed if the first SELECT
produces enough rows (in our case: 1). If you check with help EXPLAIN ANALYZE
, you will see (never executed)
in the query plan.
More details:
Assessment UNION ALL
In response to Gordon's comment. In the documentation:
Multiple operators are evaluated
UNION
in the same expressionSELECT
from left to right , unless otherwise indicated in parentheses.
The bold accent is mine.
And LIMIT
lets you stop Postgres publishing as soon as enough rows are found. This is why you see (never executed)
the output EXPLAIN ANALYZE
.
If you add external ORDER BY
before final LIMIT
, this optimization is not possible. Then you need to collect all the rows to see which ones can be sorted first.
source to share
You are on the right track. Just add order by
:
SELECT street, zip, city
FROM address
WHERE street LIKE 'Test%' OR ord = 0
ORDER BY (CASE WHEN street LIKE 'Test%' THEN 1 ELSE 0 END) DESC
LIMIT 1;
Or, alternately:
ORDER BY ord DESC
Either will put the line ord = 0
last.
EDIT:
Erwin raises a good point that in terms of using the index, OR
the proposal is WHERE
not the best approach. I would change my answer:
SELECT *
FROM ((SELECT street, zip, city
FROM address
WHERE street LIKE 'Test%'
LIMIT 1
)
UNION ALL
(SELECT street, zip, city
FROM address
WHERE ord = 0
LIMIT 1
)
) t
ORDER BY (CASE WHEN street LIKE 'Test%' THEN 1 ELSE 0 END) DESC
LIMIT 1;
This allows the request to use two indexes ( street
and ord
). Note that this is only valid because the template LIKE
does not start with a template. If the pattern LIKE
starts with a wildcard, then this query form will still perform a full scan of the table.
source to share
How about something like this ... (I'm not familiar with PostgreSQL, so the syntax might be off a bit)
SELECT street, zip, city, 1 as SortOrder
FROM address
WHERE street LIKE 'Test%'
--
union all
--
SELECT street, zip, city, 2 as SortOrder
FROM address
WHERE ord = 0
ORDER BY SortOrder
LIMIT 1;
source to share