Replace characters in all tables in Database Postgres

I'm using PostgreSQL with pgadmin. I need to replace multiple characters in all fields of type string, and in all tables in m开发者_Go百科y database.

Use the below with caution - adjust as necessary, and make sure you review each and every resultant query (yes - this generates a resultset of queries) before copying the results and executing - as this may include queries that would attempt to modify views, calculated fields, system tables etc. etc.

I'm a T-SQL'er - but I believe the below should be valid in PostgresSQL.

SELECT 'UPDATE ' || TABLE_SCHEMA || '.' || TABLE_NAME ||
       ' SET ' || COLUMN_NAME || ' = REPLACE(' || 
       COLUMN_NAME || ', ''FROM'', ''TO'')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%char%'

For additional information, which may provide ideas on how to adjust this query, check out INFORMATION_SCHEMA.COLUMNS.

This will list all columns that are "character" type in your database:

SELECT t.tablename, a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_tables t ON t.tablename = c.relname
JOIN pg_type on pg_type.oid = a.atttypid
WHERE t.schemaname not in ('information_schema', 'pg_catalog')
and pg_type.typname in ('varchar', 'text', 'char')
order by 1,2;

Then you must go through and do the updates.