clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601446 fiddles created (47984 in the last week).

select version();
version
PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
 hidden batch(es)


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


INSERT INTO test (t_field) VALUES ('bar$foo$john$doe$xxx'); -- OP's own data
1 rows affected
 hidden batch(es)


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, 29999); --<<== Vary here -- -- For this fiddle, we only have 30,000 (29,999 + the OP's original datum) records -- (although relative magnitudes appear good), the individual fiddles use -- 300,000. -- -- 300,000 appears large enough to give reliable consistent results and small -- enough so that the fiddle doesn't fail too often - rarely fails on 30k. -- -- -- You can vary this number, but please consider using the individual fiddles for -- large numbers of records so as not to hit the db<>fiddle server too hard! -- -- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD --
29999 rows affected
 hidden batch(es)


ANALYZE test;
 hidden batch(es)


-- --
 hidden batch(es)


-- -- Warm up the cache --
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT id, t_field FROM test ORDER BY id, t_field;
QUERY PLAN
Incremental Sort (cost=0.33..2361.29 rows=30000 width=29) (actual rows=30000 loops=1)
Sort Key: id, t_field
Presorted Key: id
Full-sort Groups: 938 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB
-> Index Scan using test_pkey on test (cost=0.29..1011.29 rows=30000 width=29) (actual rows=30000 loops=1)
Planning Time: 0.263 ms
Execution Time: 6.587 ms
 hidden batch(es)


-- -- Approach 1: The fastest - using OVERLAY(), STRPOS() & LENGTH() -- SELECT t_field, OVERLAY(t_field PLACING '-' FROM LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '$') ) AS result FROM test LIMIT 2;
t_field result
bar$foo$john$doe$xxx bar$foo$john$doe-xxx
0951$8c02$1cb8$07450$5b1 0951$8c02$1cb8$07450-5b1
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT OVERLAY(t_field PLACING '-' FROM LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '$') ) AS result FROM test;
QUERY PLAN
Seq Scan on test (cost=0.00..972.00 rows=30000 width=32) (actual rows=30000 loops=1)
Planning Time: 0.029 ms
Execution Time: 24.285 ms
 hidden batch(es)


-- -- --
 hidden batch(es)


-- -- 2: - REGEXP_REPLACE() with REVERSE() -- SELECT t_field, REVERSE(REGEXP_REPLACE(REVERSE(t_field), '\$', '-')) AS result FROM test LIMIT 2;
t_field result
bar$foo$john$doe$xxx bar$foo$john$doe-xxx
0951$8c02$1cb8$07450$5b1 0951$8c02$1cb8$07450-5b1
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT REVERSE(REGEXP_REPLACE(REVERSE(t_field), '\$', '-')) AS result FROM test;
QUERY PLAN
Seq Scan on test (cost=0.00..747.00 rows=30000 width=32) (actual rows=30000 loops=1)
Planning Time: 0.027 ms
Execution Time: 38.029 ms
 hidden batch(es)


-- -- --
 hidden batch(es)


-- -- 3: REGEXP_REPLACE - only method that doesn't use REVERSE() -- SELECT t_field, REGEXP_REPLACE(t_field, '(.*)\$', '\1-' ) FROM test LIMIT 2;
t_field regexp_replace
bar$foo$john$doe$xxx bar$foo$john$doe-xxx
0951$8c02$1cb8$07450$5b1 0951$8c02$1cb8$07450-5b1
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT REGEXP_REPLACE(t_field, '(.*)\$', '\1-' ) FROM test;
QUERY PLAN
Seq Scan on test (cost=0.00..597.00 rows=30000 width=32) (actual rows=30000 loops=1)
Planning Time: 0.041 ms
Execution Time: 51.827 ms
 hidden batch(es)


-- -- --
 hidden batch(es)


