Data types and type conversion

Every value in SQL has a data type: a whole number, a decimal, text, a date, true or false. A column’s type decides what the database stores, how it compares and sorts the values, and what happens to input that does not fit. Type conversion turns a value of one type into another, written CAST(value AS type) in all three engines of this track. The engines agree on clean input and disagree on messy input: given the text '12abc' for a whole number, PostgreSQL stops with an error, MySQL converts what it can and warns, and SQLite either converts what it can or keeps the text as it is. This lesson shows the types to choose, how to look at a value’s type, and what each engine does with input that does not fit.

The examples below run in your browser on SQLite 3.49.1, the engine inside sql.js 1.14.2. For PostgreSQL 18 and MySQL 8.4, the tables summarise what their manuals specify.

The types you will use most

Three choices cover most tables. Counts and identifiers that you do arithmetic on go in an integer type: bigint when the values can pass about 2.1 billion, the limit of a 4-byte integer. Money goes in an exact type, numeric or DECIMAL, or as whole paise in an integer column, never in a floating-point type; the next lesson shows why. Codes that only look like numbers, such as phone numbers, postal codes and account numbers, go in a text column, because nobody adds them up and their leading zeros matter.

True and false are the odd ones out. Only PostgreSQL has a real boolean type. In MySQL BOOLEAN is another name for a one-byte integer, and TRUE and FALSE are the numbers 1 and 0; SQLite stores the same 1 and 0 in an ordinary integer. A filter such as WHERE is_paid therefore works in all three engines, but WHERE is_paid = 'yes' does not.

Look at the type of a value

SQLite’s typeof() names the storage class of any value: integer, real, text, blob or null. PostgreSQL has pg_typeof() for the same job, and it answers with the full type name, such as integer or numeric. MySQL has no such function; SHOW CREATE TABLE shows the declared types of a table’s columns instead.

-- typeof() names the storage class of a value: integer, real, text, blob or null.
-- The column v has no declared type, so it keeps every value exactly as it was written.
CREATE TABLE samples (written TEXT, v);
INSERT INTO samples VALUES
  ('42', 42), ('4.2', 4.2), ('''42''', '42'), ('NULL', NULL), ('x''2A''', x'2A'),
  ('TRUE', TRUE), ('3 > 2', 3 > 2), ('''2026-03-31''', '2026-03-31');

SELECT written, typeof(v) AS type
FROM samples;

┌──────────────┬─────────┐
│   written    │  type   │
├──────────────┼─────────┤
│ 42           │ integer │
│ 4.2          │ real    │
│ '42'         │ text    │
│ NULL         │ null    │
│ x'2A'        │ blob    │
│ TRUE         │ integer │
│ 3 > 2        │ integer │
│ '2026-03-31' │ text    │
└──────────────┴─────────┘

Two results are worth a second look. TRUE and the result of a comparison are integers, because SQLite has no separate boolean class. And '2026-03-31' is text: SQLite has no date type at all, and its date functions work on text written in that year-month-day form, as the lesson on dates explains.

Declared types in SQLite are preferences

In PostgreSQL and MySQL a column’s type is a rule. In an ordinary SQLite table it is a preference, called the column’s type affinity. SQLite converts an incoming value to the preferred class when nothing is lost, and otherwise stores the value as it arrived. Here the same text goes into five columns:

-- The same text goes into five columns with different declared types.
CREATE TABLE contacts (
  phone_text   TEXT,
  phone_int    INTEGER,
  phone_string STRING,         -- not a type SQLite knows: the column gets NUMERIC affinity
  price        DECIMAL(10, 2),
  qty          INTEGER
);
INSERT INTO contacts VALUES ('02226540000', '02226540000', '02226540000', '19.99', '12abc');

SELECT phone_text,   typeof(phone_text)   AS t1,
       phone_int,    typeof(phone_int)    AS t2,
       phone_string, typeof(phone_string) AS t3
FROM contacts;

