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 base_tab (
  data_number decimal
)
create table ref_tab (
  ref_number decimal
)
insert into base_tab (data_number)
values (0), (1), (4.5), (7), (9.2)
5 rows affected

insert into ref_tab (ref_number)
values (1), (3), (6), (9)
4 rows affected

with fromto as (
  select
    ref_number as num
    /*
      Identify the first and the last row
      in the data set to manage values below
      the lowest (in the reference data set)
      and above the highest
    */
    , lag(0, 1, 1)
      over(order by ref_number asc)
      as first_row
    , lead(0, 1, 1)
      over(order by ref_number asc)
      as last_row
    /*
      And add value ranges (before and after current ref_num)
      to match data_number as closest above
      or closest below
    */
    , lead(ref_number, 1, 999999999999999.0)
      over(order by ref_number asc)
      as next_num
    , lag(ref_number, 1, -999999999999999.0)
      over(order by ref_number asc)
      as prev_num
  from ref_tab
)

select
  b.*
  , case
      /*
        When the data_number is below the lowest
        in the reference data set, then use the lowest
        value from reference data set
      */
      when up_.first_row = 1
      then up_.num
      /*
        When the data_number is above the highest
        in the reference data set, then use the highest
        value from reference data set
      */
      when down_.last_row = 1
      then down_.num
      /*
        Assign the closest value from the reference
        data set with minimal difference.
        Lower value has higher priority
      */
      when b.data_number - down_.num
        <= up_.num - b.data_number
      then down_.num
      when up_.num - b.data_number
        > b.data_number - down_.num
      then up_.num
    end as ref_num
from base_tab as b
  /*Test if data_number is below current ref_num*/
  left join fromto as up_
    on
      b.data_number >= up_.prev_num
      and b.data_number < up_.num
  /*Test if data_number is above current ref_num*/
  left join fromto as down_
    on
      b.data_number >= down_.num
      and b.data_number < down_.next_num




data_number
ref_num


0
1

1
1

4.5
3

7
6

9.2
9