select version();

CREATE TABLE test ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, t_field TEXT );

INSERT INTO test (t_field) VALUES ('bar$foo$john$doe$xxx'); -- OP's own data

INSERT INTO test (t_field) SELECT LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' || LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) FROM GENERATE_SERIES(1, 299999); --<<== Vary here -- -- Vary the GENERATE_SERIES(1, 299999) number if the fiddle run fails! -- -- 299999 (+ 1 - OP) = 300,000 = large enough number to get reliable, consistent results -- and small enough so that the fiddle run doesn't fail too often. --

ANALYZE test;

-- -- -- Approach 5: The slowest - a "curiosity" - uses STRING_TO_ARRAY(), -- UNNEST() and WITH ORDINALITY. Shown in detail on a whim! -- -- Different approaches are shown and analysed. --

-- -- Step "-1" (minus one) - not shown in answer --

-- -- Turn the reversed string into an array -- SELECT STRING_TO_ARRAY ( REVERSE ( ( SELECT t.t_field FROM test t WHERE t.id = 1 ) ) , '$' );

-- -- Step 1: UNNEST() with STRING_TO_ARRAY() - turn the array into a set of records -- SELECT UNNEST ( STRING_TO_ARRAY ( REVERSE ( ( SELECT t.t_field FROM test t WHERE t.id = 1 ) ), '$' ) );

-- -- --

-- -- Step 2 - using the WITH ORDINALITY functionality to maintain the order of the -- various substrings within the group - i.e. by id. -- SELECT t.t_field, t.id, x.elem, x.num FROM test t LEFT JOIN LATERAL UNNEST(STRING_TO_ARRAY ( REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) WITH ORDINALITY AS x (elem, num) ON TRUE LIMIT 10;

-- -- From this post by Erwin Brandstetter -- -- https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number/8767450#8767450 -- -- the above can be rewritten. --

-- -- Step 2 (bis): Simplified alternative -- SELECT t.t_field, t.id, x.elem, x.num FROM test t, UNNEST(STRING_TO_ARRAY ( REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) WITH ORDINALITY AS x (elem, num) LIMIT 10;

-- -- --

-- -- Step 2 (ter): Even more simplified - it is my **_understanding_** that as PostgreSQL -- is currently implemented, this will work and there are no plans to change this -- in the forseeable future. Be aware that this solution may not continue to work! -- SELECT t.t_field, t.id, s.elem, ROW_NUMBER() OVER (PARTITION BY id) AS rn FROM test t, UNNEST(STRING_TO_ARRAY ( REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) AS s (elem) LIMIT 10;

-- -- LEFT JOIN LATERAL - WITH ORDINALITY --

-- -- Step 4: final step, we combine it all. Take the last 4 elements of the array (which -- remain unchanged) and recombine them using '$', but take the 1st (of the -- reversed string - [1]) and concatenate the hyphen and then reverse the lot. -- -- SELECT (SELECT t_field FROM test WHERE test.id = tab.id), REVERSE( (STRING_TO_ARRAY((SELECT REVERSE(t_field) FROM test WHERE test.id = tab.id), '$'))[1] || '-' || STRING_AGG(elem, '$')) AS result FROM ( SELECT t.id, x.elem, x.num FROM test t LEFT JOIN LATERAL UNNEST(STRING_TO_ARRAY(REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) WITH ORDINALITY AS x (elem, num) ON TRUE ) AS tab WHERE tab.num > 1 -- this is the last element of the original string (REVERSE()) GROUP BY tab.id -- required by STRING_AGG() naturally - otherwise, we'd get one huge string LIMIT 2;

EXPLAIN (ANALYZE, TIMING OFF) SELECT REVERSE( (STRING_TO_ARRAY((SELECT REVERSE(t_field) FROM test WHERE test.id = tab.id), '$'))[1] || '-' || STRING_AGG(elem, '$')) FROM ( SELECT t.id, x.elem, x.num FROM test t LEFT JOIN LATERAL UNNEST(STRING_TO_ARRAY(REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) WITH ORDINALITY AS x (elem, num) ON TRUE ) AS tab WHERE tab.num > 1 GROUP BY tab.id;

-- -- CROSS JOIN - WITH ORDINALITY --

SELECT tab.f, REVERSE( (STRING_TO_ARRAY((SELECT REVERSE(t_field) FROM test WHERE test.id = tab.id), '$'))[1] || '-' || STRING_AGG(tab.elem, '$')) AS result FROM ( SELECT t.t_field AS f, t.id, x.elem, x.num FROM test t, UNNEST(STRING_TO_ARRAY ( REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) WITH ORDINALITY AS x (elem, num) ) AS tab WHERE tab.num > 1 GROUP BY tab.id, tab.f LIMIT 2;

EXPLAIN (ANALYZE, TIMING OFF) SELECT tab.f, REVERSE( (STRING_TO_ARRAY((SELECT REVERSE(t_field) FROM test WHERE test.id = tab.id), '$'))[1] || '-' || STRING_AGG(tab.elem, '$')) AS result FROM ( SELECT t.t_field AS f, t.id, x.elem, x.num FROM test t, UNNEST(STRING_TO_ARRAY ( REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) WITH ORDINALITY AS x (elem, num) ) AS tab WHERE tab.num > 1 GROUP BY tab.id, tab.f;

-- -- CROSS JOIN - using ROW_NUMBER() OVER() --

SELECT t.t_field, t.id, s.elem, ROW_NUMBER() OVER (PARTITION BY id) AS rn FROM test t, UNNEST(STRING_TO_ARRAY ( REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) AS s (elem) LIMIT 10;

SELECT tab.f, REVERSE( (STRING_TO_ARRAY((SELECT REVERSE(t_field) FROM test WHERE test.id = tab.id), '$'))[1] || '-' || STRING_AGG(tab.elem, '$')) AS result FROM ( SELECT t.t_field AS f, t.id, s.elem, ROW_NUMBER() OVER (PARTITION BY id) AS rn FROM test t, UNNEST(STRING_TO_ARRAY ( REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) AS s (elem) ) AS tab WHERE rn > 1 GROUP BY tab.id, tab.f LIMIT 2;

-- -- The ROW_NUMBER() is much less performant than the WITH ORDINALITY (1.3 x) --

EXPLAIN (ANALYZE, TIMING OFF) SELECT tab.f, REVERSE( (STRING_TO_ARRAY((SELECT REVERSE(t_field) FROM test WHERE test.id = tab.id), '$'))[1] || '-' || STRING_AGG(tab.elem, '$')) AS result FROM ( SELECT t.t_field AS f, t.id, s.elem, ROW_NUMBER() OVER (PARTITION BY id) AS rn FROM test t, UNNEST(STRING_TO_ARRAY ( REVERSE((SELECT t_field FROM test WHERE test.id = t.id )), '$')) AS s (elem) ) AS tab WHERE rn > 1 GROUP BY tab.id, tab.f;