Casting

Introduction

In PostgreSQL, whether two Data Types can be cast to each other and how type casting precedence is handled follows a system catalog table named pg_cast (System Catalog), which defines three levels of permission including:

Implicit Cast
- Converted automatically without requiring any extra action
- The condition is that the two types must be truly compatible and not result in data loss
- For example: SMALLINT -> INTEGER -> BIGINT -> NUMERIC
Assignment Cast
- Only automatically casts when executing an INSERT or UPDATE statement into a target column's data type
- For example: INSERTing VARCHAR data into a TEXT column
Explicit Cast (Mandatory manual casting)
- When using syntax to manually cast types such as :: or CAST(x AS type)
- For example: Casting the string '123' or '2026-07-21' to INTEGER or DATE ('123'::integer)

If there is no cast definition between Type A and Type B in pg_cast, casting cannot be performed without an intermediate conversion function.

Type Precedence

Type precedence is a rule/priority table (Weight/Rank) specified by the Database to resolve conflicts or ambiguity when different data types participate in a statement.
The core purpose is to resolve issues when two types meet in calculations or expressions (such as WHERE col_A = val_B), Postgres will choose the type with higher precedence and cast the other side to match it.
If A has higher precedence than B (A > B), then B is cast to A.
The goal is to ensure that the SQL statement always produces a consistent result without syntax conflict errors, regardless of whether the two types belong to the same hierarchy tree.

Type Hierarchy

Data type hierarchy is a hierarchical arrangement, a set system between data types based on their width (value domain) and precision.
In essence, a subtype is a subset or special case of a supertype.
The relationship is A is-a B and every valid value of a lower type can be fully and safely represented in a higher type without any risk of data loss (Lossless).
Postgres groups data types into Categories. Within the same group, the data type containing a wider range of values and higher precision will have higher precedence, as shown below:
Numeric Category
- SMALLINT < INTEGER < BIGINT < NUMERIC < DOUBLE PRECISION
- The rule is that narrower types are always automatically cast up to wider types
- When comparing INTEGER = BIGINT, Postgres casts INTEGER to BIGINT for comparison, never the reverse
String Category
- CHAR < VARCHAR < TEXT
- In Postgres, these 3 types belong to the Binary-compatible group. However, TEXT is the Preferred Type

Detail

Please create a table and seed data as follows:

CREATE TABLE test (
    col_smallint SMALLINT,
    col_integer INTEGER,
    col_bigint BIGINT,
    col_numeric NUMERIC(10, 2),
    col_double DOUBLE PRECISION,

    col_char CHAR(10),
    col_varchar VARCHAR(255),
    col_text TEXT,

    col_boolean BOOLEAN,
    col_jsonb JSONB
);

INSERT INTO test (
    col_smallint,
    col_integer,
    col_bigint,
    col_numeric,
    col_double,
    col_char,
    col_varchar,
    col_text,
    col_boolean,
    col_jsonb
)
SELECT
    (1 + floor(random() * 100))::SMALLINT,
    (1000 + floor(random() * 99000))::INTEGER,
    i::BIGINT,
    round((10 + random() * 4990)::numeric, 2),
    (random() * 100000)::DOUBLE PRECISION,
    (ARRAY['CODE_A', 'CODE_B', 'CODE_C', 'CODE_D'])[floor(1 + random() * 4)],
    'User_' || substr(md5(random()::text), 1, 8),
    'Description ' || i || ': ' || md5(i::text),
    (random() > 0.5),
    jsonb_build_object(
        'user_id', i,
        'is_active', (random() > 0.3),
        'score', floor(random() * 100),
        'tag', (ARRAY['tag1', 'tag2', 'tag3'])[floor(1 + random() * 3)]
    )
FROM generate_series(1, 10000) AS i;

In Postgres, Type Hierarchy forms the foundation for Type Precedence, but Type Precedence is the final rule used to execute queries. This entire logic lies within Postgres's Type Categories mechanism.
If you want to know exactly how Postgres handles type casting between any two data types, you can query directly into the system table pg_cast as follows:

SELECT 
    castsource::regtype AS source_type,
    casttarget::regtype AS target_type,
    CASE castcontext
        WHEN 'i' THEN 'Implicit'
        WHEN 'a' THEN 'Assignment'
        WHEN 'e' THEN 'Explicit'
    END AS cast_type
FROM pg_cast

"smallint" "integer" "Implicit"
"bigint" "smallint" "Assignment"
"integer" "boolean" "Explicit"

For example, a result with these data rows means:

Converting smallint to integer is Implicit, because smallint has a smaller size than integer, so converting to integer is always safe and Postgres will automatically perform it
Converting bigint to smallint is Assignment, because bigint has a larger size than smallint, so this type casting might cause memory overflow errors, meaning the user must perform it manually and may receive a corresponding error if it exceeds the limit supported by that data type
Converting integer to boolean is Explicit, because these are 2 different data types, Postgres cannot automatically convert between them, requiring the user to explicitly perform it manually

Next, execute these queries to test the casting behavior as follows:

-- Query 1
select * from test where col_numeric = col_double

-- Query 2
SELECT 
    10::smallint + 100::integer AS result,
    pg_typeof(10::smallint + 100::integer) AS data_type;

-- Query 3
INSERT INTO test (col_smallint) VALUES (500::bigint);
INSERT INTO test (col_smallint) VALUES (1000000::bigint);

-- Query 4
INSERT INTO test (col_boolean) VALUES (1);
INSERT INTO test (col_boolean) VALUES (1::boolean);
INSERT INTO test (col_boolean) VALUES (0::boolean);

-- Query 5
SELECT * FROM test WHERE col_char = col_text OR col_varchar = col_text

-- Query 6
CREATE INDEX idx_test_col_numeric ON test(col_numeric);
SELECT * FROM test WHERE col_numeric = 416.96
SELECT * FROM test WHERE col_numeric = CAST('416.96' AS NUMERIC)
SELECT * FROM test WHERE col_numeric = '416.96'
SELECT * FROM test WHERE col_numeric::text = '416.96'

-- Query 7
SELECT * FROM test WHERE col_jsonb ->> 'score' > 50.0;
SELECT * FROM test WHERE (col_jsonb ->> 'score')::numeric > 50.0;
SELECT 
    col_jsonb ->> 'score' AS score_text,
    (col_jsonb ->> 'score')::numeric AS score_numeric
FROM test

Query 1: This is Implicit, automatically converting numeric to double precision

Query 2: This is Implicit, automatically converting smallint to integer

Query 3: This is Assignment, you can INSERT bigint into a column with data type smallint, but be careful because if it exceeds the limit, an out of range error will occur

Query 4: This is Explicit, holding boolean and integer types that are unrelated to each other, so Postgres cannot automatically cast them, requiring you to manually do it

Query 5: This is Implicit, text is preferred type, so both char and varchar are automatically converted to text for comparison

Query 6: First, add an Index to col_numeric to check Implicit and Explicit cases as follows:

col_numeric = 416.96: Can use Index
col_numeric = CAST('416.96' AS NUMERIC): Uses CAST function to convert text to numeric, still uses Index
col_numeric = '416.96': Automatically casts text to numeric, still uses Index
col_numeric::text = '416.96': If you actively cast the column to text, it will invalidate the Index because we only created an Index for numeric, not for text

Query 7: These are queries related to JSONB:

col_jsonb ->> 'score' > 50.0: When taking a field inside a JSON Object to compare, its data type is text, regardless of whether you previously INSERTed a numeric type, so it cannot be compared
(col_jsonb ->> 'score')::numeric > 50.0: When performing casting to numeric, it compares normally
If you only extract the value there is no difference, but the data type is clearly different