PostgreSQL

Chapter 7 - DQL (Data Query Language)

CAST

In PostgreSQL, the CAST function is used to convert one data type to another. This can be particularly useful when you need to ensure that data types match in expressions or queries, or when you need to format data in a specific way for display or further processing. Casting can help with data manipulation and type compatibility, making your SQL queries more flexible and powerful.

Here's a guide to using CAST in PostgreSQL:

Basic Syntax of CAST
- To cast a value to a different type, you can use the CAST function.
- Syntax:
```
CAST(expression AS target_type)
```
Casting Examples
- Casting an integer to a text:
```
SELECT CAST(123 AS TEXT);
```
  - This converts the integer 123 to the text '123'. Casting a text to an integer:
```
SELECT CAST('456' AS INTEGER);
```
    - This converts the text '456' to the integer 456.
Using CAST with Table Data
- Example 1: Casting a numeric field to a text for display purposes
```
SELECT CAST(account_balance AS TEXT) AS balance_text
FROM accounts;
```
  - This converts the account_balance from the accounts table to text.
- Example 2: Casting a text field to a date for comparison
```
SELECT *
FROM transactions
WHERE transaction_date = CAST('2024-09-17' AS DATE);
```
  - This converts the text '2024-09-17' to a date for comparison in the transactions table.
Common Use Cases
- Ensuring Data Type Compatibility:
  - When performing operations or comparisons between different data types, casting ensures that the data types are compatible.
- Formatting Output:
  - Use casting to convert numbers to text for formatted output or reporting.

By mastering the use of CAST, you can handle various data types more effectively in your PostgreSQL queries and ensure that your data manipulations are accurate and well-formatted.

Tansy SQL Course - CAST - Video Thumbnail

TEST CODE

In this instance, the CAST function is applied to the order date, facilitating the conversion of datetime data type into the date format.

SELECT order_id, order_number, CAST(order_date AS DATE) AS order_date
FROM act_order;

In this case, the CAST function is used to convert a numeric data type with decimals into an integer data type, eliminating any decimal points.

SELECT employee_id, employee_number, CAST(salary AS INTEGER) AS salary
FROM org_employee;

Syntax of CAST

CAST(expression AS target_type)

expression is the value you wish to convert.
target_type is the data type you want the result to be.

Examples of CAST

Converting a price to an integer:
SELECT CAST(price AS INT) FROM products

Formatting a date as a string:
SELECT CAST(order_date AS VARCHAR(10)) FROM orders

The CAST function is part of the ANSI SQL standard and is supported by various SQL databases like MySQL, PostgreSQL, SQL Server, and SQLite, ensuring consistency in its use and behavior across these systems.

Limitations of the CAST Function in SQL

Data Loss: Casting from a higher precision or larger capacity data type to a lower one can result in data loss or truncation, such as from FLOAT to INT.
Performance: The CAST function can increase computational overhead and affect query performance, especially with large datasets.
Data Type Compatibility: Not all data type conversions are supported, and incompatible conversions will result in errors.
Syntax and Support: Different SQL systems may have varying levels of support for CAST, potentially leading to inconsistencies.
Implicit Conversion Risks: Implicit conversions without CAST can lead to unexpected results if the rules for conversion are not well understood.
Locale and Format: Locale settings can affect the behavior of CAST with locale-sensitive data types like dates and times.
Storage Format: Data stored in non-standard formats may require additional manipulation beyond CAST.
Precision and Scale: Consideration of precision and scale is necessary when casting to decimal types to avoid errors.
SQL Injection: Dynamic SQL using CAST can be vulnerable to SQL injection if user input is not properly sanitized.
Default Values: Unlike COALESCE or ISNULL, CAST does not handle NULLs or provide a default value if conversion fails.