Replace characters in all tables in Database Postgres

Question

I'm using PostgreSQL with pgadmin. I need to replace multiple characters in all fields of type string, and in all tables in my database.

Welcome on stack, please read the stackoverflow.com/faq, and try to create a question with additional informations instead of a statement. — Damian Leszczyński - Vash
– Damian Leszczyński - Vash, Commented Jun 2, 2011 at 6:57

Will A · Accepted Answer · 2011-06-02 07:10:39Z

1

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.

edited Jun 2, 2011 at 7:10

answered Jun 2, 2011 at 7:00

Will A

25k5 gold badges54 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bohemian Over a year ago

You may want to exclude the information schema tables themselves

Will A Over a year ago

@Bohemian - +1. Indeed - and various other bits 'n' bobs - I'm not a hardcore Postgreser (?!) - the query is intended to be a starting point, there'll be a lot to gain from reviewing the output rather than blindly executing!

Bohemian · Accepted Answer · 2011-06-02 07:05:34Z

1

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.

answered Jun 2, 2011 at 7:05

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

1 Comment

José Antonio Vázquez Over a year ago

thank you very much. But as I do? I have no experience with sql

Collectives™ on Stack Overflow

Replace characters in all tables in Database Postgres

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related