PostgreSQL CAST and :: Type Conversion

The CAST() function and :: operator in PostgreSQL convert values from one data type to another. Type casting is essential for ensuring compatible types in operations, comparisons, and function calls.

Syntax

-- SQL standard syntax
CAST(expression AS target_type)

-- PostgreSQL shorthand (:: operator)
expression::target_type

-- Common target types: INTEGER, BIGINT, NUMERIC, TEXT, DATE, TIMESTAMP, BOOLEAN, JSONB, etc.

Official Documentation

Example usage

-- Cast text to integer
SELECT CAST('123' AS INTEGER) AS number;
-- Result: 123

-- Using :: operator (equivalent)
SELECT '123'::INTEGER AS number;

-- Cast to numeric with precision
SELECT CAST('123.456' AS NUMERIC(5,2)) AS rounded;
-- Result: 123.46

-- Cast integer to text
SELECT CAST(42 AS TEXT) || ' items' AS message;
-- Result: '42 items'

-- Cast text to date
SELECT '2025-12-26'::DATE AS date;

-- Cast text to timestamp
SELECT '2025-12-26 14:30:00'::TIMESTAMP AS timestamp;

-- Cast to boolean
SELECT
    'true'::BOOLEAN AS bool1,
    't'::BOOLEAN AS bool2,
    '1'::BOOLEAN AS bool3,
    'yes'::BOOLEAN AS bool4;
-- All return: true

-- Cast JSON to JSONB
SELECT '{"name": "John"}'::JSONB AS data;

-- Cast numeric to text for concatenation
SELECT 'Order #' || order_id::TEXT AS order_label
FROM orders;

-- Cast for comparison
SELECT * FROM products
WHERE price::TEXT LIKE '%.99';

-- Cast extracted JSON values
SELECT
    (data ->> 'age')::INTEGER AS age,
    (data ->> 'price')::NUMERIC AS price
FROM products;

-- Array casting
SELECT '{1,2,3}'::INTEGER[] AS numbers;

-- Cast to different numeric types
SELECT
    value::INTEGER AS int_value,
    value::BIGINT AS bigint_value,
    value::NUMERIC AS numeric_value,
    value::REAL AS real_value,
    value::DOUBLE PRECISION AS double_value
FROM measurements;

-- Safe casting with TRY_CAST (PostgreSQL 15+)
-- Note: Use CASE or exception handling for older versions
SELECT
    CASE
        WHEN value ~ '^[0-9]+$' THEN value::INTEGER
        ELSE NULL
    END AS safe_int
FROM data;

-- Cast timestamp to date (truncate time)
SELECT created_at::DATE AS created_date
FROM orders;

-- Cast intervals
SELECT '1 day'::INTERVAL + CURRENT_TIMESTAMP AS tomorrow;

-- Multiple casting
SELECT '42'::INTEGER::NUMERIC::TEXT AS chain_cast;

-- Cast in WHERE clause
SELECT * FROM users
WHERE birth_date::TEXT LIKE '1990%';

-- Cast for aggregation
SELECT
    category,
    AVG(price::NUMERIC) AS avg_price
FROM products
GROUP BY category;

-- Cast NULL
SELECT NULL::INTEGER AS null_int;
-- Result: NULL (with INTEGER type)

-- Cast for ORDER BY
SELECT * FROM products
ORDER BY (metadata ->> 'priority')::INTEGER DESC;

Common issues

1. Invalid casts: Not all types can be cast to all others. 'abc'::INTEGER raises an error.
2. Precision loss: Casting NUMERIC to INTEGER truncates decimals: 123.9::INTEGER = 123.
3. Overflow: Casting large BIGINT to INTEGER can cause overflow errors.
4. NULL handling: Casting NULL preserves NULL but changes type.

Best practices

1. Use :: operator for conciseness in PostgreSQL-specific code.
2. Use CAST() for SQL standard compliance and portability.
3. Validate data before casting user input to avoid errors.
4. Be explicit about precision when casting to NUMERIC: CAST(value AS NUMERIC(10,2)).

Frequently Asked Questions

Q: What's the difference between CAST() and ::?
A: They're functionally equivalent. CAST(value AS INTEGER) and value::INTEGER produce the same result. The :: operator is PostgreSQL-specific and more concise. CAST is SQL standard.

Q: What happens when I cast an invalid value?
A: PostgreSQL raises an error: 'abc'::INTEGER causes an error. For safe casting, validate first with regex or use CASE to handle invalid values gracefully.

Q: Does casting change the original value?
A: No, casting creates a new value with the target type. The original value is unchanged.

Q: How do I handle casting errors gracefully?
A: Use CASE with validation: CASE WHEN value ~ '^[0-9]+$' THEN value::INTEGER ELSE 0 END. Or wrap in a try-catch block in PL/pgSQL functions.

Q: Can I cast to custom types?
A: Yes, PostgreSQL supports casting to user-defined types (composite types, enums, domains). Use the same syntax: value::my_custom_type.