Type conversion

2 snippets in SQL

SQLSQL

CAST / Type Conversion

syntax

CAST(expression AS data_type)
expression::data_type  -- PostgreSQL shorthand

example

SELECT
  CAST(price AS INTEGER) AS rounded_price,
  CAST(order_date AS VARCHAR) AS date_string,
  '42'::INTEGER + 8 AS sum_pg  -- PostgreSQL only
FROM products;

output

-- rounded_price | date_string | sum_pg
-- 29             | 2025-03-15  | 50

Note CAST is ANSI standard. PostgreSQL's :: shorthand is shorter but not portable. Be careful casting — CAST('abc' AS INTEGER) will throw an error. Use TRY_CAST in SQL Server for safe conversions.

Implicit Type Conversion

SQL · Common Mistakes

syntax

-- WRONG: comparing string column to integer
WHERE phone_number = 2065551234
-- The DB converts every phone_number to int for comparison!

-- CORRECT: compare with matching types
WHERE phone_number = '2065551234'

example

-- Slow: index on user_code (VARCHAR) is not used
SELECT * FROM users WHERE user_code = 12345;

-- Fast: index is used
SELECT * FROM users WHERE user_code = '12345';

output

-- Type mismatch forces a full table scan because the index cannot be used

Note When you compare a string column to a number, the database may cast every row's string value to a number, which prevents index usage and can cause errors on non-numeric strings. Always match the data type in your comparison.