Tables, rows, columns and data types

Tables, rows and columns

A table holds one kind of thing: students, products, orders. A row (also called a record) is one item: one student, one product. A column (also called a field) is one detail that every row has, like name or price. The place where a row and a column meet is a cell, and it holds one value.

The list of tables, their columns and their types is called the schema. The schema is the plan; the rows are the data that follows the plan. In a spreadsheet anyone can type anything anywhere. In a database the schema decides what each column may hold.

The common SQL data types

Why does money need DECIMAL? Because REAL numbers are stored in binary and cannot hold most decimal fractions exactly. Adding 0.1 and 0.2 as REAL numbers gives 0.30000000000000004. For one bill that looks harmless; across lakhs of transactions the errors add up. Banks store money as DECIMAL, or as whole paise in an INTEGER.

SELECT 0.1 + 0.2;

How SQLite is different: type affinity

PostgreSQL and MySQL check types strictly: put text into an INTEGER column and you get an error. SQLite, the engine on this site, is relaxed. Each column has a type affinity, which is a preferred type, not a strict rule. There are five affinities: TEXT, NUMERIC, INTEGER, REAL and BLOB (raw bytes). SQLite works out the affinity from the type name you write.

• VARCHAR(50) gets TEXT affinity, and the 50 is ignored: SQLite happily stores a 200-character name.
• DECIMAL(10, 2) and NUMERIC get NUMERIC affinity: 4500 is stored as an integer, 1250.75 as a real number.
• There is no separate date type. Store dates as text in the form 'YYYY-MM-DD' (the ISO 8601 format), which sorts correctly and works with SQLite's date functions.
• There is no separate boolean type. Store 1 for true and 0 for false. The words TRUE and FALSE are accepted and simply mean 1 and 0.
• An INTEGER column turns the text '25' into the number 25, but keeps the text 'abc' as text instead of rejecting it.

typeof(): what was really stored

The function typeof() returns the storage class of a value: integer, real, text, blob or null. It is SQLite-only, and very handy for spotting numbers that were saved as text.

CREATE TABLE patients (id INTEGER PRIMARY KEY, name VARCHAR(50) NOT NULL,
  admitted DATE, bill DECIMAL(10, 2), insured BOOLEAN);
INSERT INTO patients VALUES (1, 'Lata Rao', '2025-07-01', 4500, TRUE),
                            (2, 'Mohan Das', '2025-07-03', 1250.75, FALSE);
SELECT name, typeof(admitted), bill, typeof(bill), insured FROM patients;
-- Lata Rao  | text | 4500    | integer | 1
-- Mohan Das | text | 1250.75 | real    | 0

Common mistakes

• Storing dates as '05/01/2025'. Text in that form sorts wrongly: '05/01/2025' comes before '12/12/2024'. Always use 'YYYY-MM-DD', which sorts in date order.
• Storing numbers as text. As text, '9' is greater than '100', because text is compared letter by letter. Use INTEGER or DECIMAL for anything you calculate or sort by size.
• Using REAL for money. Use DECIMAL in PostgreSQL and MySQL, or whole paise in an INTEGER.
• Trusting VARCHAR(n) to limit length in SQLite. It does not. Add a CHECK rule (next topics) or check in the app.
• Forgetting that a missing value is NULL, not 0 or an empty string. typeof(NULL) is null.