How do I use coalescence or variation to pull in more than 1 value?
This COALESCE
is a PostgreSQL question . As it stands I am using
COALESCE
to get the first value NOT NULL
in the list:
COALESCE(vw_header_to_node_13.subsetname,
vw_header_to_node_12.subsetname,
vw_header_to_node_11.subsetname,
vw_header_to_node_10.subsetname,
vw_header_to_node_9.subsetname,
vw_header_to_node_8.subsetname,
vw_header_to_node_7.subsetname,
vw_header_to_node_6.subsetname,
vw_header_to_node_5.subsetname,
vw_header_to_node_4.subsetname,
vw_header_to_node_3.subsetname,
vw_header_to_node_2.subsetname,
vw_header_to_node_1.subsetname,
vw_header_to_node.subsetname,
vw_header_to_node.setname)
AS prctr1
I was just informed that instead of grabbing only the first field NOT NULL
, I now need to grab the first field NOT NULL
and the next 2 fields after the first field NOT NULL
. For example, if vw_header_to_node_8.subsetname
it was the first field NOT NULL
, I would like to capture vw_header_to_node_8.subsetname
, vw_header_to_node_7.subsetname
and vw_header_to_node_6.subsetname
. I know this isn't how it usually works COALESCE
, but does anyone know of any options or ways to do this?
source to share
If all zeros are to be excluded from the list, this method should work:
- convert the list to an array,
- remove zeros from an array,
- select the first three elements of the array.
Example:
with test as (
select
null::text as v1,
'apple'::text as v2,
null::text as v3,
'banana'::text as v4,
'pear'::text as v5)
select a[1] val1, a[2] val2, a[3] val3
from (
select
array_remove(
array[v1, v2, v3, v4, v5], null) a
from test
) alias
val1 | val2 | val3
-------+--------+------
apple | banana | pear
If the second and third values can be null, we have to trim the first zeros from the array in step 2. Postgres does not have a corresponding function, but you can write one yourself.
create function array_ltrim_nulls(arr anyarray)
returns anyarray language plpgsql immutable
as $$
declare
i integer;
l integer = array_length(arr, 1);
begin
for i in 1..l loop
if arr[i] is not null then
return arr[i:l];
end if;
end loop;
return null;
end $$;
with test as (
select
null::text as v1,
'apple'::text as v2,
null::text as v3,
'banana'::text as v4,
'pear'::text as v5)
select a[1] val1, a[2] val2, a[3] val3
from (
select
array_ltrim_nulls(
array[v1, v2, v3, v4, v5]) a
from test
) alias
val1 | val2 | val3
-------+------+--------
apple | | banana
For ease of use, I have added the following function to my standard set of functions. Perhaps this function will be useful for you. The number of arguments to a function is variable. You only need to make sure that all arguments are of the same type.
create function array_coalesce (variadic arr anyarray)
returns anyarray language sql immutable
as $$
select array_remove(arr, null);
$$;
select array_coalesce(null::text, 'apple', null, 'banana', 'pear') arr;
arr
---------------------
{apple,banana,pear}
select array_coalesce(null::int, 1, null, 2, 3, 4, null, 5) arr;
arr
-------------
{1,2,3,4,5}
select (array_coalesce(null::int, 1, null, 2, 3, 4, null, 5))[1:3] arr;
arr
-------------
{1,2,3}
source to share