Mask or anonymise values

CREATE TEMP TABLE people AS SELECT * FROM (VALUES
  (1, 'Alice Ng',   '4111111111111111'), (2, 'Bob Okafor', '5500005555555559'), (3, 'Carla Diaz', '1234'),
  (4, 'Dev Patel',  '12'),               (5, 'Eve Larsen', NULL),               (6, 'Frank Moore', '378282246310005'),
  (7, 'Alice Ng',   '12345')
) t(id, name, card);
-- masked keeps the last four characters of a longer value and hides all of a short one. Shuffled puts the rows in the order of a
-- hash of the id and gives each row the name of the next row in that order (the last takes the first), so no row keeps its own name.
-- Random replaces the name with a number; fake picks a stand-in from a list by a hash of the name, so a name that occurs twice
-- gets the same stand-in.
CREATE TEMP TABLE result AS
WITH ranked AS (SELECT id, name, row_number() OVER (ORDER BY hash(id)) AS pos, count(*) OVER () AS n FROM people),
moved AS (SELECT a.id, b.id AS shuffled_from, b.name AS shuffled_name FROM ranked a JOIN ranked b ON b.pos = a.pos % a.n + 1)
SELECT p.id, p.name,
       CASE WHEN length(card) > 4 THEN repeat('*', length(card) - 4) || right(card, 4) ELSE repeat('*', length(card)) END AS card_masked,
       m.shuffled_from, m.shuffled_name,
       'user_' || lpad((random() * 99999)::INTEGER::VARCHAR, 5, '0') AS random_name,
       ['Pat Example', 'Sam Sample', 'Kim Placeholder'][(hash(p.name) % 3 + 1)::BIGINT] AS fake_name
FROM people p JOIN moved m USING (id);
-- random_name differs on every run, so it is checked below and not printed
SELECT * EXCLUDE (random_name) FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(card_masked ORDER BY id) FROM result) = ['************1111', '************5559', '****', '**', NULL, '***********0005', '*2345'] AND (SELECT count(*) FROM result WHERE shuffled_from = id) = 0 AND (SELECT count(DISTINCT shuffled_from) FROM result) = 7 AND (SELECT list(name ORDER BY name, id) FROM result) = (SELECT list(shuffled_name ORDER BY shuffled_name, id) FROM result) AND (SELECT count(*) FROM result r JOIN people p ON p.id = r.shuffled_from WHERE p.name <> r.shuffled_name) = 0 AND (SELECT count(*) FROM result WHERE regexp_matches(random_name, '^user_[0-9]{5}$') AND random_name <> name) = 7 AND (SELECT count(*) FROM result WHERE fake_name = name) = 0 AND (SELECT count(*) FROM result WHERE fake_name IN ('Pat Example', 'Sam Sample', 'Kim Placeholder')) = 7 AND (SELECT count(DISTINCT fake_name) FROM result WHERE name = 'Alice Ng') = 1) THEN 'as expected' ELSE error('the cards were not masked to their last four, the names not moved to other rows, randomised and faked, or the two Alice Ng rows did not get the same stand-in') END AS verdict;