I have several fields in db containing url inside different strings, part of which I need to replace with another one. F.e. the string is


I have many different strings like that.

I need to replace http://localhost:5000 with https://example.com but leave everything else untouched.

Also I need to do that without stopping the database / on a working database.

I was thinking about doing smth like that:

UPDATE table01 
  SET field01 = replace(field01, 'http://localhost:5000', 'https://example.com')

How can I make this query include all strings in db, not only in table01 & field01, but all tables & all fields? Just want to make sure that there are no references left to this url at all.

  • just repeat this for all referenced columns. It only You could know - what filedXXX contain URL (if real column name contain some pattern - You can prepare queries with information schema, but must be sure) – a_vlad Feb 3 '18 at 11:22
  • @a_vlad what about all strings in db, all fields, rows & etc included? How do I do that? Is there any difference if it's json? – GTXBxaKgCANmT9D9 Feb 3 '18 at 12:05
  • You leave a lot of out of the box - so it not possible to answer for You full. For example - in Your string 2 localhost:5000, do You want replace both or only first? You can prepare list of target columns - stackoverflow.com/questions/20194806/…, and use this list for prepare list of final commands (again with query) – a_vlad Feb 3 '18 at 14:32
  • @a_vlad I want to replace "localhost:5000" with "example.com" in any place in db without any prepared list. "Abc##localhost:5000" becomes "Abc##example.com" & 'Def$$localhost:5000/ghi/jkl' becomes 'Def$$example.com/ghi/jkl' – GTXBxaKgCANmT9D9 Feb 3 '18 at 16:09

First of all, your UPDATE without WHERE clause is needlessly (very!) expensive. It would write a new row version for all rows, even where nothing changes. Only update rows that actually need an update!

UPDATE table01 
SET    field01 = replace(field01, 'http://localhost:5000', 'https://example.com')
WHERE  field01 ~ 'http://localhost:5000';


Next, careful what you replace. You'll want to avoid false positives. Test before you apply it to the whole database. Your current expression:

replace(field01, 'http://localhost:5000', 'https://example.com')

would also change strings that probably shouldn't be changed. Like 'http://localhost:50000/pic/img/1.jpg' (note: 50000 instead of 5000)

To avoid that particular case:

regexp_replace(field01, 'http://localhost:5000\M', 'https://example.com', 'g')

Using regular expressions instead. \M at the end of the string, per documentation,

matches only at the end of a word

You may want to do more, depending on your exact requirements. So:

UPDATE table01 
SET    field01 =  regexp_replace(field01, 'http://localhost:5000\M'
                                        , 'https://example.com', 'g')
WHERE  field01 ~ 'http://localhost:5000\M';

Then apply to all string columns in a table. Ideally, only update each row once, to make this as cheap as possible. This plpgsql function executes the update for one given table:

CREATE OR REPLACE FUNCTION f_replace_everywhere(_pattern text, _new_string text, _tbl regclass, OUT updated_rows int) AS
   -- basic string types, possibly extend with citext, domains or custom types:
   _typ  CONSTANT regtype[] := '{text, bpchar, varchar}';
   _sql  text;
   SELECT INTO _sql     -- build command
          format('UPDATE %s SET %s WHERE %s'
               , _tbl
               , string_agg(format($$%1$s = regexp_replace(%1$s, $1, $2, 'g')$$, col), ', ')
               , string_agg(col || ' ~ $1', ' OR '))
   FROM  (
      SELECT quote_ident(attname) AS col
      FROM   pg_attribute
      WHERE  attrelid = _tbl            -- valid, visible, legal table name 
      AND    attnum >= 1                -- exclude tableoid & friends
      AND    NOT attisdropped           -- exclude dropped columns
      AND    NOT attnotnull             -- exclude columns defined NOT NULL!
      AND    atttypid = ANY(_typ)       -- only character types
      ORDER  BY attnum
      ) sub;

   -- Test
   -- RAISE NOTICE '%', _sql;

   -- Execute
      updated_rows := 0;                         -- nothing to update
      EXECUTE _sql
      USING _pattern, _new_string;

      GET DIAGNOSTICS updated_rows = ROW_COUNT;  -- Report number of affected rows
   END IF;
$func$  LANGUAGE plpgsql;

Call to apply to a single table:

SELECT f_replace_everywhere( 'http://localhost:5000\M'
                           , 'https://example.com'
                           , 'my_table');

Closely related with detailed explanation:

Finally, apply to all relevant tables in our database. Don't touch system tables. You can loop through all tables based on pg_tables or information_schema.tables. See:

I'll leave that last step to you.

  • updated my original post – GTXBxaKgCANmT9D9 Feb 3 '18 at 23:19
  • 1
    I have rolled back the question edit. The question was about string columns and has been answered. If you have a question about adjusting the answers for json, post a new question, with a link to this one. – ypercubeᵀᴹ Feb 4 '18 at 10:46

You could query the catalog for all columns that are long enough to hold that value, and generate and execute dynamic sql, eg.

DO $$
  mysql varchar;
  Trec record;
  Crec record;
  vcomma varchar := '';
  for Trec in SELECT table_schema, table_name FROM information_schema.tables t
             where table_schema = 'public'
                -- and table_name = 't1'
               and table_type != 'VIEW'
               and exists ( select null from information_schema.columns c
                             where c.table_schema = t.table_schema
                             and c.table_name = t.table_name
                             and c.character_maximum_length >= 30)
    raise notice 'Current table is %', quote_ident(Trec.table_name);
    mysql := format('update %s set ',Trec.table_name);
    vcomma := '';
    for Crec in SELECT column_name FROM information_schema.columns c
             where table_schema = Trec.table_schema
               and c.table_name = Trec.table_name
               and c.character_maximum_length >= 30
      raise notice 'Current column is %', quote_ident(Crec.column_name);
      mysql := mysql || vcomma ||  Crec.column_name
               || ' = replace(' || Crec.column_name
               || ',' || '''http://the.olde.domain/the_olde_url'','
               || '''http://the.newer.domain/the_newer_url'')';
      vcomma := ', ';
    end loop;
    raise notice 'SQL is %', mysql;
    execute mysql;
  end loop;
end $$;

You can of course drop the "raise notice"'s. One more thing, I ran this on a 9.6, don't know about 9.5.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.