How do I substitute multiple values in Postgres? I need to do something like the following:
In column C
Case current value = x then replace by a
else if current value is y then replace by b
else if current value is z then replace by c...
Add a comment
|
2 Answers
Use CASE WHEN:
UPDATE table_name
SET column_name =
CASE WHEN column_name = 'x' THEN 'a'
WHEN column_name = 'y' THEN 'b'
WHEN column_name = 'z' THEN 'c'
...
ELSE column_name -- default
END
-- WHERE id = ?
1 Comment
user3016650
This is a perfect solution with excellent performance. Thanks lad2025.
If you happen to be working with text (or something that can be cast to text) you can try the translate function.
translate(string text, from text, to text) text:
Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are removed. translate('12345', '143', 'ax') a2x5`
