PostgreSQL – How to Update Multiple Tables at Once

dynamic-sqlplpgsqlpostgresqlupdate

I have several tables with the exact same structure and I need to update a value in all tables.

In order to do so I tried to build the following script:

DO
$do$
DECLARE
  i pg_tables%rowtype;
BEGIN
FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
LOOP
    UPDATE i.tablename SET name = replace(name, '.js', '.ts');
END LOOP;
END
$do$;

I could see that i.tablename has the correct value (I inserted in a tmp table to check), but the update fails.

name: error
length: 223
severity: ERROR
code: 42P01
internalPosition: 8
internalQuery: UPDATE i."tablename" SET name = replace(name, '.js', '.ts')
where: PL/pgSQL function inline_code_block line 7 at SQL statement
file: parse_relation.c
line: 965
routine: parserOpenTable

Just plugin i.tablename on the UPDATE statement doesn't work.

Is there a way to make it work? Or an easier way to update all tables at once?

Best Answer

You're actually close.. first create some test data..

CREATE TABLE foo_knex_migrations ( name )
  AS VALUES ('test.js'),('test2.js'),('bicycles');
CREATE TABLE bar_knex_migrations AS TABLE foo_knex_migrations;
CREATE TABLE baz_knex_migrations AS TABLE foo_knex_migrations;

Next, we're going to use EXECUTE...FORMAT(), with %I.

DO
$do$
  DECLARE
    i pg_tables%rowtype;
  BEGIN
  FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
  LOOP
    EXECUTE FORMAT(
      $$
        UPDATE %I
        SET name = replace(name, '.js', '.ts');
      $$,
      i.tablename
    );
  END LOOP;
  END
$do$;


TABLE baz_knex_migrations ;
   name   
----------
 test.ts
 test2.ts
 bicycles
(3 rows)

test=# TABLE foo_knex_migrations ;
   name   
----------
 test.ts
 test2.ts
 bicycles
(3 rows)

As a side note,

  1. In general, you should be using information_schema which is standardized for simple things like this, and where speed doesn't matter.
  2. You should probably be checking to see if the UPDATE needs to run by adding a WHERE clause. Otherwise, you're rewriting the table for nothing.
  3. In SQL, we don't use naming conventions like this. There is no need for that. If knex_migrations has multiple tables, consider CREATE SCHEMA knex_migrations to store them, rather than searing through the catalog based on a naming convention for all tables.
Related Question