Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (id int, firstname varchar(128), lastname varchar(128));

CREATE TABLE


insert into test values(1,'albert','einsten');
insert into test values(2,'isaac','newton');
insert into test values(3,null, 'curie');
insert into test values(4,'alexandre','Graham Bell');
insert into test values(5,'thomas','Edison');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT *
FROM test
ORDER BY id


id
firstname
lastname


1
albert
einsten

2
isaac
newton

3
null
curie

4
alexandre
Graham Bell

5
thomas
Edison



SELECT 5


update test
SET firstname = t2.firstname,
  lastname = t2.lastname
FROM
(
 SELECT id, 
  COALESCE(LAG(firstname, 1) OVER (ORDER BY id), LAST_VALUE(firstname) OVER (ORDER BY id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) firstname,
  COALESCE(LEAD(lastname, 1) OVER (ORDER BY id), FIRST_VALUE(lastname) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING)) lastname
 FROM test t2
 WHERE firstname IS NOT NULL AND lastname IS NOT NULL
) t2
WHERE test.id = t2.id

UPDATE 4


SELECT *
FROM test
ORDER BY id


id
firstname
lastname


1
thomas
newton

2
albert
Graham Bell

3
null
curie

4
isaac
Edison

5
alexandre
einsten



SELECT 5

id	firstname	lastname
1	albert	einsten
2	isaac	newton
3	null	curie
4	alexandre	Graham Bell
5	thomas	Edison