Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  male BOOLEAN DEFAULT FALSE,
  age INT DEFAULT NULL
  );
  
INSERT INTO users (user_id, male, age) VALUES (1, false, 5);
INSERT INTO users (user_id, male, age) VALUES (2, false, 89);
INSERT INTO users (user_id, male, age) VALUES (3, true, 18);
INSERT INTO users (user_id, male, age) VALUES (4, true, 76);

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

explain analyze select coalesce (
  (select user_id from users order by age limit 1),
  (select user_id from users where user_id=1),
  (select user_id from users order by user_id DESC limit 1)
  );

QUERY PLAN

Result  (cost=51.37..51.38 rows=1 width=4) (actual time=0.317..0.318 rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=43.00..43.00 rows=1 width=8) (actual time=0.311..0.312 rows=1 loops=1)

          ->  Sort  (cost=43.00..48.50 rows=2200 width=8) (actual time=0.182..0.182 rows=1 loops=1)

                Sort Key: users.age

                Sort Method: top-N heapsort  Memory: 25kB

                ->  Seq Scan on users  (cost=0.00..32.00 rows=2200 width=8) (actual time=0.011..0.013 rows=4 loops=1)

  InitPlan 2 (returns $1)

    ->  Index Only Scan using users_pkey on users users_1  (cost=0.15..8.17 rows=1 width=4) (never executed)

          Index Cond: (user_id = 1)

          Heap Fetches: 0

  InitPlan 3 (returns $2)

    ->  Limit  (cost=0.15..0.19 rows=1 width=4) (never executed)

          ->  Index Only Scan Backward using users_pkey on users users_2  (cost=0.15..81.16 rows=2200 width=4) (never executed)

                Heap Fetches: 0

Planning Time: 0.750 ms

Execution Time: 0.400 ms

EXPLAIN

explain analyze
(select user_id from users order by age limit 1)
UNION ALL
(select user_id from users where user_id=1)
UNION ALL
(select user_id from users order by user_id DESC limit 1)
LIMIT 1

QUERY PLAN

Limit  (cost=43.00..45.80 rows=1 width=4) (actual time=0.025..0.026 rows=1 loops=1)

  ->  Append  (cost=43.00..51.41 rows=3 width=4) (actual time=0.024..0.025 rows=1 loops=1)

        ->  Subquery Scan on "*SELECT* 1"  (cost=43.00..43.01 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)

              ->  Limit  (cost=43.00..43.00 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=1)

                    ->  Sort  (cost=43.00..48.50 rows=2200 width=8) (actual time=0.022..0.023 rows=1 loops=1)

                          Sort Key: users.age

                          Sort Method: top-N heapsort  Memory: 25kB

                          ->  Seq Scan on users  (cost=0.00..32.00 rows=2200 width=8) (actual time=0.009..0.010 rows=4 loops=1)

        ->  Index Only Scan using users_pkey on users users_1  (cost=0.15..8.17 rows=1 width=4) (never executed)

              Index Cond: (user_id = 1)

              Heap Fetches: 0

        ->  Limit  (cost=0.15..0.19 rows=1 width=4) (never executed)

              ->  Index Only Scan Backward using users_pkey on users users_2  (cost=0.15..81.16 rows=2200 width=4) (never executed)

                    Heap Fetches: 0

Planning Time: 0.158 ms

Execution Time: 0.048 ms

EXPLAIN

-- does not work!
select coalesce (
  (select * from users order by age limit 1),
  (select * from users where user_id=1),
  (select * from users order by user_id DESC limit 1)
  );

ERROR:  subquery must return only one column
LINE 3:   (select * from users order by age limit 1),
          ^

-- but this does:
(select * from users order by age limit 1)
UNION ALL
(select * from users where user_id=1)
UNION ALL
(select * from users order by user_id DESC limit 1)
LIMIT 1

user_id
male
age

1
f
5

SELECT 1