SELECT price, typeof(price) AS t4,
       qty,   typeof(qty)   AS t5
FROM contacts;

┌─────────────┬──────┬────────────┬─────────┬──────────────┬─────────┐
│ phone_text  │  t1  │ phone_int  │   t2    │ phone_string │   t3    │
├─────────────┼──────┼────────────┼─────────┼──────────────┼─────────┤
│ 02226540000 │ text │ 2226540000 │ integer │ 2226540000   │ integer │
└─────────────┴──────┴────────────┴─────────┴──────────────┴─────────┘
┌───────┬──────┬───────┬──────┐
│ price │  t4  │  qty  │  t5  │
├───────┼──────┼───────┼──────┤
│ 19.99 │ real │ 12abc │ text │
└───────┴──────┴───────┴──────┘

The phone number keeps its leading zero only in the TEXT column. INTEGER turned it into a number, and so did STRING, a type name SQLite does not know, which gives the column NUMERIC affinity. The price in the DECIMAL(10, 2) column became a REAL, an approximate floating-point number, because SQLite has no exact decimal type behind that name. And '12abc' stayed text inside an INTEGER column, since converting it would lose the letters. A query that adds up qty later meets a text value in a column everybody believed held numbers.

SQLite works out the affinity from the declared type’s name, one test after another:

The tests run in order, and the first match wins, so a column declared FLOATING POINT gets INTEGER affinity because the name contains the letters INT. To keep text exactly as it is, declare it TEXT or VARCHAR(n).

STRICT tables check every value

Since version 3.37.0, a SQLite table created with the keyword STRICT after its column list checks types as PostgreSQL does. Its columns must use one of the types INT, INTEGER, REAL, TEXT, BLOB or ANY, and a value that cannot be converted without loss is refused:

-- A STRICT table checks every value against the column's type.
CREATE TABLE stock (sku TEXT, qty INTEGER) STRICT;

INSERT INTO stock VALUES ('DAL-1KG', '42');     -- text that is a clean integer: stored as 42
SELECT sku, qty, typeof(qty) AS type FROM stock;

INSERT INTO stock VALUES ('GHEE-1L', '12abc');  -- refused

┌─────────┬─────┬─────────┐
│   sku   │ qty │  type   │
├─────────┼─────┼─────────┤
│ DAL-1KG │ 42  │ integer │
└─────────┴─────┴─────────┘

Runtime error near line 7: cannot store TEXT value in INTEGER column stock.qty

The text '42' was accepted and stored as the integer 42, because the conversion is exact. The second INSERT failed with an error and stored nothing. New SQLite tables are worth declaring STRICT unless you need the flexible behaviour; the only cost is that names such as DECIMAL or DATE are not allowed, so dates go in TEXT and money in INTEGER.

Converting values with CAST

CAST(expression AS type) converts a value explicitly. It is standard SQL and works in all three engines; PostgreSQL also accepts its own shorter form, expression::type. Here is what SQLite makes of eleven inputs:

-- What CAST makes of messy input in SQLite. The table only holds the inputs.
CREATE TABLE inputs (label TEXT, v);
INSERT INTO inputs VALUES
  ('clean',          '42'),
  ('padded',         ' 42 '),
  ('plus sign',      '+7'),
  ('trailing text',  '12abc'),
  ('no digits',      'abc'),
  ('decimal text',   '3.9'),
  ('number 3.9',     3.9),
  ('number -3.9',    -3.9),
  ('exponent',       '1e3'),
  ('hexadecimal',    '0x1A'),
  ('missing',        NULL);

SELECT label, quote(v) AS input,
       CAST(v AS INTEGER) AS as_integer,
       CAST(v AS REAL)    AS as_real
FROM inputs;

