Consistency and Data Type Behavior
Explore how PostgreSQL implements dynamic data type behavior and operator dispatch to manage query consistency. Learn about PostgreSQL catalogs, index support for data types, and how serialization affects data handling. This lesson deepens your understanding of data type interactions and indexing mechanisms for smarter database use.
Data type behavior
A key aspect of PostgreSQL data types lies in their behavior. Comparable to an object-oriented system, PostgreSQL implements functions and operator polymorphism, allowing for the dispatching of code at runtime depending on the types of arguments.
The data type of a literal
If we take a closer look at a very simple SQL query, we can see lots happening under the hood:
In this query, the expression driverid = 1 uses the = operator between a column name and a literal value. PostgreSQL knows from its catalogs that the driverid column is a bigint and parses the literal 1 as an integer. We can check that with the following query:
It would give the following output:
pg_typeof │ pg_typeof
═══════════╪═══════════
bigint │ integer
(1 row)
Now, how does PostgreSQL implement = in between an 8-bytes integer and a 4-bytes integer? It turns out this decision is dynamic: the operator = dispatches to an established function depending on the types of its left and right operands. We can even have a look at the PostgreSQL catalogs to get a better grasp of this notion.
PostgreSQL catalogs
Let’s have a look at the example to understand the PostgreSQL catalogs.
This ...