Use left hand column for null values

On Fri, Jan 19, 2018 at 8:46 AM, <kpi6288@gmail.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.

[David G. Johnston]

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.

My concern was performance, because I feared that coalesce would evaluate e.g. column c1 50 times. But I did some testing and it seems to scale linear. 100,000 rows with 50 columns take 25 seconds on my test machine. Not exciting but something I can work with.

I played already with arrays but what I came up with was slower than the plain coalesce.

Klaus