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 name_matrix (
rowid int,
name varchar(50)
);
insert into name_matrix values
(1, 'Smith,John'),
(2, 'Smith, John'),
(4, 'smith,john'),
(5, 'SMITH, JOHN');
CREATE TABLE
INSERT 0 4
select
rowid,
name,
initcap(split_part(name, ',', 1)) as last_name,
initcap(ltrim(split_part(name, ',', 2))) as first_name --ltrim removes space by default
from
name_matrix
| rowid | name | last_name | first_name |
|---|---|---|---|
| 1 | Smith,John | Smith | John |
| 2 | Smith, John | Smith | John |
| 4 | smith,john | Smith | John |
| 5 | SMITH, JOHN | Smith | John |
SELECT 4