Ravindra BagaleCourses & study guides Track your progress

6. SQL Data Types Analysts Actually Meet

6.1 TEXT, INTEGER, REAL and NUMERIC: A Plain Guide for Analysts

By Last updated:

Why. A filter or a total can go wrong when a number is stored as text. You need to know what kind of value sits in each column.

What. SQLite stores every value as one of five storage classes: NULL, INTEGER (whole numbers), REAL (decimal numbers), TEXT and BLOB (raw bytes). A column type such as NUMERIC is only a preference, called affinity. SQLite has no separate BOOLEAN type, so yes and no are stored as 1 and 0.

How. Use the typeof() function on a column. It tells you the storage class of the value in each row.

Steps

  1. Open northstar_bank.db in DB Browser for SQLite.
  2. Open the Execute SQL tab.
  3. Run the first example and read the typeof column for each row.
  4. Run the second example to see how a yes or no flag is stored as 1 or 0.

Examples

Example 1 · dataset: northstar_bank

SELECT txn_id,
       typeof(txn_id)     AS id_type,
       typeof(amount)     AS amount_type,
       typeof(txn_type)   AS type_type,
       typeof(cleared_on) AS cleared_type
FROM transactions
WHERE txn_id IN (1, 15);

Output

txn_idid_typeamount_typetype_typecleared_type
1integerrealtexttext
15integerrealtextnull

Meaning. txn_id is an INTEGER, amount is a REAL decimal and txn_type is TEXT. Row 15 has no clearing date yet, so cleared_on is NULL.

Example 2 · dataset: northstar_bank

SELECT holder_name, kyc_done
FROM accounts
WHERE kyc_done = 0;

Output

holder_namekyc_done
Diego Alvarez0
Lucas Martin0

Meaning. These two customers have not finished KYC, which means the identity check. The flag is stored as the number 0, not as the word false.

Ravindra Bagale's Tip

Before you build a report on a new table, run typeof() on its main columns. It takes one minute and saves hours.