I want to perform a fill-down operation in Postgres.
DDL:
create table brands (
id int,
category varchar(20),
brand_name varchar(20)
);
insert into brands values
(1,'chocolates','5-star')
,(2,null,'dairy milk')
,(3,null,'perk')
,(4,null,'eclair')
,(5,'Biscuits','britannia')
,(6,null,'good day')
,(7,null,'boost')
,(8,'shampoo','h&s')
,(9,null,'dove')
;
Expected output is:
category | brand_name |
---|---|
chocolates | 5-star |
chocolates | dairy milk |
chocolates | perk |
chocolates | eclair |
Biscuits | britannia |
Biscuits | good day |
Biscuits | boost |
Shampoo | h&s |
Shampoo | dove |
I tried using the following script but it doesn't seem to work.
select id,
first_value(category)
over(order by case when category is not null then id end desc nulls last) as category,
brand_name
from brands
Can someone suggest a fix?
In MS SQL the following snippet seems to work fine:
select id,
first_value (category) IGNORE NULLS
over(order by id desc
rows between current row and unbounded following) as category,
brand_name
FROM brands
ORDER BY id