SQL data types and CAST: converting values safely

In this lesson, you will convert text into numbers, dates and flags, choose suitable types for calculations, and identify values that need attention before they reach a report.

The examples run in DuckDB. PostgreSQL differences appear alongside the relevant examples, with other database differences collected in §12.

The sample data

We continue with the shop examples from the previous lessons. Its five customers are unchanged, but a partner marketplace has supplied eight new orders. The shop has loaded every column in marketplace_orders as text.

A few details about the source matter:

• customer_ref contains the shop’s numeric customer ID. Some sellers add a leading zero, so 02 still means Ben, customer 2. The value guest means there is no customer ID.
• Prices are in rupees. Both desk lamps cost 1,800 each. One seller writes 1,800; the German seller writes 1.800, using a full stop as the thousands separator.
• Discounts are fractions: 0.10 means 10%. M-503 has an empty string, while some other orders have NULL.
• M-506 comes from the India storefront. Its date, 04/06/2026, means 4 June 2026.
• Gift-wrapping choices appear as yes, no, Y and 1.

Every SQL value has a data type. The type determines which operations are available and how SQL interprets the value.

In DuckDB, typeof() shows the type of an expression:

SELECT
    typeof(8500)              AS whole_number,
    typeof('8500')            AS quoted,
    typeof(85.50)             AS with_decimals,
    typeof(DATE '2026-06-01') AS a_date,
    typeof(true)              AS a_flag;

Here, 8500 is a number and '8500' is text containing four digits. They may look identical in a result table, but their types allow different operations. As we saw in P12, || joins text, while + adds numbers:

SELECT
    8500 + 1      AS added,
    '8500' || '1' AS joined;

The main type families are:

The merchandising team wants all marketplace orders with a unit price above 1,000 rupees. The analyst knows that unit_price is text, so they put the comparison value in quotes too.

SELECT order_ref, unit_price
FROM marketplace_orders
WHERE unit_price > '1000'
ORDER BY order_ref;

Sorting the same column follows the same rule:

SELECT order_ref, unit_price
FROM marketplace_orders
ORDER BY unit_price;

CAST(value AS type) converts a value to another data type.

DuckDB and PostgreSQL also support the shorter form, value::type. We use CAST throughout this lesson because its syntax is widely supported. Let’s convert three prices:

SELECT
    order_ref,
    unit_price,
    CAST(unit_price AS INTEGER) AS price
FROM marketplace_orders
WHERE order_ref IN ('M-501', 'M-502', 'M-505')
ORDER BY order_ref;

The values look unchanged, but price is now an INTEGER. SQL can compare, sort and calculate with it as a whole number. This conversion also accepts surrounding spaces, leading zeros and a plus sign:

SELECT
    CAST(' 750 ' AS INTEGER) AS spaces,
    CAST('0750' AS INTEGER)  AS leading_zero,
    CAST('+750' AS INTEGER)  AS plus_sign;

SELECT order_ref, unit_price
FROM marketplace_orders
WHERE CAST(unit_price AS INTEGER) > 1000
ORDER BY order_ref;

People format numbers with thousands separators, currency symbols and different decimal marks. Before removing that formatting, we need to know what it means. For M-506, the comma in 1,800 separates thousands. The analyst removes commas with replace() and then converts the result.

SELECT
    order_ref,
    unit_price,
    CAST(replace(unit_price, ',', '') AS INTEGER) AS price
FROM marketplace_orders
WHERE order_ref IN ('M-506', 'M-508')
ORDER BY order_ref;

A better approach starts with an explicit rule for acceptable input. Suppose we accept either:

• Digits with no separators, such as 1800.
• Groups of three digits separated by commas, such as 1,800.

We can check that rule with a regular expression before converting:

SELECT
    order_ref,
    unit_price,
    CASE
        WHEN regexp_matches(
            unit_price,
            '^[0-9]+$|^[0-9]{1,3}(,[0-9]{3})+$'
        )
        THEN CAST(replace(unit_price, ',', '') AS INTEGER)
    END AS price
