`coalesce_equal(...)` is a PostgreSQL function that is similar to `coalesce`, but requires that all non-null arguments be equal for it to return the identical value, when not equal or all arguments are null, it returns null.

coalesce_equal.sql

-- Similar to coalesce, but only gives back a non-null value if all the non-null
-- params are equivalent. 
create or replace function coalesce_equal(variadic args anycompatiblearray)
    returns anycompatible language sql immutable as $$ 
    with all_args(arg) as (select unnest(args)), dist_args(count) as (
        select count(distinct arg) filter (where arg is not null) from all_args
    )
    select arg from all_args, dist_args
    where arg is not null and count = 1
    limit 1
$$;

-- tests
select coalesce_equal('f', null, 'f', null, 'ff', 'f');    -- gives NULL
select coalesce_equal('f', null, 'f', null, 'f', 'f');     -- gives 'f'
select coalesce_equal(6, null, 7, null, 6, null);          -- gives NULL
select coalesce_equal(6, null, 6, null, 6, null);          -- gives 6
select coalesce_equal(null, null, null, null, null, null); -- gives NULL
select coalesce_equal('f', null, 6, null, false, 'f');     -- gives ERROR 😱

valueif.sql

-- Similar to nullif, but only gives back a the value if all the params are
-- equivalent to this value.
create or replace function valueif(variadic args anycompatiblearray)
    returns anycompatible language sql immutable as $$ 
    with args(arg) as (select distinct * from unnest(args))
    select any_value(arg) from args having count(*) = 1
$$;

-- tests
select valueif('f', 'f', 'f', 'f', 'f');            -- gives 'f'
select valueif('f', 'f', 'f', 'ff', 'ff', 'f');     -- gives NULL
select valueif('f', null, 'f', null, 'ff', 'f');    -- gives NULL
select valueif(6, null, 7, null, 6, null);          -- gives NULL
select valueif(6, 6, 6);                            -- gives 6
select valueif(6, null, 6, null, 6, null);          -- gives NULL
select valueif(null, null, null, null, null, null); -- gives NULL
select valueif('f', null, 6, null, false, 'f');     -- gives ERROR
select valueif(true, false);                        -- gives NULL
select valueif(0, false);                           -- gives ERROR
select valueif(false, false);                       -- gives False
select valueif(null);                               -- gives NULL
select valueif(true);                               -- gives True

Currently, this function returns an error when passed values that cannot be promoted to a common type. This is a limitation of PL/PGSQL and the pseudo types in PostgreSQL. Can it be overcome? I think: Yes. It is possible to create a variant that will accept any number of arguments which can be of any type, regardless of whether the types passed are compatible, or not.

... But this would require writing this function using the C programming language, using variadic "any" (in this case: "any" is a type and a keyword in SQL which is why it needs to be double quoted).

Do you want me to write this function in C and create a tutorial on how to compile it and dynamically load it in PostgreSQL?

Coalesce Equal as an Aggregate Function

The utility function, coalesce_equal, can also be used to define a very useful aggregate.

-- "s" stands for state, "v" stands for value, "sf" stands for state (transition) function
create or replace function sf_coalesce_equal(s anyelement, v anyelement)
  returns anyelement language sql immutable strict as $$ 
  select coalesce_equal(s, v) $$;

drop aggregate if exists coalesce_equal_agg(anyelement);
create aggregate coalesce_equal_agg(anyelement) (
  sfunc = sf_coalesce_equal,
  msfunc = sf_coalesce_equal,
  minvfunc = sf_coalesce_equal,
  mstype = anyelement,
  stype = anyelement
  );

Reflection

I am proposing to rename the aggregate version from coalesce_equal_agg to coalesceif.