SQLDailyDrills

CAST and ::

Convert a value to another data type.

Syntax

CAST(value AS type)
value::type
  • The :: form is PostgreSQL only. CAST works in every database.

Example

SELECT name, CAST(unit_price AS integer) AS whole_price FROM products ORDER BY product_id;

Prices rounded to whole numbers.

Tables used: products. Try it in a lesson below.

Common mistakes

  • Casting a decimal to integer rounds; it does not truncate.
  • Integer divided by integer is an integer in PostgreSQL: 7 / 2 is 3. Cast one side to numeric to get 3.5.

Practise it