Início / dba / Perguntas / 337238
Accepted
kitchenprinzessin
Asked: 2024-02-26 17:08:44 +0800 CST

Atualize colunas selecionadas contendo 0 com NULL

  • 772

Quero atualizar todas as colunas nomeadas com sufixo '[]' na tabela geochemse tiverem zeros (0) com NULL.

Esta consulta retorna todos os nomes de colunas relevantes:

select c.COLUMN_NAME 
  from information_schema.COLUMNS as c 
 where c.TABLE_NAME = 'geochem' 
   and c.COLUMN_NAME like '%\[%\]'

Exemplo de resultados:

Resultado

Em seguida, atualize os valores nessas colunas, por exemplo, colunaSiO2[%{wt}]

UPDATE geochem
SET "SiO2[%{wt}]" = NULL
WHERE "SiO2[%{wt}]" = 0;

Como posso combinar essas consultas para atualizar todas as colunas retornadas pelo primeiro trimestre, em vez de atualizar coluna por coluna manualmente?

postgresql

1 respostas

  1. Best Answer

    Primeiro pergunte-se se uma estrutura de tabela com uma coluna de valor único como sugerida por Akina nos comentários não seria mais prática.

    Embora esteja preso ao seu design atual, você precisa de SQL dinâmico para direcionar um conjunto dinâmico de colunas. Mas primeiro, otimize o(s) comando(s).

    Cada UPDATEgrava uma nova versão de linha para cada linha que não é excluída do arquivo UPDATE. Sua abordagem escreve outra nova versão de linha para cada coluna.

    Combine tudo isso:

    UPDATE geochem
SET    "SiO2[%{wt}]" = NULL
WHERE  "SiO2[%{wt}]" = 0;

UPDATE geochem
SET    "TiO2[%{wt}]" = NULL
WHERE  "TiO2[%{wt}]" = 0;

-- more?

    ... em um único UPDATE:

    UPDATE geochem
SET    "SiO2[%{wt}]" = NULLIF("SiO2[%{wt}]", 0)
,      "TiO2[%{wt}]" = NULLIF("TiO2[%{wt}]", 0)
 -- more?
WHERE  0 = ANY ("SiO2[%{wt}]", "TiO2[%{wt}]");  -- more?

    Observe a cláusula adaptada WHEREpara verificar se alguma das colunas de destino contém 0 . NULLIFpor coluna só anula se for 0 .

    Para fazer isso dinamicamente, concatene a instrução com segurança e execute:

    DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          'UPDATE public.geochem
SET    '
       || string_agg(format('%1$s = NULLIF(%1$s, 0)', c.col), E'\n,      ')
       || E'\nWHERE  0 = ANY ('
       || string_agg(c.col, ', ')
       || ');'
   FROM  (
      SELECT quote_ident(c.column_name) AS col  -- ! to be safe
      FROM   information_schema.columns AS c
      WHERE  c.table_name = 'geochem'
      AND    c.table_schema = 'public'  -- ! to be safe
      AND    c.column_name LIKE '%\[%\]'
      ) c;
      
   IF _sql IS NULL THEN
      RAISE WARNING 'Did not find any matching columns!';
   ELSE
      RAISE NOTICE '%', _sql;
      -- EXECUTE _sql  -- uncomment to actually execute
   END IF;
END
$do$

    Adicionei um dispositivo de segurança infantil, então isso só gera um aviso com a instrução a ser executada. Remova o comentário da EXECUTElinha (e comente a RAISE NOTICElinha) para executá-la imediatamente.

    • 1

relate perguntas