CAST in PostgreSQL

PostgreSQL offers two type conversion syntaxes: the SQL-standard CAST(value AS target_type) function and the PostgreSQL-specific cast operator value::target_type. Both produce identical results. Use CAST() when writing SQL that must also run on other databases; use :: for brevity in PostgreSQL-only code.

Syntax

-- SQL standard
CAST(value AS target_type)

-- PostgreSQL shorthand (not in SQL standard)
value::target_type

If the value cannot be converted, PostgreSQL raises an error:

SELECT CAST('10abc' AS INTEGER);
-- ERROR: invalid input syntax for type integer: "10abc"

Practical Example

An order import table stores some fields as text because they arrived from a CSV file. Cast them to the appropriate types for analysis:

CREATE TABLE order_imports (
  raw_id       TEXT,
  raw_amount   TEXT,
  raw_date     TEXT,
  raw_shipped  TEXT    -- 'true' / 'false'
);

INSERT INTO order_imports VALUES
  ('101', '249.99', '2026-03-15', 'true'),
  ('102', '89.50',  '2026-03-16', 'false'),
  ('103', '12.00',  '2026-03-17', 'true');

-- Cast to native types for calculations and filtering
SELECT
  CAST(raw_id AS INTEGER)          AS order_id,
  CAST(raw_amount AS NUMERIC(10,2)) AS amount,
  CAST(raw_date AS DATE)           AS order_date,
  CAST(raw_shipped AS BOOLEAN)     AS shipped
FROM order_imports
WHERE CAST(raw_amount AS NUMERIC(10,2)) > 50.00;

 order_id | amount |  order_date | shipped
----------+--------+-------------+---------
      101 | 249.99 | 2026-03-15  | t
      102 |  89.50 | 2026-03-16  | f

Common Type Conversions

Important: PostgreSQL uses DOUBLE PRECISION, not DOUBLE. Using DOUBLE alone raises a type-not-found error:

SELECT CAST('10.5' AS DOUBLE PRECISION);  -- correct
SELECT '10.5'::DOUBLE PRECISION;          -- correct shorthand

Safe Casting with CASE

When a column contains mixed content—mostly numeric strings but with occasional non-numeric entries—combine CASE with a regex guard to avoid cast errors:

CREATE TABLE product_ratings (
  product_id  INTEGER,
  raw_score   TEXT    -- '1'-'5' or 'N/A' for unrated
);

INSERT INTO product_ratings VALUES
  (1, '4'), (2, '5'), (3, 'N/A'), (4, '3'), (5, 'N/A');

SELECT
  product_id,
  CASE
    WHEN raw_score ~ '^[0-9]+$' THEN CAST(raw_score AS INTEGER)
    ELSE NULL
  END AS score_int
FROM product_ratings;

Verify the resulting types match what downstream functions expect:

SELECT
  pg_typeof(CAST('42' AS INTEGER))          AS integer_type,
  pg_typeof('2026-01-01'::DATE)             AS date_type,
  pg_typeof(CAST('19.99' AS NUMERIC(10,2))) AS numeric_type;

Testing with Vela

Type mismatch issues often hide in edge-case rows with unusual formatting. Use a Vela branch cloned from production to run your CAST expressions against the full column before applying them in migrations or ETL jobs. Failing casts surface cleanly on a branch without affecting production data or causing transaction rollbacks on live tables.

Production Tips

• Cast the literal, not the column: WHERE id = CAST('5' AS INTEGER) preserves index use on id; WHERE CAST(id AS TEXT) = '5' disables it.
• PostgreSQL uses DOUBLE PRECISION, not DOUBLE. Omitting PRECISION raises an error—always write the full type name.
• Casting a floating-point number to INTEGER rounds using standard mathematical rounding (half-up). Be explicit about rounding intent using ROUND(), FLOOR(), or CEIL() before casting when the direction matters.
• The :: operator is PostgreSQL-specific. Switch to CAST() in any code intended to run on multiple database platforms.
• For non-standard date formats (e.g., '15-Mar-2026'), use TO_DATE(string, format) or TO_TIMESTAMP(string, format) instead of CAST, which only accepts formats PostgreSQL recognizes automatically.
• When constructing UNION queries, ensure matching column positions have compatible types. Use CAST(NULL AS target_type) as a placeholder column in branches that do not have that value.