How to replace a string (data value) in whole database?

Question

I want to replace a substring from all the tables say I want to replace "Center" with "Centre" in postgres. So is there any way to do this ? I found a similar question to replace substring in particular column.

F.Y.I. : I am using "pgAdmin III". I am not able to resolve my problem through solution of the similar question. I don't know any table and any column name. So I want to replace the value in entire database and in all the columns.

As answered in the very question you linked to: `update table set field = replace(field, 'cat', 'dog')`. Do that for each table (and field) as appropriate. For a wholesale change, you can also dump the data, find/replace within the .sql file, and re-import. — Denis de Bernardy, Dec 01 '14 at 06:18
It's because you can't. You can only modify one table at a time, so need to iterate over the list of all tables, and run the query with all applicable columns for each table. Or, if you're sure you want this done wholesale, dump the data, find/replace, re-import. — Denis de Bernardy, Dec 01 '14 at 06:27
I don't know all the tables and column names which have that data value so it won't be possible with given approach. It will be thankful if you can illustrate more or remove duplicate flag from the question. — Puneet Purohit, Dec 01 '14 at 06:57
@PuneetPurohit do you know which are the tables to perform this task ? and which columns ?? — Vivek S., Dec 01 '14 at 08:33
@PuneetPurohit see this [answer](http://stackoverflow.com/a/26928214/3682599) its a kind of your problem, But in your case without knowing `column`'s name (where the `Center` is present) how can we replace the text? or you want to check `Center` in entire columns in your database ?? — Vivek S., Dec 01 '14 at 08:39
@PuneetPurohit As this question is marked as duplicate I cant answer here so see this http://pastie.org/9753635 Reply me if my answer worked for you — Vivek S., Dec 01 '14 at 09:16
@varchar: re-opened, in case you want to expand on that pastie as an answer. — Denis de Bernardy, Dec 01 '14 at 09:40
You'll have to use PL/PgSQL, queries over `information_schema` and some dynamic SQL with `EXECUTE format(...)` — Craig Ringer, Dec 01 '14 at 10:45

Vivek S. · Answer 1 · 2017-01-31T11:17:43.640

2

Use this select query to get all the tables and its columns(columns with data type text or character varying since you want to update a text field)

SELECT column_name,table_name 
FROM   information_schema.columns 
WHERE  table_schema='public' 
AND    (data_type ='text' OR data_type ='character varying') 
AND    table_name in (SELECT table_name 
                      FROM   information_schema.tables 
                      WHERE  table_schema='public' AND table_type ='BASE TABLE')

Wrap this in a dynamic SQL for update according to your criteria

DO
$$
DECLARE 
rw record;
BEGIN
FOR rw IN 
    SELECT 'UPDATE '||C.table_name||'  SET '||C.column_name||' = REPLACE ('||C.COLUMN_NAME||',''Center'',''Centre''); ' QRY
    FROM (SELECT column_name,table_name 
          FROM   information_schema.columns 
          WHERE  table_schema='public' 
          AND    (data_type ='text' OR data_type ='character varying')
          AND    table_name in (SELECT table_name 
                                FROM   information_schema.tables 
                                WHERE  table_schema='public' 
                                AND    table_type ='BASE TABLE'))c

LOOP
    EXECUTE rw.QRY;
END LOOP;
END;
$$;

edited Jan 31 '17 at 11:17

answered Dec 01 '14 at 09:48

Vivek S.

19,945
7
68
85

ERROR: syntax error at or near "user" LINE 1: UPDATE user SET firstname = REPLACE (firstname,'Center','Ce... ^ QUERY: UPDATE user SET firstname = REPLACE (firstname,'Center','Centre'); CONTEXT: PL/pgSQL function inline_code_block line 17 at EXECUTE statement ********** Error ********** ERROR: syntax error at or near "user" SQL state: 42601 Context: PL/pgSQL function inline_code_block line 17 at EXECUTE statement – Puneet Purohit Dec 01 '14 at 10:19
@PuneetPurohit just run `SELECT` in the for loop and comment what you're getting – Vivek S. Dec 01 '14 at 10:23
I am getting all the columns from all the tables – Puneet Purohit Dec 01 '14 at 11:11
@PuneetPurohit not that first select the SELECT inside `FOR`, btw its working in my sample `DB` – Vivek S. Dec 01 '14 at 11:24
1

Very nice logic. Works for me beautifully with a bit of modification. – Oxymoron88 Jan 30 '17 at 06:02
@Oxymoron88 Glad to hear that – Vivek S. Jan 30 '17 at 08:12

score 2 · Answer 2 · edited Jul 05 '21 at 08:02

A little modification to Vivek's code:

DO
$$
DECLARE 
rw record;
BEGIN
FOR rw IN 
    SELECT 'UPDATE "'||C.table_name||'"  SET "'||C.column_name||'" = REPLACE ("'||C.COLUMN_NAME||'",''Center'',''Centre''); ' QRY
    FROM (SELECT column_name,table_name 
          FROM   information_schema.columns 
          WHERE  table_schema='public' 
          AND    (data_type ='text' OR data_type ='character varying')
          AND    table_name in (SELECT table_name 
                                FROM   information_schema.tables 
                                WHERE  table_schema='public' 
                                AND    table_type ='BASE TABLE'))c

LOOP
    EXECUTE rw.QRY;
END LOOP;
END;
$$;

I added some double quotes to make it still work when table name contains underline symbol. Works perfectly on PG 9.5.11-0ubuntu0.16.04.

score 0 · Answer 3 · answered May 31 '22 at 22:27

In case you need replace a specific word, such as "None" by NULL

DO
$$
DECLARE 
rw record;
BEGIN
FOR rw IN 
    SELECT 'UPDATE '||C.table_name||'  SET '||C.column_name||' = NULL where '||C.column_name||' = ''None'';' QRY
    FROM (SELECT column_name,table_name 
          FROM   information_schema.columns 
          WHERE  table_schema='public' 
          AND    (data_type ='text' OR data_type ='character varying')
          AND    table_name = 'rdb_deal')c
LOOP
    EXECUTE rw.QRY;
END LOOP;
END;
$$;

How to replace a string (data value) in whole database?

3 Answers3