Casting & Type Pitfalls

Casting is how you tell SQL, explicitly, "treat this value as that type." It is useful when the database needs help, dangerous when you use it to paper over messy data, and the doorway into several classic numeric and date traps.

Explicit casts

The standard spelling is CAST(value AS type). Here the menu price is a number, but the display label needs text so it can be concatenated with ' USD':

SELECT name,
       price,
       CAST(price AS text) || ' USD' AS display_price
FROM menu_items
WHERE id IN (1, 4)
ORDER BY id;

   name    | price | display_price
-----------+-------+---------------
 Espresso  |  2.50 | 2.50 USD
 Cold Brew |  4.50 | 4.50 USD
(2 rows)

That is the healthy use of a cast: the source value is already valid, and you are changing how the query treats it for one expression. The table has not changed. price is still numeric in the schema; only this selected expression is text.

Postgres also has a compact shorthand, value::type. You will see it constantly in Postgres codebases:

SELECT CAST('4.50' AS numeric) AS standard_cast,
       '4.50'::numeric AS pg_shorthand;

 standard_cast | pg_shorthand
---------------+--------------
          4.50 |         4.50
(1 row)

In this course, read :: as "Postgres shorthand for CAST(... AS ...)." Use the standard spelling when portability matters or when you are still learning. Use the shorthand when you are reading or writing Postgres-heavy SQL and it makes the expression clearer.

Integer division is not a half

The most surprising cast lesson is what happens before you cast anything. In Postgres, 1 and 2 are integer literals, so 1 / 2 uses integer division:

SELECT 1 / 2 AS integer_division,
       CAST(1 AS numeric) / 2 AS numeric_division;

 integer_division |    numeric_division
------------------+------------------------
                0 | 0.50000000000000000000
(1 row)

This matters in real reports. A ratio like successful_orders / total_orders looks reasonable, but if both columns are integers, small percentages can collapse to 0. Section 4 will teach aggregate totals; this is the arithmetic rule you need before those totals become ratios. SQL Server has the same integer-division trap, so this habit travels.

Floating point is approximate

REAL and DOUBLE PRECISION are useful types, but they are approximate floating-point types. They are built for measurements, coordinates, and scientific-style values where tiny binary rounding differences are acceptable. They are not a good container for money-like facts.

You can see the classic example directly:

SELECT CAST(0.1 AS double precision) + CAST(0.2 AS double precision) AS float_sum,
       CAST(0.1 AS numeric) + CAST(0.2 AS numeric) AS numeric_sum;

      float_sum      | numeric_sum
---------------------+-------------
 0.30000000000000004 |         0.3
(1 row)

The floating-point answer is not "wrong" for floating-point math. It is the honest result of an approximate representation. The problem is choosing that representation for a fact that humans expect to be exact. For prices, invoices, tax rates, and anything you would argue over in cents, use NUMERIC / DECIMAL and pick the precision deliberately.

Implicit casts: convenient until they hide the problem

Postgres sometimes casts for you. In this query, price is numeric and '4.50' starts as a string literal, but the comparison works because Postgres can infer that the literal should be numeric:

SELECT name, price
FROM menu_items
WHERE price = '4.50';

   name    | price
-----------+-------
 Cold Brew |  4.50
(1 row)

That convenience is why implicit casts exist. It is also why they are risky: the query may run today because the sample value happens to parse cleanly, then fail when the next value is 'four dollars', or behave differently when a different engine has different casting rules.

The professional habit is to put conversions at the edge of the system and make them obvious. If your app receives text, validate it before it reaches the query, or cast it in one deliberate place where errors are expected and handled. Do not scatter "maybe this text is a number" assumptions through report queries.

Dates: prefer ISO and typed literals

Dates have their own version of the same lesson. The safe shape is unambiguous ISO order: year, month, day. Postgres can cast that text, and SQL also has a typed date literal:

SELECT CAST('2026-03-02' AS date) AS cast_date,
       DATE '2026-03-02' AS date_literal;

 cast_date  | date_literal
------------+--------------
 2026-03-02 | 2026-03-02
(1 row)

The unsafe shape is human shorthand. A string like '03/02/2026' is ambiguous before the database ever sees it: is that March 2 or February 3? Different settings and different engines can disagree. For course queries and production code, prefer DATE '2026-03-02' or an explicit CAST('2026-03-02' AS date) with ISO-formatted text.

When the text is not a real date, Postgres refuses it:

SELECT CAST('2026-02-30' AS date);

ERROR:  date/time field value out of range: "2026-02-30"
LINE 1: SELECT CAST('2026-02-30' AS date)
                    ^

That error is a good thing. It means the database protected the type promise from 3.1: a date column should not contain impossible calendar days.

You now have the full Section 3 toolkit: types, NULL, CASE, and casts. Next, Section 4 starts turning many rows into answers with aggregate functions.