Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


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
1 rows affected

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

ANALYZE test;
--
--
--
-- Warm up the cache
--
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=28) (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=28) (actual rows=30000 loops=1)

Planning Time: 0.306 ms

Execution Time: 8.088 ms


--
-- 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

02e0f$e9b$c3f7$64c$fd5
02e0f$e9b$c3f7$64c-fd5


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.035 ms

Execution Time: 56.102 ms


--
--
--
--
-- 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

02e0f$e9b$c3f7$64c$fd5
02e0f$e9b$c3f7$64c-fd5


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.032 ms

Execution Time: 93.267 ms


--
--
--
--
-- 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

02e0f$e9b$c3f7$64c$fd5
02e0f$e9b$c3f7$64c-fd5


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.069 ms

Execution Time: 216.401 ms


--
--
--
--
-- 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

02e0f$e9b$c3f7$64c$fd5
02e0f$e9b$c3f7$64c-fd5


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.062 ms

Execution Time: 430.342 ms


--
--
--
--
--
-- 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.
--
--
-- 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


--
--
--
--
-- 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

02e0f$e9b$c3f7$64c$fd5
2
5df
1

02e0f$e9b$c3f7$64c$fd5
2
c46
2

02e0f$e9b$c3f7$64c$fd5
2
7f3c
3

02e0f$e9b$c3f7$64c$fd5
2
b9e
4

02e0f$e9b$c3f7$64c$fd5
2
f0e20
5


--
-- 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

02e0f$e9b$c3f7$64c$fd5
02e0f$e9b$c3f7$64c-fd5


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=24) (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.220 ms

Execution Time: 928.290 ms
t_field	result
bar$foo$john$doe$xxx	bar$foo$john$doe-xxx
02e0f$e9b$c3f7$64c$fd5	02e0f$e9b$c3f7$64c-fd5