Início / dba / Perguntas / 228046
Accepted
ChrisJ
Asked: 2019-01-25 19:39:38 +0800 CST

Por que preciso converter NULL para o tipo de coluna?

  • 772

Eu tenho um auxiliar que está gerando algum código para fazer atualizações em massa para mim e gera SQL que se parece com isso:

(Ambos os campos ativo e principal são do tipo boolean)

UPDATE fields as t set "active" = new_values."active","core" = new_values."core"
FROM (values 
(true,NULL,3419),
(false,NULL,3420)
) as new_values("active","core","id") WHERE new_values.id = t.id;

No entanto, ele falha com:

ERROR: column "core" is of type boolean but expression is of type text

Eu posso fazê-lo funcionar adicionando ::booleanaos nulos, mas isso parece estranho, por que NULL é considerado do tipo TEXT?

Além disso, é um pouco complicado de converter porque exigiria um pouco de retrabalho do código para saber para que tipo deve converter NULLs (a lista de colunas e valores está sendo gerada automaticamente a partir de uma matriz simples de objetos JSON) .

Por que isso é necessário e existe uma solução mais elegante que não exija que o código gerador saiba o tipo de NULLs?

Se for relevante, estou usando sequelize sobre Node.JS para fazer isso, mas também estou obtendo o mesmo resultado no cliente de linha de comando Postgres.

postgresql null

1 respostas

  1. Best Answer

    Esta é uma descoberta interessante. Normalmente, um NULL não tem tipo de dados assumido, como você pode ver aqui:

    SELECT pg_typeof(NULL);

 pg_typeof 
───────────
 unknown

    Isso muda quando uma VALUEStabela entra na imagem:

    SELECT pg_typeof(core) FROM (
    VALUES (NULL)
) new_values (core);

 pg_typeof 
───────────
 text

    Esse comportamento é descrito no código-fonte em https://doxygen.postgresql.org/parse__coerce_8c.html#l01373 :

     /*
  * If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
  * then resolve as type TEXT.  This situation comes up with constructs
  * like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
  * UNION SELECT 'bar'; It might seem desirable to leave the construct's
  * output type as UNKNOWN, but that really doesn't work, because we'd
  * probably end up needing a runtime coercion from UNKNOWN to something
  * else, and we usually won't have it.  We need to coerce the unknown
  * literals while they are still literals, so a decision has to be made
  * now.
  */

    (Sim, o código-fonte do PostgreSQL é relativamente fácil de entender e na maioria dos lugares, graças a excelentes comentários.)

    A saída, no entanto, pode ser a seguinte. Digamos que você esteja sempre gerando VALUESque correspondam a todas as colunas de uma determinada tabela (veja a segunda nota abaixo para outros casos). Do seu exemplo, um pequeno truque poderia ajudar:

    SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);

 active │ core │  id  
────────┼──────┼──────
 t      │      │ 1234

    Aqui você usa expressões de linha convertidas para o tipo da tabela e, em seguida, extrai-as de volta para uma tabela.

    Com base no acima, seu UPDATEpoderia parecer

    UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
           ((true, NULL, 3419)::fields),
           ((false, NULL, 3420)::fields)
     ) AS new_values(x) WHERE (x).id = t.id;

    Notas:

    • Eu removi as aspas duplas para melhor legibilidade humana, mas você pode mantê-las, pois elas ajudam na geração de nomes (coluna).
    • se você precisar apenas de um subconjunto das colunas, poderá criar tipos personalizados para essa finalidade. Use-os da mesma forma que você faria acima (onde eu uso o tipo criado automaticamente com a tabela, mantendo a estrutura de linhas desta última).

    Olhe para a coisa toda trabalhando em dbfiddle .

    • 19

relate perguntas