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

Are you actually trying to change the column names themselves? Like an alter table query? Or just the content of the rows? — Phil Cairns, Nov 14 '14 at 06:17
I don't want to change the column names. Just want to replace the content of rows. — akhi, Nov 14 '14 at 06:20

Vivek S. · Accepted Answer · 2014-11-14T10:56:26.153

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

Perfect !! This is what I was looking for. Thanks varchar. I can now extend this to pick the old-val, new-val pair from another table(say sample_1, which has these two columns and each value is unique). The ultimate goal is to replace all the old-value in table 'sample' by picking the new value from sample_1 table. I am thinking to get all old-new-value mapping pair as hstore and then looping it appropriately in 'Update' query. Is this fine or you have a better suggestion? — akhi, Nov 19 '14 at 08:45

score 0 · Answer 2 · answered Nov 14 '14 at 06:26

0

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.

answered Nov 14 '14 at 06:26

khampson

14,700
4
41
43

Yes, this is probably a limitation in update to specify the column name. I wanted to know expert views as if there is any tricky way to achieve it without specifying column name, which will make my task easier. – akhi Nov 14 '14 at 06:31
You could dynamically generate the SQL, as the other two answers have alluded to, but I'm not sure I get the use case you're looking for. You want to set *all* columns in a table to the exact same value? – khampson Nov 15 '14 at 02:17

score 0 · Answer 3 · answered Nov 14 '14 at 06:42

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/

Oops, I took it to mean across all tables, not just the one table. You can change the first query above to include a "where table_name='mytable'" if you want. — Phil Cairns, Nov 14 '14 at 06:44

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

3 Answers3

Linked