Início / dba / Perguntas / 174439
Accepted
BrunoLM
Asked: 2017-05-25 05:31:00 +0800 CST

Como posso atualizar várias tabelas de uma só vez no Postgres?

  • 772

Tenho várias tabelas com exatamente a mesma estrutura e preciso atualizar um valor em todas as tabelas.

Para isso, tentei construir o seguinte 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$;

Pude ver que i.tablenametem o valor correto (inseri em uma tabela tmp para verificar), mas a atualização falha.

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

Apenas plugin i.tablenamena UPDATEdeclaração não funciona.

Existe uma maneira de fazê-lo funcionar? Ou uma maneira mais fácil de atualizar todas as tabelas de uma só vez?

postgresql update

2 respostas

  1. Best Answer

    Você está realmente perto .. primeiro crie alguns dados de teste ..

    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;

    Em seguida, vamos usar EXECUTE...FORMAT(), com %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)

    Como uma nota rodapé,

    1. Em geral, você deve usar o information_schemaque é padronizado para coisas simples como essa e onde a velocidade não importa.
    2. Você provavelmente deve estar verificando se UPDATEprecisa ser executado adicionando uma WHEREcláusula. Caso contrário, você está reescrevendo a tabela por nada.
    3. Em SQL, não usamos convenções de nomenclatura como esta. Não há necessidade disso. Se knex_migrationstiver várias tabelas, considere CREATE SCHEMA knex_migrationsarmazená-las, em vez de navegar pelo catálogo com base em uma convenção de nomenclatura para todas as tabelas.
    • 6

  2. Você postou uma solução basicamente funcional como comentário.

    No entanto, um ponto fraco permanece: se você não qualificar o nome da tabela no UPDATEesquema e o esquema não for o primeiro no caminho de pesquisa atual, as tabelas erradas poderão ser atualizadas. Considere em vez disso:

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

    Relacionado:

    • 2

relate perguntas