Type casting with :: operator in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the :: operator do in PostgreSQL?

It converts a value from one data type to another, also called type casting.

beginner

How do you cast a string '123' to an integer using the :: operator?

You write '123'::integer to convert the string to an integer.

intermediate

Can you use the :: operator to cast between any two types?

No, only between compatible types. For example, casting text to integer works if the text is a valid number.

intermediate

What happens if you try to cast a non-numeric string to integer using ::?

PostgreSQL will return an error because the string cannot be converted to an integer.

intermediate

Is :: operator specific to PostgreSQL or standard SQL?

The :: operator is specific to PostgreSQL and some other databases; standard SQL uses the CAST() function.

What does '2024-06-01'::date do in PostgreSQL?

A Returns the string unchanged

B Converts the string to an integer

C Causes a syntax error

D Converts the string to a date type

The ::date casts the string to a date type.

Which of these is a valid use of the :: operator?

A <code>123::text</code>

B <code>'abc'::integer</code>

C <code>'true'::integer</code>

D <code>NULL::boolean</code>

Casting number 123 to text is valid. Casting 'abc' to integer or 'true' to integer is invalid. NULL can be cast to boolean and is valid.

What error occurs if you cast 'hello'::integer?

A No error, returns 0

B Syntax error

C Invalid input syntax for integer error

D Returns NULL

PostgreSQL throws an invalid input syntax error because 'hello' is not a number.

Which is the standard SQL alternative to :: for casting?

A CAST(value AS type)

B CONVERT(value, type)

C TO_TYPE(value)

D CHANGE_TYPE(value)

Standard SQL uses the CAST function to convert types.

What type will 123.45::integer return?

A 123.45 as float

B 123 as integer

C Error because of decimal

D NULL

Casting float to integer truncates the decimal part, returning 123.

Explain how the :: operator works in PostgreSQL and give an example.

Think about converting one data type to another.

What are the risks or errors you might face when using the :: operator?

Consider what happens if the value cannot be converted.