Shulou(Shulou.com)06/01 Report--

There are differences in data comparison between PostgreSQL and Oracle. This section briefly introduces the data type conversion rules of PostgreSQL.

I. Overview

PostgreSQL has a stronger and more flexible extension type system than other databases. The previously introduced PG lexical and parsing scanner / analyzer splits lexical elements into five types: integers (integer), non-integer numbers (non-integer number), strings (string), identifiers (identifier), and key words (keyword). Most non-numeric types are first classified as strings. In SQL, you can put the parser on the correct parsing path by specifying the type name.

Such as:

Testdb=# SELECT text 'Origin' AS "label", point' (0mem0)'AS "value"; label | value-+-Origin | (0mem0) (1 row)

The above SQL has two literal (literal) constants of type text and point. For string literals (literal), if no type is specified, the type of the placeholder (placeholder) will be considered as unknown, and the specific type will be determined in the subsequent parsing phase.

In PG's parser, there are four basic SQL structures that require unique type conversion rules, which are:

Function calls

PostgreSQL supports function overloading, and the function name is not unique, so it is required to determine the corresponding function based on the type of parameters provided.

Operators

PostgreSQL allows unary-binary operators. Like functions, operators support overloading and need to determine the corresponding operator based on the type of parameters provided.

Value Storage

The expression in the INSERT&UPDATE statement must be consistent with or convertible to the target column type.

UNION, CASE, and related constructs

The results of the UNION type must match and can be converted into a unified collection. Case and other related structures are similar.

The system catalog stores information about how to convert between data types and how to perform these conversions, which can be customized through CREATE CASE.

Data types are divided into several levels of type catalogs (categories), including boolean, numeric, string, bitstring, datetime, timespan, geometric, network, and user-defined types. Within each directory, there are one or more preferred types, and careful selection of the preferred type and the available implicit casting ensures that expression ambiguity is handled in a useful way.

All conversion rules follow the following principles:

1. Implicit conversion should never produce unpredictable results

two。 There should be no additional analysis or execution load when implicit conversion is not required

3. A function in a query statement requires implicit conversion. If the user defines a function that does not require implicit conversion, the parser should use the new function instead of the old function.

II. Operators

The specific operator referenced by the operator expression is determined using the following procedure, and note that the process accepts the influence of the relevant operator precedence, as this determines which subexpression will be regarded as the input to that operator.

Operator Type Resolution

1. Select which operators in the pg_operator system directory. In general, if the operator does not specify schema, an operator with a matching number of name & parameters is selected in the current search path, otherwise the operator that specifies schema is selected.

a. If there are multiple operators of the same parameter type in the search path, choose the one that appears first. Operators with different parameter types are considered equal no matter how the search path is set.

two。 Check whether the operator accepts the input parameter type. Use this operator if it exists.

a. If one parameter in the binary operator is of type unknown, it is assumed to be the same as the other parameter type in this check. In this step, if one or both parameters of a / binary operator are of type unknown, no matching operator will be found.

b. If one parameter called by the binary operator is unknown and the other is a domain type, then check to see if there is an operator that accepts the primitive type of the field on both sides.

3. Looking for the best match

a. Discards operators that do not match input parameter types and cannot be converted (implicitly). Invalid literals assumes that it can be converted to any type. If there is only one candidate operator left, it ends, otherwise proceed to the next step.

b. If one of the input parameters is a domain type, treat this parameter as the domain base type for the next step. Make sure that the field is consistent with the domain base type to disambiguate.

c. Iterate through all candidates, leaving only those operators that match exactly on the input type. If there is no exact match, all candidates are retained. If there is only one left, use this operator, otherwise the next step.

d. Iterate through all candidates and retain those that require type conversion and accept the preferred type in most places. If there is no exact match, all candidates are retained. If there is only one subsequent, use this operator, otherwise the next step.

e. If all input parameter types are unknown, use the remaining candidates to check the type catalog that accepts these parameters at these parameter locations. At each location, if all candidates accept the directory, select the string directory. Otherwise, select this directory if the remaining index candidates accept the same type catalog; otherwise, it will fail because the correct option cannot be specified. Now discard candidates that do not accept directories of the selected type. Further, if all candidates accept the preferred type in the directory, discard candidates that accept that parameter is not the preferred type. If there is no candidate left after this check, keep all the candidates, if there is only one left, use this, otherwise continue to the next step

f. If both unknown and known parameter types exist, and all known types are of the same type, assume that the unknown parameter is also of this type, check which candidate is acceptable in the unknown parameter. If there is such a candidate, use it, otherwise it fails.

A few examples:

Factorial

Testdb=# SELECT 40! AS "40 factorial"; 40 factorial-8159152832478977343456112695961158942720000000 (1 row)

Unary operator "!" The parameter type defined in the standard directory is bigint, and the scanner treats the type of input parameter as integer, so convert parameter 40 to bigint.

String concatenation

Testdb=# SELECT text 'abc' | |' def' AS "text and unknown"; text and unknown-abcdef (1 row)

If there is text on one side and unknown on the other, the second parameter, 'def', is assumed to be of type text.

Testdb=# SELECT 'abc' | |' def' AS "unspecified"; unspecified-abcdef (1 row)

In this case, there are unknown on both sides, and the parser looks for all candidate operators and finds that the candidate operator accepts (bit-) string-category input at the same time. Since the string is preferred, the category is selected, and the analogical preferred type, text, is used as a specific type to parse the parameters of the unknown type.

Inverse operation of absolute value and bit

Testdb=# SELECT @'- 4.5' AS "abs"; abs-4.5 (1 row)

The absolute value operation can accept multiple input types, and float8 is the preferred type in the numeric category, so PG uses this entry to handle unknown parameter types.

Before applying the selected operator, the system implicitly treats the literal amount of unknown type as float8, so the system verifies that the input is of float8 type, and an error is reported if it does not match.

Testdb=# SELECT @'- 4.5e500' AS "abs"; psql: ERROR: "- 4.5e500" is out of range for type double precisionLINE 1: SELECT @'- 4.5e500' AS "abs"; ^

On the other hand, taking the inverse operator ~ only accepts the integer type as a parameter. If the parameter type is unknown, it will be regarded as float8, and the execution will make an error:

Testdb=# SELECT ~ '20' AS "negation"; psql: ERROR: operator is not unique: ~ unknownLINE 1: SELECT ~' 20' AS "negation"; ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts.

Function/Value Storage/UNION, CASE, and related constructs will be introduced in the next section.

III. Reference materials

PostgreSQL Type Conversion

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.