Home > mailing lists

Thread: Use left hand column for null values

Use left hand column for null values

From

Date:

19 January 2018, 21:46:21

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

Thanks

Klaus

Re: Use left hand column for null values

From

"David G. Johnston"

Date:

19 January 2018, 21:59:40

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.