我有一个删除插入CTE，以一种奇怪的方式失败 [英] I have a delete-insert CTE that fails in a strange manner

问题描述

这是一个成功的例子：

  x为（
从common.companies删除='0f8ed160-370a-47bb-b4bf-2dcf79100a52'
返回row_to_json（公司）作为old_data，null作为new_data，'common.companies'作为模型，id，'删除'作为动作）
 insert into 
 select old_data，null，model，id，action，'0b392013-f680-45a6-b19a-34f3d42d0120'from x; edit_history（old_data，new_data，model，model_pk，action，submitter） 
 
 INSERT 0 1 
  

请注意，insert-select

 这是一个失败的示例： delete from common.companies where id ='160d7ef2-807c-4fe0-bfed-7d282c031610'
返回row_to_json（公司）为old_data，null为new_data，'common.companies'为模型，id，'delete'as action）
 insert into edit_history（old_data，new_data，model，model_pk，action，submitter）
 select old_data，new_data，model，id，action，'0b392013-f680-45a6 -b19a-34f3d42d0120'from x; 
 
错误：未能找到从未知到json的转换函数
  

这个例子，而不是第二列中的显式null，我有 new_data ，它从delete语句返回为null。

如果两个值都为null，为什么第二个例子clobber我这个错误？

解决方案

在第一个例子中，你提供一个

在第二个示例中，您提供NULL一个步骤（在CTE中），表达式必须输入并分配类型 unknown 。对于其他 常数 （像数字常量： 123 ），Postgres可以派生一个更适合的默认数据类型，但NULL（或字符串文字 foo'）可以是任何。并且未知和 json 之间没有定义类型转换。

在CTE中将NULL转换为正确的数据类型，以避免该问题（正如您现在发现的那样）。

或使用 text 踏步石在铸造链如果它迟到了。

您可以将演示简化为以下内容：

工程：

  SELECT NULL :: json; 
  

失败：

 code> SELECT new_data :: json 
 FROM（SELECT NULL As new_data）t; 
  

再次运行：

  SELECT new_data 
 FROM（SELECT NULL :: json AS new_data）t; 
  

或：

 code> SELECT new_data :: text :: json 
 FROM（SELECT NULL As new_data）t; 
  

This is an example of it succeeding:

with x as ( 
    delete from common.companies where id = '0f8ed160-370a-47bb-b4bf-2dcf79100a52' 
    returning row_to_json(companies) as old_data, null as new_data, 'common.companies' as model, id, 'delete' as action)
insert into edit_history (old_data, new_data, model, model_pk, action, submitter)
select old_data, null, model, id, action, '0b392013-f680-45a6-b19a-34f3d42d0120' from x;

INSERT 0 1

Note that the second column in the insert-select is explicity null.

Here is an example that fails:

with x as (
    delete from common.companies where id = '160d7ef2-807c-4fe0-bfed-7d282c031610' 
    returning row_to_json(companies) as old_data, null as new_data, 'common.companies' as model, id, 'delete' as action)
insert into edit_history (old_data, new_data, model, model_pk, action, submitter)                                                                   
select old_data, new_data, model, id, action, '0b392013-f680-45a6-b19a-34f3d42d0120' from x;

ERROR:  failed to find conversion function from unknown to json

Note in this example that instead of an explicit null in the second column, I've got new_data, which is returned as null from the delete statement.

If both values are null, why does the second example clobber me with this error? I've been over both carefully, and this is the only functional difference.

解决方案

In the first example you provide a yet untyped NULL to the INSERT statement.

In the second example you provide NULL one step earlier (in the CTE), the expression has to be typed and is assigned the type unknown. For other constants (like numeric constants: 123), Postgres can derive a more fitting default data type, but NULL (or a string literal 'foo') could be anything. And there is no type conversion defined between unknown and json.

Cast NULL to the right data type in the CTE to avoid the problem (as you found yourself by now).
Or use text as stepping stone in the casting chain if it's to late for that. Everything can be cast to / from text.

You can simplify your demo to the following:

Works:

SELECT NULL::json;

Fails:

SELECT new_data::json
FROM  (SELECT NULL AS new_data) t;

Works again:

SELECT new_data
FROM  (SELECT NULL::json AS new_data) t;

Or:

SELECT new_data::text::json
FROM  (SELECT NULL AS new_data) t;

这篇关于我有一个删除插入CTE，以一种奇怪的方式失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！