Type casting with :: operator in PostgreSQL - Step-by-Step Execution

Concept Flow - Type casting with :: operator

Start with a value

↓

Apply :: operator

↓

Specify target type

↓

Convert value to target type

↓

Use converted value in query

This flow shows how a value is converted from one type to another using the :: operator in PostgreSQL.

Execution Sample

PostgreSQL

SELECT '123'::int AS number;
SELECT 45.67::text AS text_value;
SELECT '2024-06-01'::date AS date_value;

These queries convert string literals to integer, float to text, and string to date using the :: operator.

Execution Table

Step	Expression	Action	Result	Notes
1	'123'::int	Convert string '123' to integer	123	String '123' cast to int 123
2	45.67::text	Convert float 45.67 to text	'45.67'	Float 45.67 cast to text '45.67'
3	'2024-06-01'::date	Convert string '2024-06-01' to date	2024-06-01	String cast to date type
4	SELECT 'abc'::int	Attempt to convert non-numeric string to int	ERROR	Invalid cast causes error
5	End	No more expressions	Execution stops	All valid casts completed

💡 Execution stops after all casts are processed or on error.

Variable Tracker

Expression	Initial Value	After Cast
'123'::int	'123' (text)	123 (integer)
45.67::text	45.67 (float)	'45.67' (text)
'2024-06-01'::date	'2024-06-01' (text)	2024-06-01 (date)

Key Moments - 2 Insights

Why does casting '123'::int work but 'abc'::int causes an error?

Because '123' is a numeric string that can be converted to integer, but 'abc' contains letters and cannot be converted to a number, causing an error as shown in execution_table row 4.

What happens to the data type after using the :: operator?

The data type changes to the specified target type immediately after casting, as shown in variable_tracker where '123' changes from text to integer.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the result of 45.67::text?

A 45.67

B '45,67'

C '45.67'

D ERROR

💡 Hint

Check the 'Result' column in execution_table row 2 for the exact output.

At which step does the casting cause an error?

A Step 4

B Step 3

C Step 1

D Step 5

💡 Hint

Look for the row with 'ERROR' in the 'Result' column in execution_table.

If you cast '2024-06-01'::date, what type does the value become according to variable_tracker?

A text

B date

C integer

D float

💡 Hint

Refer to variable_tracker row for '2024-06-01'::date to see the 'After Cast' type.

Concept Snapshot

Type casting with :: operator in PostgreSQL:
- Syntax: value::target_type
- Converts value to the specified type
- Works for compatible conversions (e.g., text to int)
- Causes error if conversion is invalid
- Useful for explicit type control in queries

Full Transcript

This visual execution trace shows how PostgreSQL uses the :: operator to convert values from one data type to another. Starting with a value, the :: operator is applied followed by the target type. The value is then converted to that type and can be used in queries. Examples include converting a string '123' to integer 123, a float 45.67 to text '45.67', and a string date '2024-06-01' to a date type. An invalid cast like 'abc' to int causes an error. Variables change type immediately after casting. This helps control data types explicitly in SQL queries.

Practice

1. What does the PostgreSQL expression '123'::integer do?

easy

A. Causes a syntax error because :: is invalid

B. Converts the integer 123 to text '123'

C. Converts the text '123' to an integer number 123

D. Leaves the value as text without any change

Solution

1. Step 1: Understand the :: operator

   The :: operator in PostgreSQL is used to cast or convert a value from one data type to another.

2. Step 2: Apply casting to the given expression

   The expression '123'::integer converts the string '123' into the integer 123.

3. Final Answer:

   Converts the text '123' to an integer number 123 -> Option C

4. Quick Check:

   Type casting '123'::integer = 123 [OK]

Hint: Remember :: changes type, here text to integer [OK]

Common Mistakes:

• Thinking :: converts integer to text
• Assuming :: is invalid syntax
• Believing the value stays as text

2. Which of the following is the correct syntax to cast a column named price to numeric in PostgreSQL?

easy

A. SELECT price AS numeric FROM products;

