How to update a row to add a character to a column

November 19, 2021

I am using PostgreSQL. I have a table, that has a column that I want to insert a character into.

For example, column unique_reference = "1121464146-1-1R". I would like to insert a "-" character before the "R", so that it is "1121464146-1-1-R".

I have the following incomplete SQL:

update powwowjournalentry
SET unique_reference = -- something?
where unique_reference LIKE '%R' and unique_reference NOT LIKE '%-R';

>Solution :

You can use Replace:

update powwowjournalentry
SET unique_reference = REPLACE (unique_reference, 'R', '-R')
where unique_reference LIKE '%R' and unique_reference NOT LIKE '%-R';

Source: postgresqltutorial