Parsing and Validation

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;

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

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 ✓

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

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;

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;

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

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)

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