SQL data types and CAST: numbers, text, dates

#Why it matters

SQL data types and CAST decide whether a revenue total reconciles, whether a date filter works and whether a customer ID survives a round trip through Python. Data engineer postings (SQL in 80% of those we analysed) ask for "loading and validating data", and most loading bugs are type bugs: a date read as text, an ID turned into a float, a phone number that lost its leading zero. This lesson finds each of them in a real retail file and fixes them.

#The concept

#Numbers

Floating point cannot represent most decimal fractions exactly. 0.1 + 0.2 in double is 0.30000000000000004. On one row it does not matter; across millions of rows the errors add up, and worse, the total depends on the order the rows were added, so two runs of the same query can disagree in the last digits. Money goes in decimal. decimal(12, 2) holds up to 9,999,999,999.99, enough for most invoice lines in rupees; pick the scale your source actually uses (this lesson's data needs three decimal places).

#Integer division

When both sides are integers, many databases do integer division:

The fix is to make one side non-integer: 5 / 2.0, 5 * 1.0 / 2, or cast(5 as decimal) / 2. Watch for it in percentages: count_card / count_all * 100 is 0 in PostgreSQL for every row where count_card < count_all.

#Text

Store codes and identifiers that look like numbers (phone numbers, PIN codes, GSTINs, stock codes, invoice numbers) as text. You never do arithmetic on them, and numbers drop leading zeros: 0712345678 becomes 712345678.

#Dates and times

timestamptz does not store the zone you gave it. It converts your input to UTC and converts back to whatever timezone the client session uses. For events from a website or app used across time zones, use timestamptz; for a "local business date" that has no zone, timestamp or date. The date functions lesson covers converting UTC to IST.

#CAST, ::, TRY_CAST

• cast(x as integer) is standard SQL and works everywhere.

• x::integer is shorthand in PostgreSQL and DuckDB.

• A failed cast stops the whole query. try_cast(x as integer) (DuckDB, SQL Server, Snowflake) and safe_cast(x as int64) (BigQuery) return NULL instead. PostgreSQL has no try_cast; you filter with a regex first or write a small function.

What counts as "failed" differs: DuckDB will cast the text '17850.0' to integer 17850; PostgreSQL and SQL Server refuse.

#Implicit casts

Databases sometimes convert types for you, and that is where surprises live:

• Comparing text: '10' < '9' is TRUE, because text compares character by character and '1' sorts before '9'. Dates stored as text like 1/10/2011 sort the same broken way.

• MySQL converts text to numbers when you compare with a number: 10 = '10abc' is TRUE, with only a warning.

• Comparing a text column to a number in PostgreSQL is an error, which is the friendlier behaviour.

#Booleans

PostgreSQL, DuckDB, BigQuery and Snowflake have a real boolean. MySQL's boolean is an alias for tinyint(1) (so true is just 1). SQL Server uses bit. Portable code compares flags to 1/0 or true/false explicitly.

#Rounding

round, ceil (ceiling in SQL Server) and floor are everywhere, but rounding halves differs by type: PostgreSQL rounds numeric half away from zero (round(2.5) is 3) and rounds double precision half to even, "banker's rounding" (round(2.5::float8) is 2). Python's round(2.5) is also 2. Round money as decimal, and round once, at the end.

#Worked example: typing the UCI Online Retail data

The UCI Online Retail dataset (Daqing Chen, UCI Machine Learning Repository, CC BY 4.0) has 541,909 invoice lines from a UK online gift shop between December 2010 and December 2011; the same transactions form the 2010-2011 sheet of Online Retail II. A CSV copy lives in the Spark: The Definitive Guide repository:

curl -LO https://raw.githubusercontent.com/databricks/Spark-The-Definitive-Guide/master/data/retail-data/all/online-retail-dataset.csv

#Step 1: see what a loader guesses

pandas, the most common loader in Python, reads it like this:

import pandas as pd
df = pd.read_csv("online-retail-dataset.csv", nrows=200000)
print(df.dtypes)
print(df[["InvoiceNo", "CustomerID", "InvoiceDate"]].head(3))

InvoiceNo          str
StockCode          str
Description        str
Quantity         int64
InvoiceDate        str
UnitPrice      float64
CustomerID     float64
Country            str
dtype: object
  InvoiceNo  CustomerID     InvoiceDate
0    536365     17850.0  12/1/2010 8:26
1    536365     17850.0  12/1/2010 8:26
2    536365     17850.0  12/1/2010 8:26

Customer 17850 became 17850.0. pandas uses a float for any integer column with missing values (NaN is a float), and many rows have no customer. Write that DataFrame back to a database and the ID column is a float, joins to a customers table fail, and exports show 17850.0. InvoiceDate stayed text.

DuckDB's CSV sniffer does better on the ID but also leaves the date as text:

select typeof(InvoiceNo), typeof(CustomerID), typeof(InvoiceDate), typeof(UnitPrice)
from 'online-retail-dataset.csv' limit 1;

┌───────────────────┬────────────────────┬─────────────────────┬───────────────────┐
│ typeof(InvoiceNo) │ typeof(CustomerID) │ typeof(InvoiceDate) │ typeof(UnitPrice) │
│      varchar      │      varchar       │       varchar       │      varchar      │
├───────────────────┼────────────────────┼─────────────────────┼───────────────────┤
│ VARCHAR           │ BIGINT             │ VARCHAR             │ DOUBLE            │
└───────────────────┴────────────────────┴─────────────────────┴───────────────────┘

InvoiceNo is text, correctly: cancellations are numbered like C536379.

#Step 2: text dates sort wrongly

select min(InvoiceDate), max(InvoiceDate) from 'online-retail-dataset.csv';

┌──────────────────┬──────────────────┐
│ min(InvoiceDate) │ max(InvoiceDate) │
│     varchar      │     varchar      │
├──────────────────┼──────────────────┤
│ 1/10/2011 10:04  │ 9/9/2011 9:52    │
└──────────────────┴──────────────────┘

The "earliest" invoice is 10 January 2011 and the "latest" 9 September 2011. Both are wrong: the data runs from December 2010 to December 2011. Text comparison sorts 1/... before 12/... before 9/.... Any where InvoiceDate >= '6/1/2011' filter on this column is nonsense.

#Step 3: build a typed table

Parse the date with an explicit format (month first, as the source is American-style; the date functions lesson covers ambiguous formats), keep IDs as integers, and store price as an exact decimal. First check how many decimal places prices use:

select max(length(split_part(cast(UnitPrice as varchar), '.', 2))) as max_decimals
from 'online-retail-dataset.csv';

┌──────────────┐
│ max_decimals │
│    int64     │
├──────────────┤
│            3 │
└──────────────┘

Four lines have a price of 0.001. With decimal(10, 2) they would round to 0.00, so use three decimal places:

-- run with: duckdb retail.duckdb
create or replace table raw_lines as select * from read_csv('online-retail-dataset.csv');

create or replace table lines as
select InvoiceNo                               as invoice,
       StockCode                               as stock_code,
       Description                             as description,
       Quantity                                as quantity,
       strptime(InvoiceDate, '%m/%d/%Y %H:%M') as invoice_ts,
       cast(UnitPrice as decimal(10, 3))       as unit_price,
       CustomerID                              as customer_id,
       Country                                 as country
from raw_lines;

select min(invoice_ts), max(invoice_ts) from lines;

┌─────────────────────┬─────────────────────┐
│   min(invoice_ts)   │   max(invoice_ts)   │
│      timestamp      │      timestamp      │
├─────────────────────┼─────────────────────┤
│ 2010-12-01 08:26:00 │ 2011-12-09 12:50:00 │
└─────────────────────┴─────────────────────┘

Now the range is right. The rest of this shelf uses this retail.duckdb file and its lines table, with snake_case column names you never need to quote.

#Step 4: revenue as DOUBLE vs DECIMAL

select sum(Quantity * UnitPrice) as revenue_double from raw_lines;
select sum(quantity * unit_price) as revenue_decimal from lines;

┌───────────────────┐
│  revenue_double   │
│      double       │
├───────────────────┤
│ 9747747.933998778 │
└───────────────────┘
┌─────────────────┐
│ revenue_decimal │
│  decimal(38,3)  │
├─────────────────┤
│     9747747.934 │
└─────────────────┘

Rounded to the penny (or paisa), both say 9,747,747.93. The double is off by about 0.0000012, which looks harmless until you reconcile:

select sum(Quantity * UnitPrice) = 9747747.934 as double_equals_exact,
       sum(Quantity * cast(UnitPrice as decimal(10,3))) = 9747747.934 as decimal_equals_exact
from raw_lines;

┌─────────────────────┬──────────────────────┐
│ double_equals_exact │ decimal_equals_exact │
│       boolean       │       boolean        │
├─────────────────────┼──────────────────────┤
│ false               │ true                 │
└─────────────────────┴──────────────────────┘

And the double total depends on the order of addition:

select list_sum(list(v order by v))      as asc_list,
       list_sum(list(v order by v desc)) as desc_list
from (select Quantity * UnitPrice as v from raw_lines);

┌───────────────────┬───────────────────┐
│     asc_list      │     desc_list     │
│      double       │      double       │
├───────────────────┼───────────────────┤
│ 9747747.933995992 │ 9747747.933997504 │
└───────────────────┴───────────────────┘

Same numbers, different order, different total. A parallel database adds rows in a different order on each run, so a "revenue = finance's figure" test written against a double can pass on Monday and fail on Tuesday. With decimal, the sum is exact and stable.

#Step 5: integer division and other dialect traps

-- PostgreSQL 17
select 5/2 as int_div, 5/2.0 as numeric_div, 5::numeric/2 as cast_div;
select '10' < '9' as text_compare, 10 < 9 as number_compare;
select cast('17850.0' as integer);

 int_div |    numeric_div     |      cast_div
---------+--------------------+--------------------
       2 | 2.5000000000000000 | 2.5000000000000000
 
 text_compare | number_compare
--------------+----------------
 t            | f
 
ERROR:  invalid input syntax for type integer: "17850.0"

The same cast in SQL Server 2022 with try_cast, and in DuckDB:

-- SQL Server
select try_cast('17850.0' as int) as try_int, try_cast('17850.0' as float) as try_float;
-- DuckDB
select try_cast('17850.0' as integer) as try_int, '17850.0'::double::bigint as shorthand;

try_int try_float
------- ---------
NULL 17850.0
 
┌─────────┬───────────┐
│ try_int │ shorthand │
│  int32  │   int64   │
├─────────┼───────────┤
│   17850 │     17850 │
└─────────┴───────────┘

SQL Server returns NULL (not an integer string), DuckDB accepts it. The safe pattern everywhere is to cast through a decimal or double first, then to integer.

MySQL 8.4 shows its loose side:

select 5/2 as division, 5 div 2 as int_div, '10' < '9' as text_compare, 10 = '10abc' as implicit;
show warnings;

division	int_div	text_compare	implicit
2.5000	2	1	1
Level	Code	Message
Warning	1292	Truncated incorrect DOUBLE value: '10abc'

And varchar truncation depends on sql_mode:

create table codes (code varchar(5));
set session sql_mode = '';              -- strict mode off, as on some old servers
insert into codes values ('ABCDEFG');
select code from codes;                 -- ABCDE: silently cut
set session sql_mode = default;         -- STRICT_TRANS_TABLES is in the default
insert into codes values ('ABCDEFG');

code
ABCDE
ERROR 1406 (22001) at line 1: Data too long for column 'code' at row 1

#Step 6: time zones and booleans

-- PostgreSQL: one input, two types, two session time zones
create temp table ts_demo (as_timestamp timestamp, as_timestamptz timestamptz);
insert into ts_demo values ('2021-01-15 10:00:00+05:30', '2021-01-15 10:00:00+05:30');
set timezone = 'UTC';           select * from ts_demo;
set timezone = 'Asia/Kolkata';  select * from ts_demo;

    as_timestamp     |     as_timestamptz
---------------------+------------------------
 2021-01-15 10:00:00 | 2021-01-15 04:30:00+00
 
    as_timestamp     |      as_timestamptz
---------------------+---------------------------
 2021-01-15 10:00:00 | 2021-01-15 10:00:00+05:30

timestamp threw away the +05:30 and kept the wall clock. timestamptz stored the instant (04:30 UTC) and displays it in whatever zone the session asks for.

MySQL's boolean is an integer in disguise:

create table flags (is_active boolean);
show create table flags\G

Create Table: CREATE TABLE `flags` (
  `is_active` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

#Step 7: rounding

-- PostgreSQL
select round(2.5::numeric) as numeric_round, round(2.5::float8) as float_round,
       round(3.5::float8) as float_round_35;

 numeric_round | float_round | float_round_35
---------------+-------------+----------------
             3 |           2 |              4

The double rounds half to even (2.5 to 2, 3.5 to 4). DuckDB and SQL Server round both to 3. If a finance team's spreadsheet and your query disagree by one paisa on some rows, check the type before anything else.

#Common mistakes

• Money in float or double. Totals drift and depend on row order. Use decimal with the scale your source needs.

• Percentages from integer division. card_trips / all_trips is 0 in PostgreSQL and SQL Server. Multiply by 1.0 or cast first.

• Dates kept as text. They sort and filter wrongly. Parse them once, at load time, with an explicit format.

• IDs and phone numbers as numbers. Leading zeros vanish, and pandas turns integer IDs with gaps into floats. Read them as text (dtype={"CustomerID": "Int64"} or str in pandas) and keep them as text or bigint in SQL.

• decimal(10, 2) without checking the data. Prices of 0.001 became 0.00. Measure the scale first.

• Trusting MySQL to complain. With strict mode off, too-long strings are cut and bad numbers become 0. Check @@sql_mode on any server you inherit.

#Interview angle

#Practice task

On retail.duckdb:

1. Count how many lines have no customer_id. Explain what pandas would do to that column and how to prevent it.

2. Compute revenue per country as decimal and as double, and find any country where the two disagree after rounding to 2 decimal places.

3. Compute the share of lines that are cancellations (invoice starting with C) as a percentage, in DuckDB and in PostgreSQL, and make sure neither returns 0 because of integer division.

4. Store a phone number '0712345678' as text and as bigint and show what happens.

-- 1
select count(*) filter (where customer_id is null) as no_customer from lines;

-- 2
select country,
       round(sum(quantity * unit_price), 2)                     as rev_decimal,
       round(sum(quantity * cast(unit_price as double)), 2)     as rev_double
from lines
group by country
having round(sum(quantity * unit_price), 2) <> round(sum(quantity * cast(unit_price as double)), 2);

-- 3 (works in both; count(*) is an integer, so multiply by 100.0 first)
select 100.0 * count(*) filter (where invoice like 'C%') / count(*) as cancel_pct from lines;

-- 4
select '0712345678' as phone_text, cast('0712345678' as bigint) as phone_number;

#Further reading

• PostgreSQL: numeric types: exact vs floating types and rounding rules.

• PostgreSQL: date/time types: how timestamptz stores and displays instants.

• DuckDB: typecasting: explicit, implicit and try_cast rules in DuckDB.

• MySQL: strict SQL mode: what strict mode changes for inserts.

• What every programmer should know about floating-point arithmetic: a short, clear explanation of why 0.1 + 0.2 is not 0.3.