SQL Data Types: INT, VARCHAR, DECIMAL, DATE and More

Every column is declared with a type when the table is created. You can see the declared types of a Playground table with SQLite's pragma_table_info:

SELECT name AS column_name, type AS declared_type
FROM pragma_table_info('orders');

▶ Run this query in the SQL Playground

Integers have no fractional part. DECIMAL stores an exact number of decimal places: DECIMAL(10,2) means up to 10 digits in total, 2 of them after the point, so up to 99,999,999.99. FLOAT and REAL store approximate binary values, which causes tiny rounding errors:

SELECT 0.1 + 0.2        AS float_sum,
       0.1 + 0.2 = 0.3  AS equals_0_3,
       ROUND(0.1 + 0.2, 2) AS rounded;

▶ Run this query in the SQL Playground

The sum is not exactly 0.3, so the comparison is false (0). For money that is not acceptable: rounding errors add up over millions of rows and make totals fail to reconcile. Use DECIMAL for prices, salaries and balances.

VARCHAR(100) stores up to 100 characters and uses only the space the value needs. CHAR(100) always stores 100, padding short values with spaces, which suits fixed-length codes like country codes. TEXT holds long text.

Phone numbers are text, not numbers

A phone number is an identifier, not a quantity: you never add two of them. Stored as an integer, the formatting, the + and any leading zeros are lost. Here is what converting to INTEGER does:

SELECT phone,
       CAST(phone AS INTEGER) AS phone_as_integer,
       CAST('007' AS INTEGER) AS leading_zeros_as_integer
FROM users
LIMIT 3;

▶ Run this query in the SQL Playground

SQLite reads the number up to the first character that is not a digit, so +1-263-376-2371 becomes 1. Other databases reject the conversion or behave differently, but none keep the original value. The same applies to postal codes, account numbers and SKUs.

With a real date type the database can compare, sort and calculate with dates. SQLite has no date type: dates are text, and they only behave correctly in YYYY-MM-DD form. You can see that in the Playground:

SELECT typeof(order_date) AS stored_as,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order,
       SUM(order_date >= '2024-01-01') AS orders_in_2024
FROM orders;

▶ Run this query in the SQL Playground

Text in another format, such as 12/24/2024, would sort by month first and break range filters. Store dates in a date type, or at least in ISO format. See date functions.

PostgreSQL has a real BOOLEAN type. MySQL's BOOLEAN is TINYINT(1), SQL Server uses BIT, and SQLite stores 0 and 1 in an INTEGER column, as the Playground does with is_premium:

SELECT is_premium, COUNT(*) AS users
FROM users
GROUP BY is_premium;

▶ Run this query in the SQL Playground

SQLite is unusual: a declared type is a preference (an "affinity"), not a strict rule, and each value carries its own storage class. typeof() shows it:

SELECT typeof(id) AS id_type,
       typeof(total) AS total_type,
       typeof(status) AS status_type,
       typeof(shipped_date) AS shipped_date_type
FROM orders
WHERE id = 1;

▶ Run this query in the SQL Playground

An order that has not shipped has NULL there, which is its own storage class. MySQL, PostgreSQL and SQL Server enforce column types much more strictly: inserting 'abc' into an INT column is an error. SQLite has STRICT tables (3.37+) for the same behaviour.

Five errors and how to avoid them.

price FLOAT

price DECIMAL(10,2)

phone INT

phone VARCHAR(20)

order_date VARCHAR(20)  -- '12/24/2024'

order_date DATE

quantity VARCHAR(10)

quantity INT

email VARCHAR(30)

email VARCHAR(255)

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List the columns of the products table and their declared types. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name AS column_name, type AS declared_type
   FROM pragma_table_info('products');

   ▶ Run this query in the SQL Playground

   pragma_table_info is SQLite's way to read a table definition; other databases use information_schema.columns.

2. 2Easy Show the storage class of the salary, hire_date and manager_id values for employee 1. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT typeof(salary) AS salary_type,
          typeof(hire_date) AS hire_date_type,
          typeof(manager_id) AS manager_id_type
   FROM employees
   WHERE id = 1;

   ▶ Run this query in the SQL Playground

   Employee 1 has no manager, so manager_id is NULL.

3. 3Medium Show what happens to a SKU such as SKU-000042 when it is converted to an integer, and how to get its number correctly. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT sku,
          CAST(sku AS INTEGER) AS cast_whole_sku,
          CAST(SUBSTR(sku, 5) AS INTEGER) AS sku_number
   FROM products
   WHERE id = 42;

   ▶ Run this query in the SQL Playground

   The whole SKU starts with letters, so it converts to 0. Take the digits first with SUBSTR.

4. 4Medium Show why numbers stored as text sort wrongly: compare '10' < '9' as text with 10 < 9 as numbers. Table: (none)

   Show solution

   SQLite · runs in the Playground

   SELECT '10' < '9' AS text_comparison,
          10 < 9     AS number_comparison;

   ▶ Run this query in the SQL Playground

   As text, '10' comes before '9' because '1' is less than '9', so the first comparison is true (1). As numbers it is false (0).