Postgres 15

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

CREATE TABLE test (id int, firstname text, lastname text);

INSERT INTO test VALUES
  (1,'albert','einsten')
, (2,'isaac','newton')
, (3, null, 'curie')
, (4,'alexandre','Graham Bell')
, (5,'thomas','Edison')
;

CREATE TABLE



INSERT 0 5


SELECT *
FROM  test t0
    , (SELECT row_number() OVER (ORDER BY random()) AS rn, id        FROM test) t1
JOIN  (SELECT row_number() OVER (ORDER BY random()) AS rn, firstname FROM test) t2 USING (rn)
JOIN  (SELECT row_number() OVER (ORDER BY random()) AS rn, lastname  FROM test) t3 USING (rn)
WHERE  t0.id = t1.id;


id
firstname
lastname
rn
id
firstname
lastname


4
alexandre
Graham Bell
1
4
alexandre
curie

2
isaac
newton
2
2
null
einsten

3
null
curie
3
3
isaac
Graham Bell

1
albert
einsten
4
1
albert
Edison

5
thomas
Edison
5
5
thomas
newton



SELECT 5


-- Shuffle all (incl. NULL values)
UPDATE test t0
SET    firstname = t2.firstname
     , lastname  = t3.lastname
FROM  (SELECT row_number() OVER (ORDER BY random()) AS rn, id        FROM test) t1
JOIN  (SELECT row_number() OVER (ORDER BY random()) AS rn, firstname FROM test) t2 USING (rn)
JOIN  (SELECT row_number() OVER (ORDER BY random()) AS rn, lastname  FROM test) t3 USING (rn)
WHERE  t0.id = t1.id
RETURNING t0.*;


id
firstname
lastname


3
alexandre
curie

5
null
einsten

1
isaac
Edison

2
thomas
Graham Bell

4
albert
newton



UPDATE 5


-- Only shuffle not null values in column firstname
UPDATE test t0
SET    firstname = t2.firstname
FROM  (SELECT row_number() OVER (ORDER BY random()) AS rn, id        FROM test WHERE firstname IS NOT NULL) t1
JOIN  (SELECT row_number() OVER (ORDER BY random()) AS rn, firstname FROM test WHERE firstname IS NOT NULL) t2 USING (rn)
WHERE  t0.id = t1.id
AND    t0.firstname IS NOT NULL
RETURNING t0.*;


id
firstname
lastname


4
isaac
newton

3
thomas
curie

1
alexandre
Edison

2
albert
Graham Bell



UPDATE 4


TABLE test;


id
firstname
lastname


5
null
einsten

4
isaac
newton

3
thomas
curie

1
alexandre
Edison

2
albert
Graham Bell



SELECT 5

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