I have a CTE with delete-insert, which is not so strange

Question

This is an example of success:

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 -select insert is a null expression.

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 explicit null in the second column, I have new_data , which is returned as null from the delete statement.

If both values ​​are zero, why does the second example knock me down with this error? I walked over them carefully, and this is the only functional difference.

Answer 1

In the first example, you provide an unexplored NULL in an INSERT .

In the second example, you provide NULL one step earlier (in CTE), the expression must be printed, and it is assigned the unknown type. For other constants (e.g. numeric constants: 123 ), Postgres can get a more appropriate default data type, but NULL (or the string literal 'foo' ) can be anything. And there is no type conversion defined between unknown and json .

Include NULL in the desired data type in the CTE to avoid the problem (since you were at the moment).
Or use text as a stepping stone in the casting chain if it's too late for that. Everything can be dropped to / from text .

You can simplify your demo:

Works:

 SELECT NULL::json; 

Fails:

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

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

Answer 2

It seems that the trick should be distinguishing zero from the type of the column (json in my case):

 with x as ( delete from common.companies where id = '160d7ef2-807c-4fe0-bfed-7d282c031610' returning row_to_json(companies) as old_data, null::json 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; 

This must be done in the return clause, because a pace / pseudo table is created, which (without translation) is determined, who knows how ... Postgres cannot infer the type from the value. Therefore, when you try to insert this value into another type, you get a conversion error.