By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE t1 ( id INT, employee TEXT);
INSERT INTO t1 VALUES
(1, 'Mr. John Cole Thornton'),
(2, 'Mr. Paul George Mckenzie'),
(3, 'Mr. George Mick McDoughal'),
(4, 'Ms. Emily Suzan Flemming'),
(5, 'Mr. Alan Bourdillion Traherne');
INSERT 0 5
CREATE TABLE t2 (id INT, first_name TEXT);
INSERT INTO t2 VALUES
(1, 'Emily'),
(2, 'John'),
(3, 'George'),
(4, 'Suzan'),
(5, 'Paul'),
(6, 'Alan'),
(7, 'Mary'),
(8, 'Mick'),
(9, 'Bourdillion'),
(10, 'Jim'),
(11, 'Cole')
INSERT 0 11
WITH RECURSIVE cte AS (
SELECT employee, 1 id
FROM t1
UNION ALL
SELECT REPLACE(employee, first_name, ''), id+1
FROM cte
JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte
WHERE id > ( SELECT MAX(id)
FROM t2 )
| employee |
|---|
| Mr. Thornton |
| Mr. Mckenzie |
| Mr. McDoughal |
| Ms. Flemming |
| Mr. Traherne |
SELECT 5