FROM marketplace_orders
ORDER BY order_ref;

We can now review the value instead of silently assigning the wrong price. Once the seller’s formatting convention is known, we can apply a separate rule to that seller’s data. This pattern checks the format, not the numeric range. A very long string of digits could still exceed the maximum INTEGER value.

Some values containing digits should remain text. For example, postal codes, phone numbers and account codes may depend on their leading zeros:

SELECT
    CAST('018956' AS INTEGER)                 AS as_number,
    CAST(CAST('018956' AS INTEGER) AS VARCHAR) AS back_to_text;

The Singapore postal code 018956 loses its leading zero. Converting the number back to text cannot recover information that was discarded.

DuckDB provides TRY_CAST for conversions that should allow processing to continue when a value cannot be converted.

Finance wants the total order value: quantity multiplied by unit price, added across all eight orders, before discounts. The analyst uses TRY_CAST on both columns.

SELECT
    SUM(
        TRY_CAST(quantity AS INTEGER)
        * TRY_CAST(unit_price AS INTEGER)
    ) AS revenue
FROM marketplace_orders;

A report should show how much data contributed to its total. We can use the format rule from §04 and count the prices that remain unresolved:

WITH priced AS (
    SELECT
        order_ref,
        CAST(quantity AS INTEGER) AS quantity,
        CASE
            WHEN regexp_matches(
                unit_price,
                '^[0-9]+$|^[0-9]{1,3}(,[0-9]{3})+$'
            )
            THEN CAST(replace(unit_price, ',', '') AS INTEGER)
        END AS price
    FROM marketplace_orders
)
SELECT
    COUNT(*)               AS orders,
    COUNT(price)           AS priced,
    SUM(quantity * price)  AS revenue,
    COUNT(*) - COUNT(price) AS to_review
FROM priced;

The report now makes its limitation visible: seven of the eight orders contribute to the total, and one needs review. All quantities in this sample are valid. If quantities could also be missing or invalid, the report would need to check both fields before counting an order as usable.

Empty strings need a deliberate rule too. This conversion fails on M-503:

SELECT
    order_ref,
    CAST(discount AS DECIMAL(4,2)) AS discount_rate
FROM marketplace_orders
ORDER BY order_ref;

Result: A conversion error because '' cannot be converted to DECIMAL(4,2).

An empty string is text with no characters. It is not automatically a missing numeric value. If the shop’s rule is that a blank discount means “not supplied,” we can express that directly:

SELECT
    order_ref,
    discount,
    CAST(
        NULLIF(trim(discount), '')
        AS DECIMAL(4,2)
    ) AS discount_rate
FROM marketplace_orders
ORDER BY order_ref;

The expression works in three steps:

1. trim() removes surrounding spaces.
2. NULLIF(..., '') turns an empty result into NULL.
3. CAST converts the remaining text to a decimal.

This treats blanks as missing while allowing unexpected text, such as 'abc', to raise an error. Replacing the whole expression with TRY_CAST would turn both cases into NULL, hiding that distinction unless we track it separately.

The shop awards one loyalty point for every complete 100 rupees spent. An amount of 250 rupees should earn two points. The analyst divides the order amount by 100 and converts the result to INTEGER.

SELECT
    order_ref,
    CAST(quantity AS INTEGER)
        * CAST(unit_price AS INTEGER) AS amount,
    CAST(
        CAST(quantity AS INTEGER)
        * CAST(unit_price AS INTEGER)
        / 100
        AS INTEGER
    ) AS points
FROM marketplace_orders
WHERE order_ref = 'M-505';

Compare the alternatives:

SELECT
    270 / 100                 AS divided,
    CAST(270 / 100 AS INTEGER) AS cast_to_integer,
    270 // 100                AS integer_division,
    floor(270 / 100)          AS floored;

floor() rounds down to the nearest whole number. It gives the intended two points.

