Type casting with :: operator in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this type casting query?

Consider the following PostgreSQL query:

SELECT '123'::integer + 10 AS result;

What is the value of result?

PostgreSQL

SELECT '123'::integer + 10 AS result;

A '12310'

B 133

C Syntax error

D Type error

💡 Hint

Casting '123' to integer allows arithmetic addition.

The string '123' is cast to integer 123, then 10 is added, resulting in 133.

📝 Syntax

intermediate

Which option causes an error in type casting?

Which of the following PostgreSQL queries will cause an error due to invalid use of the :: operator?

A SELECT 'abc'::integer;

B SELECT 123::text;

C SELECT '123'::integer + 10;

D SELECT '2023-01-01'::date;

💡 Hint

Casting a non-numeric string to integer is invalid.

Option A tries to cast 'abc' to integer, which causes a runtime error (invalid input syntax for type integer: "abc"). The other options are valid.

❓ query_result

advanced

What is the output of this complex type casting?

Given the query:

SELECT ('2023-06-15 14:30:00'::timestamp)::date AS cast_date;

What is the value of cast_date?

PostgreSQL

SELECT ('2023-06-15 14:30:00'::timestamp)::date AS cast_date;

A Type error

B 2023-06-15 14:30:00

C Syntax error

D 2023-06-15

💡 Hint

Casting timestamp to date removes the time part.

The timestamp is first cast from string, then cast to date, which keeps only the date part.

🔧 Debug

advanced

Why does this query fail?

Examine the query:

SELECT '12.34'::integer;

Why does this query fail?

PostgreSQL

SELECT '12.34'::integer;

A Because integer type does not exist in PostgreSQL

B Because the :: operator is not valid for casting strings

C Because '12.34' is a string that cannot be cast to integer due to decimal point

D Because the query is missing a FROM clause

💡 Hint

Integer casting requires whole numbers without decimals.

The string '12.34' contains a decimal point, so casting to integer fails with invalid input syntax error.

❓ optimization

expert

Which query is more efficient for casting and filtering?

You want to select rows where the text column price_text represents an integer greater than 100.

Which query is more efficient in PostgreSQL?

A SELECT * FROM products WHERE price_text::integer > 100;

B SELECT * FROM products WHERE price_text > '100';

C SELECT * FROM products WHERE CAST(price_text AS integer) > 100;

D SELECT * FROM products WHERE price_text::text > '100';

💡 Hint

The :: operator is a shorthand for CAST and often preferred for readability and performance.

Option A uses :: operator which is concise and efficient. Option A is equivalent but more verbose. Options C and D compare strings lexicographically, which is incorrect for numeric comparison.