主页 / dba / 问题 / 174439
Accepted
BrunoLM
Asked: 2017-05-25 05:31:00 +0800 CST

如何在 Postgres 中一次更新多个表？

  • 772

我有几个结构完全相同的表，我需要更新所有表中的值。

为此，我尝试构建以下脚本：

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.tablename具有正确的值（我插入了一个 tmp 表进行检查），但更新失败。

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

i.tablename只是声明上的插件UPDATE不起作用。

有没有办法让它工作？或者更简单的方法来一次更新所有表？

postgresql update

2 个回答

  1. Best Answer

    你实际上很接近..首先创建一些测试数据..

    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;

    接下来，我们将使用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)

    作为旁注，

    1. 一般来说，你应该使用information_schema标准化的东西来处理像这样的简单事情，而速度并不重要。
    2. 您可能应该通过添加子句来检查是否UPDATE需要运行。WHERE否则，您将一劳永逸地重写表格。
    3. 在 SQL 中，我们不使用这样的命名约定。没有必要这样做。如果knex_migrations有多个表，请考虑CREATE SCHEMA knex_migrations存储它们，而不是根据所有表的命名约定来搜索目录。
    • 6

  2. 您发布了一个基本可行的解决方案作为评论。

    但是，一个弱点仍然存在：如果您不对 中的表名进行模式限定，UPDATE并且该模式恰好不是当前搜索路径中的第一个，则可能会更新错误的表。改为考虑：

    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$;

    有关的：

    • 2

相关问题