DuckDB’s // performs integer division for integer operands, discarding the fractional part. For these positive amounts, it produces the same whole-number result.

However, discarding a fraction and rounding down differ for negative values:

• -7 // 2 gives -3, moving towards zero.
• floor(-7 / 2) gives -4, moving downward.

Whole numbers also have limits. DuckDB’s INTEGER can hold values up to 2,147,483,647. Suppose a bulk order contains 3,000 items, each priced at 850,000 paise:

SELECT
    CAST('3000' AS INTEGER)
    * CAST('850000' AS INTEGER) AS amount_in_paise;

Result: An overflow error. The product is 2,550,000,000, which is larger than an INTEGER can hold.

Convert an operand to BIGINT before multiplying. Casting the product afterwards is too late: the multiplication has already overflowed. BIGINT holds signed values up to roughly 9.2 × 10¹⁸.

The shop plans a promotion that adds 20 percentage points to each order’s existing discount rate. A discount of 0.10 would therefore become 0.30. Any total rate above 0.30 needs approval. The analyst converts the stored rates to DOUBLE.

SELECT
    order_ref,
    discount,
    CAST(NULLIF(trim(discount), '') AS DOUBLE)
        + 0.2 AS total_discount
FROM marketplace_orders
WHERE CAST(NULLIF(trim(discount), '') AS DOUBLE)
        + 0.2 > 0.3
ORDER BY order_ref;

For rates that require exact decimal comparisons, use DECIMAL:

SELECT
    order_ref,
    discount,
    CAST(NULLIF(trim(discount), '') AS DECIMAL(4,2))
        + 0.2 AS total_discount
FROM marketplace_orders
WHERE CAST(NULLIF(trim(discount), '') AS DECIMAL(4,2))
        + 0.2 > 0.3
ORDER BY order_ref;

Only M-505 exceeds the threshold.

Missing discounts remain NULL in both queries, so those orders do not pass the filter. Treating a missing discount as zero would require a separate business rule.

In DECIMAL(p, s):

• Precision, p, is the total number of digits.
• Scale, s, is the number of digits after the decimal point.

For example, DECIMAL(4,2) holds values from -99.99 to 99.99, in steps of 0.01.

The literals in the query matter too:

SELECT
    typeof(0.2) AS literal_type,
    0.1 + 0.2 = 0.3 AS decimals_equal,
    CAST('0.1' AS DOUBLE)
        + CAST('0.2' AS DOUBLE) = 0.3 AS doubles_equal;

In DuckDB, a plain literal such as 0.2 is a decimal. PostgreSQL similarly treats ordinary decimal-point literals as NUMERIC.

The marketplace’s timestamps also arrived as text. Most use a year-first format, so the analyst tries converting the whole column:

SELECT
    order_ref,
    CAST(ordered_at AS TIMESTAMP) AS ordered_ts
FROM marketplace_orders
ORDER BY order_ref;

Result: A conversion error for M-506’s value, '04/06/2026 18:20'.

DuckDB can cast supported standard timestamp forms, but it does not accept this day-first format through this cast.

For a known text format, use strptime(text, format). Its format string tells DuckDB which parts represent the year, month, day and time.

The same text can describe two different timestamps:

SELECT
    strptime(
        '04/06/2026 18:20',
        '%d/%m/%Y %H:%M'
    ) AS day_first,
    strptime(
        '04/06/2026 18:20',
        '%m/%d/%Y %H:%M'
    ) AS month_first;

Both conversions succeed. Only the first matches the source’s intended date. DuckDB’s try_strptime() returns NULL when parsing fails.

SELECT
    order_ref,
    ordered_at,
    try_strptime(
        ordered_at,
        [
            '%Y-%m-%d %H:%M:%S',
            '%m/%d/%Y %H:%M',
            '%d/%m/%Y %H:%M'
        ]
    ) AS ordered_ts
FROM marketplace_orders
WHERE order_ref IN ('M-505', 'M-506')
ORDER BY order_ref;

