Consistency and Data Type Behavior

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 ...