B. SELECT price to numeric FROM products;

C. SELECT CAST(price numeric) FROM products;

D. SELECT price::numeric FROM products;

Solution

1. Step 1: Recall correct casting syntax

   In PostgreSQL, the correct way to cast a value is using value::type or CAST(value AS type).

2. Step 2: Identify the correct option

   SELECT price::numeric FROM products; uses price::numeric, which is valid syntax. Options B, C, and D have incorrect syntax.

3. Final Answer:

   SELECT price::numeric FROM products; -> Option D

4. Quick Check:

   Correct cast syntax uses :: [OK]

Hint: Use ::type after value for casting [OK]

Common Mistakes:

• Using 'to' instead of :: or CAST
• Incorrect CAST syntax missing AS
• Using AS for casting instead of aliasing

3. What is the output of this query?

SELECT '2023-06-01'::date + 5;

medium

A. '2023-06-06' (date 5 days after June 1, 2023)

B. 2023-06-01 05:00:00 (timestamp with 5 hours added)

C. Error: cannot add integer to date

D. '2023-06-01 5' (string concatenation)

Solution

1. Step 1: Cast string to date

   The expression '2023-06-01'::date converts the text to a date value representing June 1, 2023.

2. Step 2: Add integer to date

   Adding 5 to a date adds 5 days, resulting in June 6, 2023.

3. Final Answer:

   '2023-06-06' (date 5 days after June 1, 2023) -> Option A

4. Quick Check:

   Date + integer adds days [OK]

Hint: Adding integer to date adds days [OK]

Common Mistakes:

• Thinking integer adds hours or seconds
• Expecting string concatenation
• Assuming error on adding integer to date

4. Identify the error in this query:

SELECT 'abc'::integer;

medium

A. Runtime error: invalid input syntax for integer

B. Syntax error due to invalid cast operator

C. Returns NULL because 'abc' is not a number

D. No error, returns 0

Solution

1. Step 1: Understand casting invalid text to integer

   Trying to cast a non-numeric string like 'abc' to integer causes a runtime error in PostgreSQL.

2. Step 2: Identify error type

   The error message is 'invalid input syntax for integer', indicating the cast failed at runtime.

3. Final Answer:

   Runtime error: invalid input syntax for integer -> Option A

4. Quick Check:

   Invalid cast causes runtime error [OK]

Hint: Casting non-numeric text to integer causes error [OK]

Common Mistakes:

• Expecting NULL instead of error
• Thinking syntax error instead of runtime error
• Assuming automatic conversion to 0

5. You have a table orders with a text column order_date storing dates as strings like '06/01/2023'. You want to find all orders after June 1, 2023. Which query correctly uses type casting with :: to achieve this?

hard

A. SELECT * FROM orders WHERE order_date > '2023-06-01'::date;

B. SELECT * FROM orders WHERE order_date::date > '2023-06-01';

C. SELECT * FROM orders WHERE order_date::text > '2023-06-01'::text;

D. SELECT * FROM orders WHERE order_date::integer > 20230601;

Solution

1. Step 1: Understand data types involved

   The column order_date is text, so to compare dates properly, it must be cast to date type.

2. Step 2: Choose correct casting and comparison

   SELECT * FROM orders WHERE order_date::date > '2023-06-01'; casts order_date to date and compares it to a date literal, which is correct. SELECT * FROM orders WHERE order_date > '2023-06-01'::date; compares text to date, which is invalid. SELECT * FROM orders WHERE order_date::text > '2023-06-01'::text; compares text to text, which is lexicographical, not date. SELECT * FROM orders WHERE order_date::integer > 20230601; tries to cast text to integer, which will cause error.

3. Final Answer:

   SELECT * FROM orders WHERE order_date::date > '2023-06-01'; -> Option B

4. Quick Check:

   Cast text column to date before date comparison [OK]

Hint: Cast text column to date before comparing dates [OK]

Common Mistakes:

• Comparing text directly to date
• Casting to wrong type like integer
• Comparing text lexicographically instead of date