Search code examples
sqlpostgresqlsql-limit

Select first record if none match

In PostgreSQL, 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. The table actually contains an ordinal column, so the task should be easier (the first row is the one with 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 records that match, and I have nothing to order them by. What would be the way to do this using a single SELECT statement?

Solution

  • 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)

    But slow.
    Logically, you don't actually need a WHERE clause:

    SELECT street, zip, city
FROM   address
ORDER  BY (street LIKE 'Test%') DESC NULLS LAST, ord
-- ORDER  BY street NOT LIKE 'Test%', ord  -- shorter, maybe less clear
LIMIT  1;

    ORDER BY expr DESC sorts null values first. If null values are possible, you'll want to add NULLS LAST. Or invert the logic: NOT LIKE instead of LIKE, and use default sort order ASC where null values sort last. See:

    If street is defined NOT NULL this is irrelevant, but that has not been defined. (Exact table definition would clarify.)

    More importantly, if multiple rows have a matching street (which is to be expected), the returned row would be arbitrary and could change between calls - typically undesirable. Add an (arbitrary unless you know better) tiebreaker to make it deterministic. This query picks the row with the smallest ord to be deterministic. (Below query uses the street name to break ties.)

    This form is also flexible in that it does not rely on the existence of a row with ord = 0. Instead, the row with the smallest ord is picked.

    Faster with index (and still correct)

    For a big table and only left-anchored patterns (like you show) this COLLATE "C" index radically improves performance:

    CREATE INDEX address_street_collate_c_idx ON address (street COLLATE "C");

    See:

    Plus, I assume an index on address(ord). (The PK?)

    This query makes use of the index(es):

    ( -- uses address_street_collate_c_idx
SELECT street, zip, city
FROM   address
WHERE  street LIKE 'Test%'
ORDER  BY street COLLATE "C"
LIMIT  1  -- logically redundant, but helps in this query
)

UNION ALL

( -- uses index on (ord)
SELECT street, zip, city
FROM   address
ORDER  BY ord
LIMIT  1  -- logically redundant, but helps in this query
)
LIMIT  1

    fiddle

    The 2nd query uses the street name as tiebreaker. It's cheapest to pick the fist hit from the index.

    The second SELECT of the UNION ALL is never executed if the first SELECT produces enough rows (in our case: 1). If you test with EXPLAIN ANALYZE, you'll see (never executed) in this case.

    Related:

    Evaluation of UNION ALL

    In reply to Gordon's comment. The manual:

    Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.

    Bold emphasis mine.
    And LIMIT makes Postgres stop evaluating as soon as enough rows are found. That's why you see (never executed) in the output of EXPLAIN ANALYZE.

    If you add an outer ORDER BY before the final LIMIT this optimization is not possible. Then all rows have to be collected to see which might sort first.

    This query with cheap index scans and LIMIT 1 will never break.
    But exact guarantees are under investigation. See: