The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/typeconv-overview.html
Description:
Hello, I'm trying to understand how json/jsonb is implicitly converted in
INSERT and UPDATE queries.
In section "10.1. Overview" there is the following:
If a type is not specified for a string literal, then the placeholder type
unknown is assigned initially, to be resolved in later stages as described
below.
Value Storage
SQL INSERT and UPDATE statements place the results of expressions into a
table. The expressions in the statement must be matched up with, and perhaps
converted to, the types of the target columns.
---
In section "10.4. Value Storage" there is:
Value Storage Type Conversion
1. Check for an exact match with the target.
2. Otherwise, try to convert the expression to the target type. This is
possible if an assignment cast between the two types is registered in the
pg_cast catalog (see CREATE CAST). Alternatively, if the expression is an
unknown-type literal, the contents of the literal string will be fed to the
input conversion routine for the target type.
3. Check to see if there is a sizing cast for the target type. A sizing cast
is a cast from that type to itself. If one is found in the pg_cast catalog,
apply it to the expression before storing into the destination column. The
implementation function for such a cast always takes an extra parameter of
type integer, which receives the destination column's atttypmod value
(typically its declared length, although the interpretation of atttypmod
varies for different data types), and it may take a third boolean parameter
that says whether the cast is explicit or implicit. The cast function is
responsible for applying any length-dependent semantics such as size
checking or truncation.
I used the following sql to test out jsonb through pg admin
DROP TABLE IF EXISTS jsonb_test;
CREATE TABLE jsonb_test
(
data jsonb
);
INSERT INTO jsonb_test VALUES ('{ "a": "1", "b": "2" }');
INSERT INTO jsonb_test VALUES ('{ "a": "1", "b": "2" }'::unknown);
-- This fails to insert with "column "data" is of type jsonb but expression
is of type text"
-- INSERT INTO jsonb_test VALUES ('{ "a": "1", "' || 'b": "2" }');
-- This failed to insert with "failed to find conversion function from
unknown to jsonb"
-- INSERT INTO jsonb_test VALUES (('{ "a": "1",' || '"b": "2" }')::unknown);
So based on the docs, my understanding is that the first INSERT is assigned
as "unknown", and there is an implicit conversion from "unknown" to "jsonb".
I can see that casting to "unknown" works as well in the second INSERT. The
third INSERT fails because there is no implicit cast from text to jsonb. But
if explicitly cast text to "unknown" as in the last INSERT, I get an error.
Is my understanding correct? If so, how do the conversion rules explain the
last INSERT?