┌───────────────┬─────────┬────────────┬─────────┐
│     label     │  input  │ as_integer │ as_real │
├───────────────┼─────────┼────────────┼─────────┤
│ clean         │ '42'    │ 42         │ 42.0    │
│ padded        │ ' 42 '  │ 42         │ 42.0    │
│ plus sign     │ '+7'    │ 7          │ 7.0     │
│ trailing text │ '12abc' │ 12         │ 12.0    │
│ no digits     │ 'abc'   │ 0          │ 0.0     │
│ decimal text  │ '3.9'   │ 3          │ 3.9     │
│ number 3.9    │ 3.9     │ 3          │ 3.9     │
│ number -3.9   │ -3.9    │ -3         │ -3.9    │
│ exponent      │ '1e3'   │ 1          │ 1000.0  │
│ hexadecimal   │ '0x1A'  │ 0          │ 0.0     │
│ missing       │ NULL    │            │         │
└───────────────┴─────────┴────────────┴─────────┘

SQLite reads text from the left and keeps the longest prefix that forms a number, ignoring leading spaces. That is why '12abc' becomes 12, 'abc' becomes 0, the exponent in '1e3' is dropped when the target is INTEGER, and '0x1A' stops at the letter x. A real number cast to INTEGER loses its fraction and moves toward zero, so 3.9 becomes 3, and -3.9 becomes -3. Only NULL stays NULL.

The other two engines treat the same inputs differently:

PostgreSQL accepts only text that is a valid integer and stops the whole statement otherwise. MySQL converts as much as it can and records a warning, such as 1292 “Truncated incorrect INTEGER value”, which a program sees only if it asks for warnings. Its CAST also has a short list of target types: a whole number is SIGNED or UNSIGNED, and CAST(x AS INT) is a syntax error. Numbers with a fraction are rounded by PostgreSQL and MySQL but cut toward zero by SQLite, so the same query returns 4 in two engines and 3 in the third. When you need one behaviour everywhere, say it: round() or floor() first, then the cast.

When a value does not fit a column

Writing data shows the same split, with one twist in MySQL. PostgreSQL refuses an INSERT of '12abc' into an integer column with the same “invalid input syntax” error. MySQL 8.4 runs in strict mode by default (STRICT_TRANS_TABLES is part of its default SQL mode), so it refuses the row as well; without strict mode it would store 12 with a warning. Strict mode applies to writing data, not to SELECT, so a cast in a query still converts and warns. SQLite stores the text unchanged, as the declared-types example showed, unless the table is STRICT.

The safe habit is the same everywhere: check or convert data before it reaches a typed column, and in SQLite declare new tables STRICT. The exercise below builds such a check.

Comparing values of different types

A comparison between values of different types needs a conversion, and each engine has its own rules. SQLite’s depend on where the values come from:

-- The same comparison with values of different types.
CREATE TABLE stock (qty INTEGER, code TEXT);
INSERT INTO stock VALUES (10, '10');

SELECT '10' > 9  AS text_vs_number,       -- two literals: any text sorts after any number
       qty > '9'  AS integer_column,       -- the column's INTEGER affinity turns '9' into 9
       code > 9   AS text_column           -- the column's TEXT affinity turns 9 into '9'
FROM stock;

SELECT CAST(code AS INTEGER) > 9 AS cast_first
FROM stock;

┌────────────────┬────────────────┬─────────────┐
│ text_vs_number │ integer_column │ text_column │
├────────────────┼────────────────┼─────────────┤
│ 1              │ 1              │ 0           │
└────────────────┴────────────────┴─────────────┘
┌────────────┐
│ cast_first │
├────────────┤
│ 1          │
└────────────┘

Two literals have no affinity, and SQLite then orders every number before every text, so '10' > 9 is true without any arithmetic. Against an INTEGER column, the text '9' is converted to the number 9 first. Against a TEXT column, the number 9 becomes the text '9', and '10' sorts before '9' the way words do, so the comparison is false. An explicit CAST gives the answer you meant in every case.

The other engines go further in both directions. MySQL converts the text to a number for such a comparison, so a VARCHAR column compared with the number 0 matches every value that does not begin with a number, such as 'pending', and its manual notes that an index on that column cannot then be used to find the rows. PostgreSQL refuses to compare a text column with an integer at all (“operator does not exist”) until you add a cast. Compare like with like: quote the value when the column holds text, and cast deliberately when it does not.

