🌱BeginnerConstraints
CAST and Type Conversion
Convert values between data types using CAST and the :: shorthand.
Overview
Type conversion changes a value from one data type to another. PostgreSQL supports explicit casts (CAST(val AS type) or the shorthand val::type) and implicit casts (automatic conversion when the context requires it). Explicit casting is always preferred in production code implicit casting can silently behave in unexpected ways.
Casts are used everywhere: parsing user input (text to dates), converting between numeric precision levels, generating output labels, and moving data between tables with different schemas.
Key Points
- •CAST(val AS type) SQL standard syntax
- •val::type PostgreSQL shorthand, very common
- •Implicit casts happen automatically in some contexts
- •Casting can fail at runtime if conversion is impossible (e.g., 'abc'::INTEGER)
- •TRY_CAST does not exist in PostgreSQL use to_number(), to_date(), etc. for safe conversion
Examples
Common casts
-- Text to integer
SELECT '42'::INTEGER;
SELECT CAST('42' AS INTEGER);
-- Integer to text
SELECT 100::TEXT || ' items';
-- Float to integer (truncates)
SELECT 9.7::INTEGER; -- returns 9
-- Text to timestamp
SELECT '2024-06-15 10:00:00'::TIMESTAMPTZ;
-- Date to text
SELECT NOW()::DATE::TEXT;Safe conversion with format functions
-- to_number: safe text-to-number with format pattern
SELECT to_number('1,234.56', '9,999.99');
-- to_date: safe text-to-date parsing
SELECT to_date('15/06/2024', 'DD/MM/YYYY');
-- to_char: number/date to formatted text
SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT to_char(1234567.89, 'FM999,999,990.00');Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4