Depending on how you would want to deal with possible NULL values, concat_ws()
concat_ws()
is probably your safest and simplest way to go:
UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')
concat_ws()
ignores NULL
values. With plain concatenation (||
), one NULL
field would make the whole pattern NULL
. It was introduced with Postgres 9.1.
The added WHERE
clause prevents empty updates. This enhances performance a lot if many rows wouldn't change anyway.