How can I update multiple tables at once in Postgres?

Question 1

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?

Question 2

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,

In general, you should be using information_schema which is standardized for simple things like this, and where speed doesn’t matter.
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.
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.

Question 3

You posted a basically working solution as comment.

A weaknesses remains, though: If you don’t schema-qualify the table name in the UPDATE and the schema does not happen to be the first in the current search path, the wrong tables might be updated. Consider instead:

DO
$do$
DECLARE
   sch text := 'public';  -- your schema here
   tbl text;
BEGIN
   FOR tbl IN
      SELECT tablename FROM pg_catalog.pg_tables
      WHERE  schemaname = sch
      AND    tablename LIKE '%_knex_migrations'
   LOOP
      EXECUTE format($$UPDATE %I.%I SET name = replace(name, '.js', '.ts')$$, sch, tbl); 
   END LOOP;
END
$do$;

How can I update multiple tables at once in Postgres?

Question :

Answer :

Leave a Reply Cancel reply