Question 114292 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to write this SQL against PG?

Say, there is a table A, with columns a and b, both are int type with unique index.

I want to select from A where

  • if there a tuple whose a equal to 100, then return this tuple
  • otherwise, return a tuple whose b is smallest and less than 10000

This can be easily done with two SQL statements:

select * from A where a = 100

If first SQL returns nothing, then run:

select * from A where b < 10000 order by b limit 1

But I want to implement this logic in a single SQL statement. Is it possible?

Tags:

2

Answers


  1. 0 
    with a100orminb as
(
  select *, 1 as priority from A where a = 100
  union all
  select *, 2 as priority from A where b < 10000 order by b limit 1
)
select * from a100orminb order by priority limit 1;

    You’ll get unneeded column "priority", if you don’t want to list all columns in the SQL, and use *, but I suppose that it is not a problem.

    Login or Signup to reply.
  2. 0

    I don’t know why you want that to be one statement, but UNION ALL, sorting and a LIMIT should do the trick:

    (SELECT *, FALSE AS differs_from_100
FROM a WHERE a = 100
UNION ALL
SELECT *, TRUE AS differs_from_100
FROM a WHERE b < 10000)
ORDER BY differs_from_100, b
FETCH FIRST 1 ROWS ONLY;

    The disadvantage is that you have to execute the second statement even if the first returns a result, so running two statements might be preferable.

    A more complicated version that might perform better would be

    WITH has_100 AS (
   SELECT *
   FROM a WHERE a = 100
)
SELECT * FROM has_100
UNION ALL
SELECT *
FROM a WHERE b < 10000
WHERE NOT EXISTS (SELECT FROM has_100)
ORDER BY b
FETCH FIRST 1 ROWS ONLY;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search