Peter Krauss Peter Krauss - 3 years ago 142
JSON Question

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?

Answer Source

Do it the other way round:

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

I.e.: declare 'hello' as type text. This way you retain 1 as number and 'hello' as string.

An explicit cast ('hello'::text) does the trick, too. But it is slightly more efficient to declare the constant as text directly.

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' is just a string literal that starts out as type unknown. The function to_json() is polymorphic, that means it's input parameter is defined as ANYELEMENT. What it actually does 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:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download