I have a delete-insert CTE that fails in a strange

问题:

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.

回答1:

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

回答2:

The trick seems to be to cast the null to whatever the column type should be (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 needs to be done in the returning clause because that's creating a temp/pseudo table that (without the cast) gets defined who knows how... Postgres can't infer the type from the value. So when you try to insert that value into a different type, you get the conversion error.