Re: Use left hand column for null values

On Fri, Jan 19, 2018 at 8:46 AM, <kpi6288(at)gmail(dot)com> wrote:

> I’m trying to fill up columns containing NULL with the most recent NOT
> NULL value from left to right.
>
> Example:
>
> Select 2, 1, null, null, 3
>
> Should be converted into
>
> 2, 1, 1, 1, 3
>
>
>
> The following query works but I wonder if there is an easier way for
> tables with 50 or more columns:
>
>
>
> with a (c1, c2, c3, c4, c5) as (
>
> values(2, 1, null::int, null::int, 3)
>
> )
>
> select
>
> c1,
>
> coalesce (c2, c1) as c2,
>
> coalesce (c3, c2, c1) as c3,
>
> coalesce (c4, c3, c2, c1) as c4,
>
> coalesce (c5, c4, c3, c2, c1) as c5
>
> from a
>
>
>
​My $0.02​

​Unless you have some need to generalize I'd say ​just brute-force it and
be done.

You could maybe play with arrays to get something that looks different but
I don't think it would be much shorter to code or easier to understand.

It is structured enough that you could probably build the query as a string
and then "EXECUTE" it but you'd probably spend more time working that out
than just coding it once.

If you don't have to return 50 columns but could return text (or an array)
it might be worth some R&D to find something similar.

One trick I use when dealing with structured but tedious queries is to
write them in a spreadsheet. I between auto-fill and formulas I can
usually speed up the input of the structured data compared to typing it out
line-by-line manually.

David J.