CODEWITHSUNDEEP
databasepostgresqlreplacedynamic-sql
Puneet Purohit
Puneet Purohit

Reputation: 1281

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

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.

Upvotes: 2

Views: 3333

Answers (3)

Esther
Esther

Reputation: 391

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;
$$;

Upvotes: 0

HeliXZz
HeliXZz

Reputation: 41

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.

Upvotes: 4

Vivek S.
Vivek S.

Reputation: 21915

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;
$$;

Upvotes: 2

Related Questions