Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge about "PostgreSQL's data type conversion rules". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. Value Storage

Values inserted into a data table are converted to the target column data type as follows.

Value Storage Type Conversion

1. Check for exact match with target

2. If not, the expression is converted to the target type. This is possible if there are two types of conversion information in the pg_cast system directory. Otherwise, if the expression is unknown, the contents of the literal string are fed back to the input conversion routine for conversion to the target type.

3. Checks whether the target type has a sizing cast. A sizing cast is a cast from that type to itself. If a cast is found in pg_cast, apply the cast to the expression before storing it in the target column. Implementing a function typically requires an additional parameter of type integer to receive the atttypmod attribute value of the target column (which is usually used to indicate length), and a third parameter of type boolean to determine whether cast is an explicit or implicit conversion. Conversion functions are responsible for performing all length-dependent semantics such as size checking or truncation.

Here are some examples:

character character type conversion

testdb=# CREATE TABLE vv (v character(20));CREATE TABLEtestdb=# INSERT INTO vv SELECT 'abc' || 'def';INSERT 0 1testdb=# SELECT v, octet_length(v) FROM vv; v | octet_length ----------------------+-------------- abcdef | 20(1 row)

Both types 'abc' and 'def' are treated as unknown, converted to text type to perform join operations,|| The result of the operation is text, which is converted to bpchar(blank-padded char, internal name of character) to match the target column type.

View pg_cast system directory

testdb=# select oid,typname from pg_type where typname in ('text','bpchar','char'); oid | typname ------+--------- 18 | char 25 | text 1042 | bpchar(3 rows)testdb=# select * from pg_cast where castsource=25; oid | castsource | casttarget | castfunc | castcontext | castmethod -------+------------+------------+----------+-------------+------------ 11381 | 25 | 2205 | 1079 | i | f 11397 | 25 | 1042 | 0 | i | b 11398 | 25 | 1043 | 0 | i | b 11409 | 25 | 18 | 944 | a | f 11412 | 25 | 19 | 407 | i | f 11466 | 25 | 142 | 2896 | e | f(6 rows)

conversion function

testdb=# select oid,proname,prorettype,proargtypes,prosrc from pg_proc where oid in (0,407,944,1079,2896); oid | proname | prorettype | proargtypes | prosrc ------+----------+------------+-------------+--------------- 407 | name | 19 | 25 | text_name 944 | char | 18 | 25 | text_char 1079 | regclass | 2205 | 25 | text_regclass 2896 | xml | 142 | 25 | texttoxml(4 rows) II. UNION, CASE, and related constructs

UNION(INTERSECT/EXCEPT) must match possible dissimilar types to form a single result set. Case, ARRAY, VALUES, GREATEST, and LEAST use the same algorithm to match expressions and select the resulting data type.

Type Resolution for UNION, CASE, and Related Constructs

1. If all inputs are of the same type and not unknown, resolve to that type

2. If all inputs are of the same domain type, the subsequent steps treat the type as the base type of the domain.

3. If all input types are unknown, it resolves to text(the preferred type for strings). Otherwise,unknown type input is ignored next

4. Failure if non-unknown types are not the same type

5. Select the preferred type of the first unknown type

6. Otherwise, select the last unknown input type that allows all preferred unknown inputs to be converted by privacy

7. Convert all inputs to the selected type, if there are types that cannot be converted, fail

Here are some examples:

Simple Union

testdb=# SELECT 1.2 AS "numeric" UNION SELECT 1; numeric --------- 1 1.2(2 rows)

1.2 explicitly numeric type,integer 1 can be implicitly converted to numeric, therefore numeric type is used

Transposed Union

testdb=# SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL); real ------ 1 2.2(2 rows)

Real numbers cannot be implicitly converted to integers, but integers can be implicitly converted to real, so 1 is converted to real,union results in type real.

Nested Union

testdb=# SELECT NULL UNION SELECT NULL UNION SELECT 1;psql: ERROR: UNION types text and integer cannot be matchedLINE 1: SELECT NULL UNION SELECT NULL UNION SELECT 1;

PG treats multiple Unions as a pair of operations, and the SQL can be viewed as "(SELECT NULL UNION SELECT NULL) UNION SELECT 1." According to the above rules, the inner union will resolve to text type, and the input of the outer union will be text and integer type, resulting in the above error.

"PostgreSQL data type conversion rules are what" content introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.