-- -- 4: Another "pure" string function approach - very much less efficient that the fastest -- SELECT t_field, REVERSE( SUBSTRING(REVERSE(t_field) FROM 1 FOR POSITION('$' IN REVERSE(t_field)) - 1) || '-' || SUBSTRING(REVERSE(t_field) FROM POSITION('$' IN REVERSE(t_field)) + 1 FOR (LENGTH(REVERSE(t_field))))) FROM test LIMIT 2;
t_field reverse
bar$foo$john$doe$xxx bar$foo$john$doe-xxx
0951$8c02$1cb8$07450$5b1 0951$8c02$1cb8$07450-5b1
 hidden batch(es)


EXPLAIN(ANALYZE, TIMING OFF) SELECT REVERSE( SUBSTRING (REVERSE(t_field) FROM 1 FOR (LENGTH((STRING_TO_ARRAY(REVERSE(t_field), '$'))[1]))) || '-' || SUBSTRING (REVERSE(t_field) FROM 5 FOR (LENGTH(REVERSE(t_field))))) AS result FROM test;
QUERY PLAN
Seq Scan on test (cost=0.00..1422.00 rows=30000 width=32) (actual rows=30000 loops=1)
Planning Time: 0.032 ms
Execution Time: 65.399 ms
 hidden batch(es)


-- -- --
 hidden batch(es)


-- -- -- Approach 5: The slowest - a "curiosity" - uses STRING_TO_ARRAY(), -- UNNEST() and WITH ORDINALITY. Shown in detail on a whim! -- -- Far more detail and analysis is available on the individual fiddle -- with the solution in the answer. --
 hidden batch(es)


-- -- Step 1: UNNEST() with STRING_TO_ARRAY() - turn the reversed string array -- into a set of records -- SELECT UNNEST ( STRING_TO_ARRAY ( REVERSE ( ( SELECT t.t_field FROM test t WHERE t.id = 1 ) ), '$' ) );
unnest
xxx
eod
nhoj
oof
rab
 hidden batch(es)


-- -- --
 hidden batch(es)


-- -- 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;
t_field id elem num
bar$foo$john$doe$xxx 1 xxx 1
bar$foo$john$doe$xxx 1 eod 2
bar$foo$john$doe$xxx 1 nhoj 3
bar$foo$john$doe$xxx 1 oof 4
bar$foo$john$doe$xxx 1 rab 5
0951$8c02$1cb8$07450$5b1 2 1b5 1
0951$8c02$1cb8$07450$5b1 2 05470 2
0951$8c02$1cb8$07450$5b1 2 8bc1 3
0951$8c02$1cb8$07450$5b1 2 20c8 4
0951$8c02$1cb8$07450$5b1 2 1590 5
 hidden batch(es)


-- -- Step 3: 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;
t_field result
bar$foo$john$doe$xxx bar$foo$john$doe-xxx
0951$8c02$1cb8$07450$5b1 0951$8c02$1cb8$07450-5b1
 hidden batch(es)


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;
QUERY PLAN
GroupAggregate (cost=8.60..256019.60 rows=30000 width=36) (actual rows=30000 loops=1)
Group Key: t.id
-> Nested Loop (cost=8.60..5669.60 rows=90000 width=36) (actual rows=120000 loops=1)
-> Index Only Scan using test_pkey on test t (cost=0.29..1011.29 rows=30000 width=4) (actual rows=30000 loops=1)
Heap Fetches: 30000
-> Function Scan on unnest x (cost=8.31..8.44 rows=3 width=32) (actual rows=4 loops=30000)
Filter: (num > 1)
Rows Removed by Filter: 1
SubPlan 2
-> Index Scan using test_pkey on test test_1 (cost=0.29..8.30 rows=1 width=25) (actual rows=1 loops=30000)
Index Cond: (id = t.id)
SubPlan 1
-> Index Scan using test_pkey on test (cost=0.29..8.31 rows=1 width=32) (actual rows=1 loops=30000)
Index Cond: (id = t.id)
Planning Time: 0.158 ms
Execution Time: 359.140 ms
 hidden batch(es)