Your turn

-- Flag each row of raw_quantities: is_clean is 1 when qty_text is an integer
-- written exactly as SQLite writes it, and 0 otherwise (NULL included).
SELECT id,
       qty_text,
       1 AS is_clean
FROM raw_quantities;

# Sample tests: each one builds a small raw_quantities table, runs your query on it and compares the rows it returns
# (in any order).
tests:
  - name: flags clean integers, text, decimals and NULL
    setup: |
      CREATE TABLE raw_quantities (id INTEGER PRIMARY KEY, qty_text TEXT);
      INSERT INTO raw_quantities VALUES (1, '42'), (2, '12abc'), (3, '3.5'), (4, '-7'), (5, NULL);
    expected:
      columns: [id, qty_text, is_clean]
      rows:
        - [1, '42', 1]
        - [2, '12abc', 0]
        - [3, '3.5', 0]
        - [4, '-7', 1]
        - [5, null, 0]
  - name: refuses spaces, plus signs and an empty string
    setup: |
      CREATE TABLE raw_quantities (id INTEGER PRIMARY KEY, qty_text TEXT);
      INSERT INTO raw_quantities VALUES (1, ' 42'), (2, '42 '), (3, '+42'), (4, '- 7'), (5, ''), (6, '0');
    expected:
      columns: [id, qty_text, is_clean]
      rows:
        - [1, ' 42', 0]
        - [2, '42 ', 0]
        - [3, '+42', 0]
        - [4, '- 7', 0]
        - [5, '', 0]
        - [6, '0', 1]
  - name: refuses leading zeros, other digits and values beyond 64 bits
    setup: |
      CREATE TABLE raw_quantities (id INTEGER PRIMARY KEY, qty_text TEXT);
      INSERT INTO raw_quantities VALUES
        (1, '007'), (2, '-0'), (3, '४२'), (4, '9223372036854775807'), (5, '9223372036854775808'), (6, '1e3');
    expected:
      columns: [id, qty_text, is_clean]
      rows:
        - [1, '007', 0]
        - [2, '-0', 0]
        - [3, '४२', 0]
        - [4, '9223372036854775807', 1]
        - [5, '9223372036854775808', 0]
        - [6, '1e3', 0]

Interview questions

Fresher · What is type affinity in SQLite, and how is it different from a column type in PostgreSQL?

In PostgreSQL a column’s type is a rule: every value in an integer column is an integer, and input that cannot be converted is an error. In an ordinary SQLite table the declared type only sets the column’s affinity, the storage class it prefers. SQLite converts a value to that class when nothing is lost, so the text '42' becomes the integer 42 in an INTEGER column, and otherwise keeps the value as it came, so '12abc' stays text. The affinity comes from the type’s name: INT gives INTEGER; CHAR, CLOB or TEXT give TEXT; BLOB or no type give BLOB; REAL, FLOA or DOUB give REAL; anything else, such as STRING or DECIMAL, gives NUMERIC. A table declared STRICT, available since SQLite 3.37.0, checks types the way PostgreSQL does.

Key takeaways

• Choose integer types for counts and identifiers you calculate with, exact types or whole paise for money, and text for codes that only look like numbers.
• typeof() in SQLite and pg_typeof() in PostgreSQL show the type of any value; use them when a result surprises you.
• An ordinary SQLite column prefers a type but accepts anything, so '12abc' can sit in an INTEGER column; STRICT tables refuse it.
• CAST(x AS type) works everywhere; PostgreSQL also has x::type, and MySQL casts to SIGNED, not INT.
• On messy text PostgreSQL raises an error, MySQL converts with a warning and SQLite converts silently; numbers with a fraction are rounded by PostgreSQL and MySQL but cut toward zero by SQLite.
• Compare values of the same type, or cast explicitly, because mixed comparisons follow different rules in each engine.