Moving the day-first format earlier would fix this order, but could misread an order from a month-first source. Format order cannot resolve information that is missing from the text.

We can identify values for which both interpretations succeed but disagree:

SELECT
    order_ref,
    ordered_at,
    try_strptime(
        ordered_at,
        '%m/%d/%Y %H:%M'
    ) AS month_first,
    try_strptime(
        ordered_at,
        '%d/%m/%Y %H:%M'
    ) AS day_first
FROM marketplace_orders
WHERE try_strptime(ordered_at, '%m/%d/%Y %H:%M')
   <> try_strptime(ordered_at, '%d/%m/%Y %H:%M');

This finds ambiguous values with two different valid results. It does not find every invalid date: a failed parse produces NULL, and a comparison involving NULL does not pass this filter. Check failed parses separately.

A value such as 13/06/2026 fits only the day-first interpretation because 13 cannot be a month. A value such as 04/04/2026 produces the same date either way.

For ambiguous values, use information from the source: the storefront, file specification or seller’s documented format.

A date stored numerically as 20260604 needs its own rule. Convert it to text and parse it with %Y%m%d; casting the integer directly to DATE does not explain how its digits should be interpreted.

The gift_wrap column represents a yes-or-no choice, but the marketplace uses several spellings.

DuckDB accepts all the spellings in this sample:

SELECT
    order_ref,
    gift_wrap,
    CAST(gift_wrap AS BOOLEAN) AS gift
FROM marketplace_orders
ORDER BY order_ref;

DuckDB accepts true and false, t and f, yes and no, y and n, and 1 and 0, without regard to letter case.

These rules are database-specific. For example, PostgreSQL also accepts on, off and surrounding whitespace. DuckDB does not accept those additional forms in these text-to-Boolean casts. PostgreSQL Boolean input rules

We can count gift orders and calculate their share:

SELECT
    COUNT(*) FILTER (
        WHERE CAST(gift_wrap AS BOOLEAN)
    ) AS gift_orders,
    AVG(
        CAST(CAST(gift_wrap AS BOOLEAN) AS INTEGER)
    ) AS gift_share
FROM marketplace_orders;

The filtered count includes rows where the flag is true. For the average, converting the Boolean to INTEGER turns true into 1 and false into 0. Four ones and four zeros have an average of 0.5, or 50%. The extra conversion is necessary:

SELECT AVG(CAST(gift_wrap AS BOOLEAN)) AS gift_share
FROM marketplace_orders;

Result: An error because AVG does not accept BOOLEAN in DuckDB.

DuckDB does allow SUM of Boolean values, but PostgreSQL accepts neither SUM(BOOLEAN) nor AVG(BOOLEAN). Converting the flag to an integer makes the calculation explicit in both databases.

If some flags were NULL, AVG would exclude them. Its result would then be the share among orders with known flags. Report the number of unknown flags if they could affect the interpretation.

For values typed by people, an explicit mapping gives you control over the accepted vocabulary:

SELECT
    order_ref,
    CASE
        WHEN lower(trim(gift_wrap))
             IN ('yes', 'y', 'true', '1') THEN true
        WHEN lower(trim(gift_wrap))
             IN ('no', 'n', 'false', '0') THEN false
    END AS gift
FROM marketplace_orders
ORDER BY order_ref;

This version removes surrounding spaces, ignores case and accepts only the listed values. Anything else becomes NULL for review.

The shop’s customers.customer_id is an integer. The marketplace’s customer_ref is text. Before joining them, consider a simpler comparison:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id = '2';

The comparison works even though 2 is quoted. The database can interpret this literal as an integer because of the surrounding expression. This is an implicit conversion: a conversion the database performs without an explicit CAST.

DuckDB and PostgreSQL both accept this query. For an integer ID, writing customer_id = 2 still makes the intended type clearer.

SELECT m.order_ref, c.customer_name
FROM marketplace_orders AS m
JOIN customers AS c
    ON c.customer_id = m.customer_ref
