What is the best way to replace a string with another string on entire table?

Question

My question is similar to below but I want to replace it for any column:

postgresql - replace all instances of a string within text field

for instance - replace all instances of cat with dog in entire table(all rows)

I am expecting something like below query to work for all columns(without specifying the column name in update query).

UPDATE table_name SET information_schema.columns replace (information_schema.columns, 'old_value', 'new_value');

which actually doesn't seems to work.

Any suggestions please ??

Answer 1

create table sample (s varchar,s1 varchar,s2 varchar,s3 varchar,s4 varchar);

insert into sample values ('A','A','A','A','A');
insert into sample values('AB','AB','A','AB','AB');
insert into sample values('A','AB','A','AB','A');
insert into sample values('CA','A','A','CA','CA');

select * from sample 

And try like this

create or replace function f1(_table text,_oldVal text,_newVal text) returns void as 
$$
declare 
rw record;
begin
for rw in 
    select 'UPDATE '||$1||' SET '||C.COLUMN_NAME||' = REPLACE ('||C.COLUMN_NAME||','''||$2||''','''||$3||'''); ' QRY
    FROM (select column_name from information_schema.columns where table_schema='public' and table_name =$1)c
loop
    EXECUTE rw.QRY;
end loop;
end;
$$language plpgsql

And Call

select f1('sample','A','Z')

select * from sample 

---

OR

do 
$$
declare 
rw record;
begin
for rw in 
    select 'UPDATE sample SET '||C.COLUMN_NAME||' = REPLACE ('||C.COLUMN_NAME||',''Z'',''A''); ' QRY
    FROM (select column_name from information_schema.columns where table_schema='public' and table_name ='sample')c
loop
    EXECUTE rw.QRY;
end loop;
end;
$$;

select * from sample 

Answer 2

UPDATE doesn't work that way. You need to specify each of the columns you want to change with column = value expressions, separated by a comma.

i.e.

UPDATE foo
SET bar = 1, baz = 2
WHERE bar = 5;

Here is more info about UPDATE, including the grammar.

Answer 3

You're going to have to write some code. You'll need to retrieve the names of all tables and all the names that are textual:

select distinct table_name, column_name from information_schema.columns
    where table_schema='public'
and (data_type like 'char%' or data_type='text')
order by table_name, column_name;

and then for each table/column combination, build a query to make the replacement:

myQuery = "update " + table_name +
    " set " + column_name + " = replace(" + column_name +
    ", 'old_val', 'new_val')"

... and then execute the query in whatever language you're using. You could write this in plpgsql, but seriously, don't. This is a really dangerous piece of code.

If you're looking at doing this for a Wordpress or Drupal site, visit these guys: https://interconnectit.com/products/search-and-replace-for-wordpress-databases/