::
SQL construct
MySQL equivalent:
Converts a value to the given type: a short form of CAST(value AS type) available only in PostgreSQL.
PostgreSQL 17.5
value::typeParameters
- value
- Value to convert
- typedata type
- Target data type, for example integer, numeric, date or text
Return value
A value of the given type; a failed conversion is a query error
Examples
Strings to a number and a date, a fraction to an integer
PostgreSQL 17.5
SELECT '42'::integer + 1 AS number,
'2024-03-08'::date AS DAY,
3.7::integer AS rounded;Date and text from columns
PostgreSQL 17.5
SELECT member_name,
birthday::date AS birth_date,
member_id::text AS id_text
FROM FamilyMembers;Integer and exact division
PostgreSQL 17.5
SELECT 10 / 4 AS integer_division,
10::numeric / 4 AS exact_division;Details
value::type does the same as CAST(value AS type) and follows the same rules: for example, '3.7'::integer causes an error, while 3.7::integer is rounded to 4.
The :: operator is applied before arithmetic: in 10::numeric / 4 only 10 is cast to numeric, and the division becomes exact. To cast a whole expression, put it in parentheses: (amount * unit_price)::text.