ORDER BY m.order_ref;

In DuckDB, we can make the intended conversion explicit and allow references without a numeric value to become NULL:

SELECT
    m.order_ref,
    m.customer_ref,
    c.customer_name
FROM marketplace_orders AS m
JOIN customers AS c
    ON c.customer_id = TRY_CAST(m.customer_ref AS INTEGER)
ORDER BY m.order_ref;

M-505’s '02' becomes 2 and matches Ben.

M-507’s 'guest' becomes NULL. It cannot satisfy the equality condition, so the inner join excludes it. A left join would retain the order with a missing customer name. Here, guest is an expected marker. Unexpected invalid references should also be counted and reviewed, rather than quietly disappearing from an inner join.

We could instead convert the integer IDs to text:

SELECT
    m.order_ref,
    m.customer_ref,
    c.customer_name
FROM marketplace_orders AS m
JOIN customers AS c
    ON CAST(c.customer_id AS VARCHAR) = m.customer_ref
ORDER BY m.order_ref;

This query runs, but M-505 no longer matches. The text '02' is different from '2'. Automatic conversion also depends on the operator. DuckDB accepts the mixed-type equality comparison above, but rejects this range comparison:

SELECT order_ref, unit_price
FROM marketplace_orders
WHERE unit_price > 1000;

Result: An error because the comparison mixes VARCHAR and an integer literal without an explicit cast.

Expressions that combine values also need a common result type:

SELECT 1 AS value
UNION ALL
SELECT 'two';

DuckDB chooses text for the result column, so the first row contains the text representation of 1. PostgreSQL instead tries to interpret the untyped literal 'two' as an integer and raises an error. PostgreSQL type resolution

COALESCE and CASE also need compatible result types. For example, in DuckDB:

COALESCE(CAST(NULLIF(trim(discount), '') AS DECIMAL(4,2)), 'none')

tries to convert 'none' to a decimal when that fallback is needed, causing an error. If the result is a display label, convert the numeric value to text before combining it with a text label.

Some type problems stop a query immediately. Others produce plausible results with the wrong order, amount, date or match.

Use these clues to decide what to check:

Before writing the query, answer four questions:

1. What type does the value have now? Inspect the type rather than inferring it from the displayed value.
2. What does the value represent? Choose a type for its meaning: an amount, identifier, flag, calendar date or timestamp.
3. Which source formats are allowed? Establish the rules for separators, decimal marks, date order and flag spellings.
4. What should happen when conversion fails? Decide whether to stop, record a missing result for review, or apply a documented correction.

The principles in this lesson apply across databases, but conversion behaviour is not identical. Before moving a query, check the available types, accepted input formats, rounding rules and treatment of failed conversions.

For PostgreSQL, the main differences are:

For the integer conversion in this lesson, PostgreSQL 16 and later can use:

CASE WHEN pg_input_is_valid(unit_price, 'integer') THEN CAST(unit_price AS INTEGER) END

This checks whether the text is valid input for the integer type, including whether it fits its range. Check support before applying the same approach to other types. A regular expression alone checks the text’s shape, not whether the value fits. PostgreSQL input-validation functions

Other databases have their own important differences:

Conversion direction matters in these databases too. MySQL can compare text and numbers numerically; SQL Server and Oracle generally convert the text to the numeric type. SQLite can instead compare text when the column has text affinity, repeating the problem from §02.

A value’s type determines how SQL compares, sorts and calculates with it. Check the actual types, choose types that match the data’s meaning, and use explicit conversions where needed. Keep codes as text when their characters matter, and use suitable decimal types for amounts and rates.

Conversion also needs a rule for the source format. A successful cast can misread a price or date, while a failed TRY_CAST can quietly remove a value from a calculation. Validate formats, count unresolved values, and define rounding and missing-value rules before relying on a report. When importing files, establish these decisions once so later queries use consistent data.

Next, P14 covers JSON and semi-structured data: working with fields, nested objects and arrays, and converting their values into useful SQL types.