Use left hand column for null values

From: <kpi6288(at)gmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Use left hand column for null values
Date: 2018-01-19 15:46:21
Message-ID: 006801d3913c$a7b7c420$f7274c60$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hmidi slim 2018-01-19 15:47:15 Re: Notify client when a table was full
Previous Message David G. Johnston 2018-01-19 15:44:25 Re: Notify client when a table was full