Implicit type conversion for json/jsonb
| От | PG Doc comments form |
|---|---|
| Тема | Implicit type conversion for json/jsonb |
| Дата | |
| Msg-id | 176182660867.770.7493759479585273126@wrigleys.postgresql.org обсуждение исходный текст |
| Список | pgsql-docs |
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?
В списке pgsql-docs по дате отправления: