Type Checking — Parsing and Validation | OneNoughtOne

0/131

Database Management SystemsQuery Processing

Parsing and Validation

LevelIntermediate

Duration75 mins

TopicQuery Processing

4 / 5

Type Checking

The Type Safety Guardian

Consider this innocent-looking query: SELECT * FROM employees WHERE salary > 'fifty thousand' The syntax is perfect. The table exists. The column exists. Yet this query will likely fail—because you cannot compare a numeric salary to a text string using a greater-than operator. This is a type error, and detecting it is the job of the type checking phase. Type checking is the semantic analysis phase that ensures all expressions in your query operate on compatible data types. It verifies that: Comparisons are between comparable types Arithmetic operations receive numeric operands Functions receive arguments of expected types Assignments match target column types Without type checking, queries would fail at execution time with cryptic errors—or worse, produce silently incorrect results by interpreting data in unintended ways.

What You Will Learn

By the end of this page, you will understand SQL's type system, how databases verify type compatibility, when and how implicit type conversions (coercions) occur, and how to diagnose and fix type-related query errors. You'll also learn how type information flows through expressions and what makes some type systems stricter than others.

The SQL Type System

SQL has a rich type system that has evolved over decades. Understanding the type hierarchy is essential for understanding type checking behavior. Core SQL Data Types: The SQL standard defines several type categories:

SQL Data Type Categories
Category	Types	Examples
Numeric	INTEGER, SMALLINT, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION	`42`, `3.14159`, `-1000000000000`
Character	CHAR, VARCHAR, TEXT, CLOB	`'Hello'`, `'SQL is fun'`
Binary	BINARY, VARBINARY, BLOB, BYTEA	`\x48656C6C6F`, binary files
Temporal	DATE, TIME, TIMESTAMP, INTERVAL	`'2024-01-15'`, `'14:30:00'`
Boolean	BOOLEAN	`TRUE`, `FALSE`, `NULL`
UUID	UUID (extension)	`'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'`
JSON	JSON, JSONB (extension)	`'{"key": "value"}'`
Array	Type[] (extension)	`'{1,2,3}'`, `ARRAY['a','b']`
Composite	User-defined types, ROW	`ROW(1, 'value', TRUE)`

Type Properties: Each type has associated properties that affect type checking: Precision/Scale : For NUMERIC types, how many digits total and after decimal Length : For character types, maximum character count Nullability : Whether NULL values are permitted Collation : For text types, how comparisons and sorting work Time Zone : For temporal types, whether timezone-aware Type checking must consider these properties. For example, assigning a VARCHAR(100) value to a VARCHAR(50) column may require truncation or raise an error. Type Compatibility: Types are compatible if they can be compared, combined, or assigned. The rules vary: Same type \to Always compatible Same category \to Usually compatible (INT and BIGINT) Different category \to Rarely compatible without explicit cast

Converting Mermaid diagram...

Type Inference for Expressions

Every expression in SQL has a type, either explicit or inferred. The type checker must determine the type of each expression node in the parse tree. Type Inference Rules: Literals: Integer literals (42) \to INTEGER or BIGINT depending on size Decimal literals (3.14) \to NUMERIC String literals ('hello') \to Unknown/varchar until context determines Boolean literals (TRUE) \to BOOLEAN NULL \to Unknown type until context determines Column References: Type is retrieved from the catalog (table definition) Already known from name resolution phase Operators: Type determined by operator definition and operand types Many operators are overloaded for different type combinations

type-inference-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Type inference examples
 
-- Literal type inference
SELECT 
    42,                    -- integer (small enough for INT)
    9999999999999999999,   -- bigint (too large for INT)
    3.14159,              -- numeric
    'hello',              -- text/varchar/unknown
    TRUE,                 -- boolean
    NULL;                 -- unknown (determined by context)
 
-- Operator result type
SELECT 
    10 + 5,               -- integer (int + int)
    10 + 5.0,             -- numeric (int + numeric → numeric)
    10 / 3,               -- integer division = 3
    10.0 / 3,             -- numeric division = 3.333...
    'Hello' || ' World',  -- text concatenation
    CURRENT_DATE + 7;     -- date + integer = date
 
-- Function return type
SELECT
    LENGTH('hello'),       -- integer
    UPPER('hello'),        -- text
    NOW(),                 -- timestamp with time zone
    ARRAY_LENGTH('{1,2,3}'::int[], 1);  -- integer
 
-- Expression composition
SELECT 
    (salary * 12) / 52 AS weekly_from_yearly,
    -- salary: numeric → *12: numeric → /52: numeric
    
    hire_date + (years_employed * INTERVAL '1 year') AS projected_date
    -- hire_date: date → +interval: timestamp
FROM employees;

Type Propagation: Types propagate through complex expressions. The type checker walks the AST bottom-up: Assign types to leaf nodes (literals, column refs) For each operator/function node, check operand types Determine result type based on operand types Propagate result type upward Example Type Walk: SELECT salary * 1.1 + bonus FROM employees AST with types: [+] : numeric \leftarrow result of numeric + numeric / [*] bonus:numeric / salary:numeric 1.1:numeric

Unknown Type Resolution

String literals and NULL initially have an 'unknown' type that gets resolved by context. In WHERE name = 'John', the literal 'John' resolves to the type of name . In SELECT COALESCE(NULL, 0), NULL resolves to INTEGER because 0 is an integer. This deferred resolution allows flexible literal usage.

Implicit Type Coercion

When types don't match exactly, databases often perform implicit type coercion (also called implicit casting or type promotion). This allows expressions like salary + 100 to work even if salary is DECIMAL and 100 is INTEGER. Coercion Rules: Databases define a hierarchy of which types can implicitly convert to others: Less Precise ──────────────────────\to More Precise SMALLINT \to INTEGER \to BIGINT \to NUMERIC \to FLOAT Narrower ──────────────────────────\to Wider CHAR(10) \to VARCHAR(10) \to VARCHAR(100) \to TEXT Less Information ──────────────────\to More Information DATE \to TIMESTAMP \to TIMESTAMP WITH TIME ZONE Implicit coercion typically goes from less precise to more precise (avoiding data loss).

implicit-coercion.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Safe implicit coercions (no data loss)
 
-- Integer to numeric (widening)
SELECT 10 + 3.5;  -- 10 becomes 10.0, result is 13.5
 
-- Integer to float
SELECT 10 / 3.0;  -- 10 becomes 10.0, result is 3.333...
 
-- Char to varchar
SELECT 'ab'::CHAR(2) = 'ab'::VARCHAR;  -- Works
 
-- Date to timestamp  
SELECT '2024-01-15'::DATE + INTERVAL '1 hour';
-- Date promoted to timestamp for interval arithmetic
 
-- Dangerous implicit coercions (potential data loss)
 
-- Float to integer in some contexts
INSERT INTO int_table (value) VALUES (3.99);
-- Some DBs: truncates to 3  (data loss!)
-- Some DBs: rounds to 4
-- Best DBs: error without explicit cast
 
-- Long string to short column
INSERT INTO users (code) VALUES ('ABCDEFGHIJ');  -- code is VARCHAR(5)
-- Some DBs: silently truncates to 'ABCDE' (data loss!)
-- Better DBs: error
 
-- String to number
SELECT * FROM orders WHERE order_id = '12345';
-- order_id is INTEGER, '12345' coerced to integer 12345
-- Works but hides potential index usage issues

Coercion Categories in PostgreSQL: PostgreSQL classifies type conversions into categories: Implicit : Automatic, always applied (integer \to bigint) Assignment : Applied for INSERT/UPDATE target columns Explicit : Requires CAST or :: operator only This fine-grained control prevents unexpected conversions while allowing convenience.

Common Implicit Coercion Behaviors
From Type	To Type	Behavior	Safe?
INTEGER	BIGINT	Always allowed	✓ Yes
INTEGER	NUMERIC	Always allowed	✓ Yes
FLOAT	INTEGER	DB-dependent (often assignment only)	✗ Data loss
VARCHAR(100)	VARCHAR(50)	May truncate	✗ Data loss
TEXT	INTEGER	Usually requires explicit cast	✗ May fail
DATE	TIMESTAMP	Adds midnight time	✓ Yes
TIMESTAMP	DATE	Assignment only, loses time	✗ Data loss

Implicit Coercion Dangers

Implicit coercion can hide bugs. If a column type changes from INTEGER to VARCHAR, queries comparing with integers might silently start doing VARCHAR comparisons (where '9' > '10' lexicographically). Always be explicit with casts when type conversion is intentional.

Type Checking in Different Contexts

Type checking applies throughout the query with context-specific rules. Let's examine type requirements in different SQL clauses. WHERE Clause: The WHERE clause must evaluate to a boolean (or NULL): -- Correct: produces boolean WHERE salary > 50000 WHERE status = 'active' AND hire_date > '2020-01-01' -- Error: not boolean WHERE salary -- Just a number, not a boolean WHERE 'active' -- Just a string Comparison Operators: Both operands must be of comparable types: -- Same types: always works WHERE salary = 50000 -- numeric = numeric WHERE name = 'John' -- text = text -- Compatible types: works with coercion WHERE int_column = 100.0 -- int comparable to numeric -- Incompatible types: error WHERE date_column = 12345 -- date not comparable to int

context-type-checking.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- SELECT list: any expression type allowed
SELECT 
    42,                        -- integer
    'text',                    -- text
    TRUE,                      -- boolean
    ARRAY[1,2,3],             -- integer array
    ROW(1, 'a');              -- composite
 
-- INSERT: must match column types
INSERT INTO employees (id, name, salary, hire_date)
VALUES (
    1,                         -- must be compatible with id type
    'John Doe',               -- must be compatible with name type
    75000.00,                 -- must be compatible with salary type
    '2024-01-15'              -- must be compatible with hire_date type
);
 
-- UPDATE: SET expressions must match column types
UPDATE employees
SET salary = salary * 1.1,    -- salary type = numeric expression ✓
    name = 'Updated',         -- name type = text ✓
    hire_date = CURRENT_DATE; -- hire_date type = date ✓
 
-- UNION: corresponding columns must be compatible
SELECT id, name, salary FROM employees
UNION
SELECT id, name, hourly_rate * 2080 FROM contractors;
-- Column 3: salary (numeric) must be compatible with expression (numeric) ✓
 
-- GROUP BY: grouping columns must support equality comparison
GROUP BY department_id, hire_date
-- Both integer and date support equality ✓
 
-- ORDER BY: sorting columns must be orderable
ORDER BY salary DESC, name ASC
-- Both numeric and text support ordering ✓
 
-- CASE expressions: all branches must have compatible types
SELECT CASE status
    WHEN 'A' THEN 'Active'        -- text
    WHEN 'I' THEN 'Inactive'      -- text
    ELSE 'Unknown'                -- text
END AS status_label;              -- All branches are text ✓

Context-Specific Type Rules

•WHERE/HAVING — Must evaluate to BOOLEAN (or NULL)
•JOIN ON — Must evaluate to BOOLEAN for join condition
•INSERT/UPDATE — Values must be assignable to target column types
•CASE branches — All result expressions must have a common supertype
•COALESCE/NULLIF — All arguments must have a common supertype
•UNION/INTERSECT/EXCEPT — Corresponding columns must be compatible
•IN list — All list elements must be compatible with operand
•BETWEEN — All three expressions must be mutually comparable

Function Type Signatures and Overloading

Functions have type signatures that define their parameter types and return type. Type checking verifies that function calls provide arguments matching an available signature. Function Signature Components: function_name(param1_type, param2_type, ...) \to return_type SUBSTRING(text, integer, integer) \to text ROUND(numeric, integer) \to numeric COALESCE(anytype, anytype, ...) \to anytype Overloading: Many SQL functions are overloaded—multiple signatures with the same name: -- PostgreSQL ROUND overloads: ROUND(DOUBLE PRECISION) \to DOUBLE PRECISION ROUND(NUMERIC) \to NUMERIC ROUND(NUMERIC, INTEGER) \to NUMERIC The type checker must select the best matching overload.

function-type-checking.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Function overload resolution
 
-- Exact match preferred
SELECT ROUND(3.14159);           -- Uses ROUND(numeric) → 3
 
-- Argument count selects overload
SELECT ROUND(3.14159, 2);        -- Uses ROUND(numeric, int) → 3.14
 
-- Type coercion to find match
SELECT ROUND(3);                 -- 3 is integer, no ROUND(int)
                                 -- Coerced to numeric, uses ROUND(numeric)
 
-- Ambiguous overload error
CREATE FUNCTION foo(INTEGER) RETURNS INTEGER AS $$ SELECT $1; $$ LANGUAGE SQL;
CREATE FUNCTION foo(BIGINT) RETURNS BIGINT AS $$ SELECT $1; $$ LANGUAGE SQL;
SELECT foo(42);  
-- 42 fits both INTEGER and BIGINT equally well
-- ERROR: function foo(integer) is ambiguous
 
-- Resolve with explicit cast
SELECT foo(42::INTEGER);   -- Now clearly calls foo(INTEGER)
SELECT foo(42::BIGINT);    -- Now clearly calls foo(BIGINT)
 
-- Polymorphic functions
-- COALESCE accepts any type but all must be compatible
SELECT COALESCE(null, 0);           -- Returns integer
SELECT COALESCE(null, 'default');   -- Returns text
SELECT COALESCE(null, 0, 'text');   -- Error: incompatible types

Overload Resolution Algorithm: Find all functions with matching name Filter to those with correct argument count (considering defaults/variadic) Filter to those where arguments are type-compatible Calculate "distance" based on coercion requirements Select lowest-distance match, or error if ambiguous Type Distance Examples: Exact match: distance 0 Binary coercible (same category): low distance Implicit coercion: medium distance Requires explicit cast: infinite (won't auto-select)

Avoiding Overload Ambiguity

When overload resolution fails with 'ambiguous function call', add explicit casts to your arguments. This both fixes the error and documents your intent. For example: SUM(column::NUMERIC) clearly specifies which SUM overload you want.

Diagnosing Common Type Errors

Type errors are among the most common query failures. Let's examine typical type errors and how to diagnose and fix them. Error Category 1: Direct Type Incompatibility

type-incompatibility.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Error: Comparing incompatible types
SELECT * FROM orders WHERE order_date = 12345;
-- ERROR: operator does not exist: date = integer
 
-- Fix: Use appropriate literal type
SELECT * FROM orders WHERE order_date = '2024-01-15';
SELECT * FROM orders WHERE order_date = DATE '2024-01-15';
 
-- Error: Arithmetic on non-numeric type
SELECT name + 10 FROM employees;
-- ERROR: operator does not exist: character varying + integer
 
-- Fix: Use appropriate operation
SELECT name || ' (ID: ' || employee_id || ')' FROM employees;
 
-- Error: Boolean expression expected
SELECT * FROM employees WHERE salary;
-- ERROR: argument of WHERE must be type boolean
 
-- Fix: Create boolean expression
SELECT * FROM employees WHERE salary > 0;
SELECT * FROM employees WHERE salary IS NOT NULL;

Error Category 2: Function Type Mismatch

function-type-mismatch.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Error: Function doesn't exist for argument types
SELECT LENGTH(12345);
-- ERROR: function length(integer) does not exist
-- HINT: No function matches the given name and argument types.
 
-- Fix: Cast to appropriate type
SELECT LENGTH(12345::TEXT);   -- Returns 5
 
-- Error: String function on non-string
SELECT UPPER(salary) FROM employees;
-- ERROR: function upper(numeric) does not exist
 
-- Fix: Cast to string if that's what you want
SELECT UPPER(salary::TEXT) FROM employees;
 
-- Error: Aggregate type mismatch
SELECT SUM(name) FROM employees;
-- ERROR: function sum(character varying) does not exist
 
-- Fix: Use string aggregate if needed
SELECT STRING_AGG(name, ', ') FROM employees;
 
-- Error: Date function on wrong type
SELECT EXTRACT(YEAR FROM employee_id) FROM employees;
-- ERROR: cannot extract year from type integer
 
-- Fix: Use correct column
SELECT EXTRACT(YEAR FROM hire_date) FROM employees;

Error Category 3: Insert/Update Type Mismatch

insert-type-mismatch.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Error: Wrong type for column
INSERT INTO employees (id, hire_date) VALUES (1, 'not-a-date');
-- ERROR: invalid input syntax for type date: "not-a-date"
 
-- Fix: Use valid date format
INSERT INTO employees (id, hire_date) VALUES (1, '2024-01-15');
 
-- Error: Type conversion failure
INSERT INTO products (price) VALUES ('N/A');
-- ERROR: invalid input syntax for type numeric: "N/A"
 
-- Fix: Use NULL for missing numeric
INSERT INTO products (price) VALUES (NULL);
-- Or use CASE in data migration
INSERT INTO products (price)
SELECT CASE WHEN price_str = 'N/A' THEN NULL 
       ELSE price_str::NUMERIC END
FROM import_table;
 
-- Error: Precision overflow
INSERT INTO items (quantity) VALUES (99999999999999999999);
-- ERROR: integer out of range
 
-- Fix: Use appropriate type
ALTER TABLE items ALTER COLUMN quantity TYPE BIGINT;
-- Or check data validity before insert

Type Error Diagnostic Steps

•Read the error message carefully — It usually states exactly which types are incompatible
•Check column definitions — Use \d tablename (psql) or equivalent to verify types
•Verify literal types — Unquoted numbers vs. quoted strings behave very differently
•Consider NULL handling — NULL has no type until context provides one
•Look for implicit conversions — What works in one DB may fail in another
•Add explicit casts — When in doubt, cast explicitly to document intent

Explicit Type Casting

When implicit coercion isn't sufficient (or when you want to be explicit), SQL provides explicit casting syntax. Cast Syntax Variants:

cast-syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- CAST function (ANSI SQL standard)
SELECT CAST(salary AS INTEGER) FROM employees;
SELECT CAST('2024-01-15' AS DATE);
SELECT CAST(123 AS VARCHAR(10));
 
-- Double-colon syntax (PostgreSQL, commonly used)
SELECT salary::INTEGER FROM employees;
SELECT '2024-01-15'::DATE;
SELECT 123::VARCHAR(10);
 
-- Type function syntax (some databases)
SELECT INTEGER(salary) FROM employees;
SELECT DATE('2024-01-15');
 
-- Comparison: All equivalent in PostgreSQL
SELECT CAST(3.14159 AS INTEGER);   -- Standard SQL
SELECT 3.14159::INTEGER;            -- PostgreSQL shorthand
SELECT INTEGER '3.14159';           -- Type literal syntax (constants only)

When to Cast Explicitly: Resolving ambiguity — When overloaded functions match multiple signatures Forcing precision — Ensuring specific numeric precision in calculations Data transformation — Converting between fundamentally different types Documentation — Making type intentions clear for maintenance Portability — Avoiding reliance on DB-specific implicit coercion

explicit-cast-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Numeric precision control
SELECT 10 / 3;                -- Integer division: 3
SELECT 10::NUMERIC / 3;       -- Decimal division: 3.333...
SELECT 10 / 3::NUMERIC;       -- Also decimal: 3.333...
SELECT (10 / 3)::NUMERIC;     -- Still 3 (division already happened)
 
-- String to number conversion
SELECT '123'::INTEGER;         -- 123
SELECT '123.45'::NUMERIC;      -- 123.45
SELECT 'abc'::INTEGER;         -- ERROR: invalid input syntax
 
-- Date/time conversions
SELECT NOW()::DATE;            -- Current date (drops time)
SELECT NOW()::TIME;            -- Current time (drops date)
SELECT '2024-01-15'::TIMESTAMP; -- Midnight on that date
 
-- Complex casting
SELECT ARRAY[1,2,3]::TEXT;     -- '{1,2,3}' (array to text)
SELECT '{1,2,3}'::INTEGER[];   -- {1,2,3} (text to array)
 
-- JSON casting
SELECT '{"key": "value"}'::JSON;
SELECT '{"key": "value"}'::JSONB;
SELECT row_to_json(t) FROM mytable t;  -- Row to JSON
 
-- Casting with NULL handling
SELECT COALESCE(nullable_col::TEXT, 'N/A');
SELECT NULLIF(value::TEXT, '')::INTEGER;  -- Empty string to NULL to int

Cast Failures

Not all casts succeed. Casting 'hello' to INTEGER fails at runtime. Casting a large BIGINT to INTEGER may overflow. Test your casts with edge cases, and consider using TRY_CAST (SQL Server) or error handling where available.

Database Management SystemsQuery Processing

Parsing and Validation

LevelIntermediate

Duration75 mins

TopicQuery Processing

4 / 5

Type Checking

The Type Safety Guardian

Consider this innocent-looking query: SELECT * FROM employees WHERE salary > 'fifty thousand' The syntax is perfect. The table exists. The column exists. Yet this query will likely fail—because you cannot compare a numeric salary to a text string using a greater-than operator. This is a type error, and detecting it is the job of the type checking phase. Type checking is the semantic analysis phase that ensures all expressions in your query operate on compatible data types. It verifies that: Comparisons are between comparable types Arithmetic operations receive numeric operands Functions receive arguments of expected types Assignments match target column types Without type checking, queries would fail at execution time with cryptic errors—or worse, produce silently incorrect results by interpreting data in unintended ways.

What You Will Learn

By the end of this page, you will understand SQL's type system, how databases verify type compatibility, when and how implicit type conversions (coercions) occur, and how to diagnose and fix type-related query errors. You'll also learn how type information flows through expressions and what makes some type systems stricter than others.

The SQL Type System

SQL has a rich type system that has evolved over decades. Understanding the type hierarchy is essential for understanding type checking behavior. Core SQL Data Types: The SQL standard defines several type categories:

SQL Data Type Categories
Category	Types	Examples
Numeric	INTEGER, SMALLINT, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION	`42`, `3.14159`, `-1000000000000`
Character	CHAR, VARCHAR, TEXT, CLOB	`'Hello'`, `'SQL is fun'`
Binary	BINARY, VARBINARY, BLOB, BYTEA	`\x48656C6C6F`, binary files
Temporal	DATE, TIME, TIMESTAMP, INTERVAL	`'2024-01-15'`, `'14:30:00'`
Boolean	BOOLEAN	`TRUE`, `FALSE`, `NULL`
UUID	UUID (extension)	`'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'`
JSON	JSON, JSONB (extension)	`'{"key": "value"}'`
Array	Type[] (extension)	`'{1,2,3}'`, `ARRAY['a','b']`
Composite	User-defined types, ROW	`ROW(1, 'value', TRUE)`

Type Properties: Each type has associated properties that affect type checking: Precision/Scale : For NUMERIC types, how many digits total and after decimal Length : For character types, maximum character count Nullability : Whether NULL values are permitted Collation : For text types, how comparisons and sorting work Time Zone : For temporal types, whether timezone-aware Type checking must consider these properties. For example, assigning a VARCHAR(100) value to a VARCHAR(50) column may require truncation or raise an error. Type Compatibility: Types are compatible if they can be compared, combined, or assigned. The rules vary: Same type \to Always compatible Same category \to Usually compatible (INT and BIGINT) Different category \to Rarely compatible without explicit cast

Converting Mermaid diagram...

Type Inference for Expressions

Every expression in SQL has a type, either explicit or inferred. The type checker must determine the type of each expression node in the parse tree. Type Inference Rules: Literals: Integer literals (42) \to INTEGER or BIGINT depending on size Decimal literals (3.14) \to NUMERIC String literals ('hello') \to Unknown/varchar until context determines Boolean literals (TRUE) \to BOOLEAN NULL \to Unknown type until context determines Column References: Type is retrieved from the catalog (table definition) Already known from name resolution phase Operators: Type determined by operator definition and operand types Many operators are overloaded for different type combinations

type-inference-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Type inference examples
 
-- Literal type inference
SELECT 
    42,                    -- integer (small enough for INT)
    9999999999999999999,   -- bigint (too large for INT)
    3.14159,              -- numeric
    'hello',              -- text/varchar/unknown
    TRUE,                 -- boolean
    NULL;                 -- unknown (determined by context)
 
-- Operator result type
SELECT 
    10 + 5,               -- integer (int + int)
    10 + 5.0,             -- numeric (int + numeric → numeric)
    10 / 3,               -- integer division = 3
    10.0 / 3,             -- numeric division = 3.333...
    'Hello' || ' World',  -- text concatenation
    CURRENT_DATE + 7;     -- date + integer = date
 
-- Function return type
SELECT
    LENGTH('hello'),       -- integer
    UPPER('hello'),        -- text
    NOW(),                 -- timestamp with time zone
    ARRAY_LENGTH('{1,2,3}'::int[], 1);  -- integer
 
-- Expression composition
SELECT 
    (salary * 12) / 52 AS weekly_from_yearly,
    -- salary: numeric → *12: numeric → /52: numeric
    
    hire_date + (years_employed * INTERVAL '1 year') AS projected_date
    -- hire_date: date → +interval: timestamp
FROM employees;

Type Propagation: Types propagate through complex expressions. The type checker walks the AST bottom-up: Assign types to leaf nodes (literals, column refs) For each operator/function node, check operand types Determine result type based on operand types Propagate result type upward Example Type Walk: SELECT salary * 1.1 + bonus FROM employees AST with types: [+] : numeric \leftarrow result of numeric + numeric / [*] bonus:numeric / salary:numeric 1.1:numeric

Unknown Type Resolution

String literals and NULL initially have an 'unknown' type that gets resolved by context. In WHERE name = 'John', the literal 'John' resolves to the type of name . In SELECT COALESCE(NULL, 0), NULL resolves to INTEGER because 0 is an integer. This deferred resolution allows flexible literal usage.

Implicit Type Coercion

When types don't match exactly, databases often perform implicit type coercion (also called implicit casting or type promotion). This allows expressions like salary + 100 to work even if salary is DECIMAL and 100 is INTEGER. Coercion Rules: Databases define a hierarchy of which types can implicitly convert to others: Less Precise ──────────────────────\to More Precise SMALLINT \to INTEGER \to BIGINT \to NUMERIC \to FLOAT Narrower ──────────────────────────\to Wider CHAR(10) \to VARCHAR(10) \to VARCHAR(100) \to TEXT Less Information ──────────────────\to More Information DATE \to TIMESTAMP \to TIMESTAMP WITH TIME ZONE Implicit coercion typically goes from less precise to more precise (avoiding data loss).

implicit-coercion.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Safe implicit coercions (no data loss)
 
-- Integer to numeric (widening)
SELECT 10 + 3.5;  -- 10 becomes 10.0, result is 13.5
 
-- Integer to float
SELECT 10 / 3.0;  -- 10 becomes 10.0, result is 3.333...
 
-- Char to varchar
SELECT 'ab'::CHAR(2) = 'ab'::VARCHAR;  -- Works
 
-- Date to timestamp  
SELECT '2024-01-15'::DATE + INTERVAL '1 hour';
-- Date promoted to timestamp for interval arithmetic
 
-- Dangerous implicit coercions (potential data loss)
 
-- Float to integer in some contexts
INSERT INTO int_table (value) VALUES (3.99);
-- Some DBs: truncates to 3  (data loss!)
-- Some DBs: rounds to 4
-- Best DBs: error without explicit cast
 
-- Long string to short column
INSERT INTO users (code) VALUES ('ABCDEFGHIJ');  -- code is VARCHAR(5)
-- Some DBs: silently truncates to 'ABCDE' (data loss!)
-- Better DBs: error
 
-- String to number
SELECT * FROM orders WHERE order_id = '12345';
-- order_id is INTEGER, '12345' coerced to integer 12345
-- Works but hides potential index usage issues

Coercion Categories in PostgreSQL: PostgreSQL classifies type conversions into categories: Implicit : Automatic, always applied (integer \to bigint) Assignment : Applied for INSERT/UPDATE target columns Explicit : Requires CAST or :: operator only This fine-grained control prevents unexpected conversions while allowing convenience.

Common Implicit Coercion Behaviors
From Type	To Type	Behavior	Safe?
INTEGER	BIGINT	Always allowed	✓ Yes
INTEGER	NUMERIC	Always allowed	✓ Yes
FLOAT	INTEGER	DB-dependent (often assignment only)	✗ Data loss
VARCHAR(100)	VARCHAR(50)	May truncate	✗ Data loss
TEXT	INTEGER	Usually requires explicit cast	✗ May fail
DATE	TIMESTAMP	Adds midnight time	✓ Yes
TIMESTAMP	DATE	Assignment only, loses time	✗ Data loss

Implicit Coercion Dangers

Implicit coercion can hide bugs. If a column type changes from INTEGER to VARCHAR, queries comparing with integers might silently start doing VARCHAR comparisons (where '9' > '10' lexicographically). Always be explicit with casts when type conversion is intentional.

Type Checking in Different Contexts

Type checking applies throughout the query with context-specific rules. Let's examine type requirements in different SQL clauses. WHERE Clause: The WHERE clause must evaluate to a boolean (or NULL): -- Correct: produces boolean WHERE salary > 50000 WHERE status = 'active' AND hire_date > '2020-01-01' -- Error: not boolean WHERE salary -- Just a number, not a boolean WHERE 'active' -- Just a string Comparison Operators: Both operands must be of comparable types: -- Same types: always works WHERE salary = 50000 -- numeric = numeric WHERE name = 'John' -- text = text -- Compatible types: works with coercion WHERE int_column = 100.0 -- int comparable to numeric -- Incompatible types: error WHERE date_column = 12345 -- date not comparable to int

context-type-checking.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- SELECT list: any expression type allowed
SELECT 
    42,                        -- integer
    'text',                    -- text
    TRUE,                      -- boolean
    ARRAY[1,2,3],             -- integer array
    ROW(1, 'a');              -- composite
 
-- INSERT: must match column types
INSERT INTO employees (id, name, salary, hire_date)
VALUES (
    1,                         -- must be compatible with id type
    'John Doe',               -- must be compatible with name type
    75000.00,                 -- must be compatible with salary type
    '2024-01-15'              -- must be compatible with hire_date type
);
 
-- UPDATE: SET expressions must match column types
UPDATE employees
SET salary = salary * 1.1,    -- salary type = numeric expression ✓
    name = 'Updated',         -- name type = text ✓
    hire_date = CURRENT_DATE; -- hire_date type = date ✓
 
-- UNION: corresponding columns must be compatible
SELECT id, name, salary FROM employees
UNION
SELECT id, name, hourly_rate * 2080 FROM contractors;
-- Column 3: salary (numeric) must be compatible with expression (numeric) ✓
 
-- GROUP BY: grouping columns must support equality comparison
GROUP BY department_id, hire_date
-- Both integer and date support equality ✓
 
-- ORDER BY: sorting columns must be orderable
ORDER BY salary DESC, name ASC
-- Both numeric and text support ordering ✓
 
-- CASE expressions: all branches must have compatible types
SELECT CASE status
    WHEN 'A' THEN 'Active'        -- text
    WHEN 'I' THEN 'Inactive'      -- text
    ELSE 'Unknown'                -- text
END AS status_label;              -- All branches are text ✓

Context-Specific Type Rules

•WHERE/HAVING — Must evaluate to BOOLEAN (or NULL)
•JOIN ON — Must evaluate to BOOLEAN for join condition
•INSERT/UPDATE — Values must be assignable to target column types
•CASE branches — All result expressions must have a common supertype
•COALESCE/NULLIF — All arguments must have a common supertype
•UNION/INTERSECT/EXCEPT — Corresponding columns must be compatible
•IN list — All list elements must be compatible with operand
•BETWEEN — All three expressions must be mutually comparable

Function Type Signatures and Overloading

Functions have type signatures that define their parameter types and return type. Type checking verifies that function calls provide arguments matching an available signature. Function Signature Components: function_name(param1_type, param2_type, ...) \to return_type SUBSTRING(text, integer, integer) \to text ROUND(numeric, integer) \to numeric COALESCE(anytype, anytype, ...) \to anytype Overloading: Many SQL functions are overloaded—multiple signatures with the same name: -- PostgreSQL ROUND overloads: ROUND(DOUBLE PRECISION) \to DOUBLE PRECISION ROUND(NUMERIC) \to NUMERIC ROUND(NUMERIC, INTEGER) \to NUMERIC The type checker must select the best matching overload.

function-type-checking.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Function overload resolution
 
-- Exact match preferred
SELECT ROUND(3.14159);           -- Uses ROUND(numeric) → 3
 
-- Argument count selects overload
SELECT ROUND(3.14159, 2);        -- Uses ROUND(numeric, int) → 3.14
 
-- Type coercion to find match
SELECT ROUND(3);                 -- 3 is integer, no ROUND(int)
                                 -- Coerced to numeric, uses ROUND(numeric)
 
-- Ambiguous overload error
CREATE FUNCTION foo(INTEGER) RETURNS INTEGER AS $$ SELECT $1; $$ LANGUAGE SQL;
CREATE FUNCTION foo(BIGINT) RETURNS BIGINT AS $$ SELECT $1; $$ LANGUAGE SQL;
SELECT foo(42);  
-- 42 fits both INTEGER and BIGINT equally well
-- ERROR: function foo(integer) is ambiguous
 
-- Resolve with explicit cast
SELECT foo(42::INTEGER);   -- Now clearly calls foo(INTEGER)
SELECT foo(42::BIGINT);    -- Now clearly calls foo(BIGINT)
 
-- Polymorphic functions
-- COALESCE accepts any type but all must be compatible
SELECT COALESCE(null, 0);           -- Returns integer
SELECT COALESCE(null, 'default');   -- Returns text
SELECT COALESCE(null, 0, 'text');   -- Error: incompatible types

Overload Resolution Algorithm: Find all functions with matching name Filter to those with correct argument count (considering defaults/variadic) Filter to those where arguments are type-compatible Calculate "distance" based on coercion requirements Select lowest-distance match, or error if ambiguous Type Distance Examples: Exact match: distance 0 Binary coercible (same category): low distance Implicit coercion: medium distance Requires explicit cast: infinite (won't auto-select)

Avoiding Overload Ambiguity

When overload resolution fails with 'ambiguous function call', add explicit casts to your arguments. This both fixes the error and documents your intent. For example: SUM(column::NUMERIC) clearly specifies which SUM overload you want.

Diagnosing Common Type Errors

Type errors are among the most common query failures. Let's examine typical type errors and how to diagnose and fix them. Error Category 1: Direct Type Incompatibility

type-incompatibility.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Error: Comparing incompatible types
SELECT * FROM orders WHERE order_date = 12345;
-- ERROR: operator does not exist: date = integer
 
-- Fix: Use appropriate literal type
SELECT * FROM orders WHERE order_date = '2024-01-15';
SELECT * FROM orders WHERE order_date = DATE '2024-01-15';
 
-- Error: Arithmetic on non-numeric type
SELECT name + 10 FROM employees;
-- ERROR: operator does not exist: character varying + integer
 
-- Fix: Use appropriate operation
SELECT name || ' (ID: ' || employee_id || ')' FROM employees;
 
-- Error: Boolean expression expected
SELECT * FROM employees WHERE salary;
-- ERROR: argument of WHERE must be type boolean
 
-- Fix: Create boolean expression
SELECT * FROM employees WHERE salary > 0;
SELECT * FROM employees WHERE salary IS NOT NULL;

Error Category 2: Function Type Mismatch

function-type-mismatch.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Error: Function doesn't exist for argument types
SELECT LENGTH(12345);
-- ERROR: function length(integer) does not exist
-- HINT: No function matches the given name and argument types.
 
-- Fix: Cast to appropriate type
SELECT LENGTH(12345::TEXT);   -- Returns 5
 
-- Error: String function on non-string
SELECT UPPER(salary) FROM employees;
-- ERROR: function upper(numeric) does not exist
 
-- Fix: Cast to string if that's what you want
SELECT UPPER(salary::TEXT) FROM employees;
 
-- Error: Aggregate type mismatch
SELECT SUM(name) FROM employees;
-- ERROR: function sum(character varying) does not exist
 
-- Fix: Use string aggregate if needed
SELECT STRING_AGG(name, ', ') FROM employees;
 
-- Error: Date function on wrong type
SELECT EXTRACT(YEAR FROM employee_id) FROM employees;
-- ERROR: cannot extract year from type integer
 
-- Fix: Use correct column
SELECT EXTRACT(YEAR FROM hire_date) FROM employees;

Error Category 3: Insert/Update Type Mismatch

insert-type-mismatch.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Error: Wrong type for column
INSERT INTO employees (id, hire_date) VALUES (1, 'not-a-date');
-- ERROR: invalid input syntax for type date: "not-a-date"
 
-- Fix: Use valid date format
INSERT INTO employees (id, hire_date) VALUES (1, '2024-01-15');
 
-- Error: Type conversion failure
INSERT INTO products (price) VALUES ('N/A');
-- ERROR: invalid input syntax for type numeric: "N/A"
 
-- Fix: Use NULL for missing numeric
INSERT INTO products (price) VALUES (NULL);
-- Or use CASE in data migration
INSERT INTO products (price)
SELECT CASE WHEN price_str = 'N/A' THEN NULL 
       ELSE price_str::NUMERIC END
FROM import_table;
 
-- Error: Precision overflow
INSERT INTO items (quantity) VALUES (99999999999999999999);
-- ERROR: integer out of range
 
-- Fix: Use appropriate type
ALTER TABLE items ALTER COLUMN quantity TYPE BIGINT;
-- Or check data validity before insert

Type Error Diagnostic Steps

•Read the error message carefully — It usually states exactly which types are incompatible
•Check column definitions — Use \d tablename (psql) or equivalent to verify types
•Verify literal types — Unquoted numbers vs. quoted strings behave very differently
•Consider NULL handling — NULL has no type until context provides one
•Look for implicit conversions — What works in one DB may fail in another
•Add explicit casts — When in doubt, cast explicitly to document intent

Explicit Type Casting

When implicit coercion isn't sufficient (or when you want to be explicit), SQL provides explicit casting syntax. Cast Syntax Variants:

cast-syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- CAST function (ANSI SQL standard)
SELECT CAST(salary AS INTEGER) FROM employees;
SELECT CAST('2024-01-15' AS DATE);
SELECT CAST(123 AS VARCHAR(10));
 
-- Double-colon syntax (PostgreSQL, commonly used)
SELECT salary::INTEGER FROM employees;
SELECT '2024-01-15'::DATE;
SELECT 123::VARCHAR(10);
 
-- Type function syntax (some databases)
SELECT INTEGER(salary) FROM employees;
SELECT DATE('2024-01-15');
 
-- Comparison: All equivalent in PostgreSQL
SELECT CAST(3.14159 AS INTEGER);   -- Standard SQL
SELECT 3.14159::INTEGER;            -- PostgreSQL shorthand
SELECT INTEGER '3.14159';           -- Type literal syntax (constants only)

When to Cast Explicitly: Resolving ambiguity — When overloaded functions match multiple signatures Forcing precision — Ensuring specific numeric precision in calculations Data transformation — Converting between fundamentally different types Documentation — Making type intentions clear for maintenance Portability — Avoiding reliance on DB-specific implicit coercion

explicit-cast-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Numeric precision control
SELECT 10 / 3;                -- Integer division: 3
SELECT 10::NUMERIC / 3;       -- Decimal division: 3.333...
SELECT 10 / 3::NUMERIC;       -- Also decimal: 3.333...
SELECT (10 / 3)::NUMERIC;     -- Still 3 (division already happened)
 
-- String to number conversion
SELECT '123'::INTEGER;         -- 123
SELECT '123.45'::NUMERIC;      -- 123.45
SELECT 'abc'::INTEGER;         -- ERROR: invalid input syntax
 
-- Date/time conversions
SELECT NOW()::DATE;            -- Current date (drops time)
SELECT NOW()::TIME;            -- Current time (drops date)
SELECT '2024-01-15'::TIMESTAMP; -- Midnight on that date
 
-- Complex casting
SELECT ARRAY[1,2,3]::TEXT;     -- '{1,2,3}' (array to text)
SELECT '{1,2,3}'::INTEGER[];   -- {1,2,3} (text to array)
 
-- JSON casting
SELECT '{"key": "value"}'::JSON;
SELECT '{"key": "value"}'::JSONB;
SELECT row_to_json(t) FROM mytable t;  -- Row to JSON
 
-- Casting with NULL handling
SELECT COALESCE(nullable_col::TEXT, 'N/A');
SELECT NULLIF(value::TEXT, '')::INTEGER;  -- Empty string to NULL to int

Cast Failures

Not all casts succeed. Casting 'hello' to INTEGER fails at runtime. Casting a large BIGINT to INTEGER may overflow. Test your casts with edge cases, and consider using TRY_CAST (SQL Server) or error handling where available.

Type Checking

The Type Safety Guardian

The SQL Type System

Type Inference for Expressions

Implicit Type Coercion

Type Checking in Different Contexts

Function Type Signatures and Overloading

Diagnosing Common Type Errors

Explicit Type Casting

There’s more to understand.

Type Checking

The Type Safety Guardian

The SQL Type System

Type Inference for Expressions

Implicit Type Coercion

Type Checking in Different Contexts

Function Type Signatures and Overloading

Diagnosing Common Type Errors

Explicit Type Casting

There’s more to understand.