Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE measurement(id int, measurement numeric);
INSERT INTO measurement (id, measurement) VALUES
  (1, 0.24)
, (2, 0.5)
, (3, 0.14)
, (4, 0.68);

CREATE TABLE lookup (id int, nominal_value numeric UNIQUE);  -- automatically indexed
INSERT INTO lookup(id, nominal_value) VALUES
  (1, 0.1)
, (2, 0.2)
, (3, 0.3)
, (4, 0.4)
, (5, 0.5)
, (6, 0.6)
, (7, 0.7)
, (8, 0.8)
, (9, 0.9);

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 9


-- LATERAL
SELECT m.id, m.measurement, l.nominal_value
FROM   measurement m
JOIN   LATERAL (
   (
   SELECT nominal_value - m.measurement AS diff, nominal_value
   FROM   lookup
   WHERE  nominal_value >= m.measurement
   ORDER  BY nominal_value
   LIMIT  1
   )
   UNION  ALL
   (
   SELECT m.measurement - nominal_value, nominal_value
   FROM   lookup
   WHERE  nominal_value <= m.measurement
   ORDER  by nominal_value DESC
   LIMIT  1
   )
   ORDER  BY 1
   LIMIT  1
   ) l ON true;


id
measurement
nominal_value


1
0.24
0.2

2
0.5
0.5

3
0.14
0.1

4
0.68
0.7



SELECT 4


-- Correlated subqueries in subquery
SELECT id, measurement
     , CASE WHEN hi - measurement > measurement - lo
            THEN lo
            ELSE hi
       END AS nominal_value
FROM  (
   SELECT id, measurement
        , ( SELECT nominal_value
            FROM   lookup
            WHERE  nominal_value >= m.measurement
            ORDER  BY nominal_value
            LIMIT  1) AS hi
         , COALESCE((
            SELECT nominal_value
            FROM   lookup
            WHERE  nominal_value <= m.measurement
            ORDER  by nominal_value DESC
            LIMIT  1), 0) AS lo   -- cover possible NULL values
   FROM   measurement m
   ) sub;


id
measurement
nominal_value


1
0.24
0.2

2
0.5
0.5

3
0.14
0.1

4
0.68
0.7



SELECT 4


-- Correlated subqueries in CTE
WITH cte AS (
   SELECT id, measurement
        , ( SELECT nominal_value
            FROM   lookup
            WHERE  nominal_value >= m.measurement
            ORDER  BY nominal_value
            LIMIT  1) AS hi
        , COALESCE((
            SELECT nominal_value
            FROM   lookup
            WHERE  nominal_value <= m.measurement
            ORDER  by nominal_value DESC
            LIMIT  1), 0) AS lo   -- cover possible NULL values
   FROM   measurement m
   )
SELECT id, measurement
     , CASE WHEN hi - measurement > measurement - lo
            THEN lo
            ELSE hi
       END AS nominal_value
FROM   cte;


id
measurement
nominal_value


1
0.24
0.2

2
0.5
0.5

3
0.14
0.1

4
0.68
0.7



SELECT 4


-- Nested correlated subqueries
SELECT id, measurement
     , (SELECT nominal_value FROM (
         (
         SELECT nominal_value - m.measurement, nominal_value
         FROM   lookup
         WHERE  nominal_value >= m.measurement
         ORDER  BY nominal_value
         LIMIT  1
         )
         UNION  ALL
         (
         SELECT m.measurement - nominal_value, nominal_value
         FROM   lookup
         WHERE  nominal_value <= m.measurement
         ORDER  by nominal_value DESC
         LIMIT  1
         )
         ORDER  BY 1
         LIMIT  1
         ) sub
         ) AS nominal_value
FROM   measurement m;


id
measurement
nominal_value


1
0.24
0.2

2
0.5
0.5

3
0.14
0.1

4
0.68
0.7



SELECT 4