Beginner6 min read PostgreSQL
Data Types & Casting in PostgreSQL
The types Postgres uses (text, integer, numeric, date, timestamp, boolean) and how to convert between them with ::type or CAST().
- type
- cast
- ::
- INTEGER
- NUMERIC
- TEXT
- DATE
- TIMESTAMP
Use this when you need to:
- Convert a string column (e.g., "12.5") into a number so you can do math on it
- Pull the year or month out of a timestamp by casting to a date or using EXTRACT
- Force a literal into a specific numeric precision to avoid floating-point surprises
- Compare values from columns that disagree on type — Postgres won't auto-cast everything
- Format a date or timestamp for display in a report
Every column in PostgreSQL has a type, and operations between types are stricter than in some other databases. Understanding the basic types and how to cast between them prevents a whole category of "why doesn't this work" moments.
The Types You'll See Most
-- Text text -- variable-length string varchar(n) -- limited-length string -- Numbers integer / int -- whole number, ~ ±2 billion bigint -- big whole number numeric(p, s) -- exact decimal — pick this for money double precision -- floating point — pick this for stats -- Dates and times date -- 2025-01-15 timestamp -- 2025-01-15 14:30:00 timestamptz -- with time zone (preferred for new columns) -- Boolean boolean -- true, false, or NULL
Casting — :: or CAST()
-- Postgres shorthand
SELECT '42'::integer; -- 42
SELECT 3.14::numeric(4,2); -- 3.14
SELECT NOW()::date; -- 2025-01-15
-- Standard SQL syntax (more portable)
SELECT CAST('42' AS integer);
SELECT CAST(NOW() AS date);Common Conversions
-- A number that arrived as text
SELECT '123'::integer + 1; -- 124
-- A real one: active_student_count on ods_course_sections is stored as
-- varchar. sec_capacity - active_student_count fails with
-- "operator does not exist: numeric - character varying" until you cast.
SELECT
course_sections_id,
sec_capacity,
sec_capacity - active_student_count::integer AS open_seats
FROM ods_course_sections;
-- Date out of a timestamp
SELECT applications_id, appl_status_date::date AS status_day
FROM ods_appl_status_history;
-- Forcing exact decimal for money math
SELECT
fa_student,
fa_year,
(fa_cost_of_attendance - fa_total_awards)::numeric(10,2) AS cost_not_covered
FROM ods_financial_aid;When in doubt about precision (money, GPAs, anything that needs exact decimals), reach for numeric(p, s) — not double precision. Floating-point math is fast but loses cents to rounding.
Practise this on real university data
Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.