Type Conversion

Sometimes, data is stored in one format but needs to be used in another. For example, a “Price” might be stored as a String (VARCHAR) in a legacy system, but you need it to be a Decimal to perform calculations.

Definition: Casting

Casting is the process of explicitly converting a value from one data type to another.

Context: Implicit vs. Explicit

Implicit Conversion

The database engine automatically converts a type behind the scenes.

Example: If you run SELECT '100' + 5;, the engine might see the string '100', convert it to an integer, and return 105.
Danger: Implicit conversion is dialect-dependent and can lead to unexpected errors or slow performance (by preventing index usage).

Explicit Conversion

You tell the database exactly how to convert the data. This is safer and more readable.

Detailed Explanation: Syntax

1. The ANSI Standard: CAST

The CAST function is supported by almost all major relational databases.

CAST(expression AS target_type)

2. The Postgres Shorthand: `::`

PostgreSQL provides a concise syntax for casting.

expression::target_type

Example: Converting Messy Data

We have a LegacySales table where everything was stored as text:

ID	PriceText	SaleDateText
1	’19.99'	'2023-01-01’
2	’5.00'	'2023-01-02’

Task: Calculate the total price including a 10% tax.

SELECT
  CAST(PriceText AS DECIMAL(10,2)) * 1.1 AS PriceWithTax,
  CAST(SaleDateText AS DATE) AS ValidDate
FROM LegacySales;

Output

PriceWithTax	ValidDate
21.989	2023-01-01
5.500	2023-01-02

Under the Hood: Data Loss and Overflow

Casting is not always safe. You must be aware of the “Capacity” of your data types.

1. Truncation

If you cast a DECIMAL(10,5) (which has 5 decimal places) to an INTEGER, the database will truncate the decimal part. 10.99 becomes 10, not 11.

2. Overflow

If you try to cast the number 1,000,000 to a SMALLINT (which only goes up to 32,767), the query will fail with an Overflow Error.

3. Invalid Format

Casting the string 'Pizza' to an INTEGER will cause the entire query to crash.

Summary of Common Types

SQL Type	Use Case
`INT` / `INTEGER`	Whole numbers.
`DECIMAL(p,s)` / `NUMERIC`	Exact numbers (Money, Scientific data).
`FLOAT` / `REAL`	Approximate numbers (Large numbers where precision is less critical).
`VARCHAR(n)`	Variable length text.
`BOOLEAN`	True / False.