Search code examples
jsonpostgresqlcastingpolymorphism

Strange JSON interpretation (polymorphic type), how to workaround?

This legal(!) CASE construct returns a JSON datatype:

 SELECT CASE WHEN true THEN to_json(1) ELSE to_json('hello') END;

but:

ERROR: could not determine polymorphic type because input has type "unknown"

It is not "polymorphic", it is JSON.

... So, as bad workaround (lost number/string JSON representations), 

 SELECT to_json(CASE WHEN true THEN 1::text ELSE 'hello' END);

Is there a better way to do this SQL-to-JSON cast?

Solution

  • Do it the other way round:

    SELECT CASE WHEN true THEN to_json(1) ELSE to_json(text 'hello') END;

    Declare 'hello' as type text. This way you retain 1 as number and 'hello' as string.
    The explicit cast 'hello'::text is equivalent.

    The reason is the Postgres type system. An unquoted 1 is a legal numeric constant and defaults to the Postgres data type integer. But 'hello' (with single quotes) is a string literal that starts out as type unknown. The function to_json() is polymorphic, its input parameter is defined as ANYELEMENT. The output depends on the input data type. And it does not know what to do with data type unknown. Hence the error message.

    The result data type is json in either case (which is a regular Postgres data type), but that is orthogonal to the problem.

    Related: