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 t AS
SELECT 'TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3' AS col, 1 AS id UNION ALL
SELECT 'TType=SEND|Status=Failure|URL=min://j?_a=3&ver=1.1|day=4' AS col, 2 AS id
2 rows affected
SELECT * FROM t
| col | id |
|---|---|
| TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3 | 1 |
| TType=SEND|Status=Failure|URL=min://j?_a=3&ver=1.1|day=4 | 2 |
SELECT t.*, s.result
FROM t
LEFT JOIN LATERAL (
SELECT string_agg(elements,'|') AS result
FROM regexp_split_to_table(t.col, '\|') as elements
WHERE split_part(elements, '=', 1) = ANY(ARRAY['TType', 'URL']) IS NOT TRUE
) s ON TRUE
| col | id | result |
|---|---|---|
| TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3 | 1 | Status=OK|day=3 |
| TType=SEND|Status=Failure|URL=min://j?_a=3&ver=1.1|day=4 | 2 | Status=Failure|day=4 |