Inference vs Explicit Types

Schema Inference Versus Explicit Typing

The sniffers read a sample (20,480 rows by default) and pick the narrowest type that fits. That is convenient and sometimes wrong. JSON numbers become DOUBLE, binary floating point, so money stops adding up to the cent (Numbers and Precision); declaring the columns fixes the type, and store_rejects sets aside rows that do not fit instead of failing the whole read:

473-typing.sql: inferred versus declared types, and rejected rowsSQL

SELECT 'inferred' AS read, typeof(any_value(total)) AS type, sum(total) AS revenue
FROM read_json('data/orders.jsonl')
UNION ALL
SELECT 'declared', typeof(any_value(total)), sum(total)
FROM read_json('data/orders.jsonl', columns = {order_id: 'INTEGER', total: 'DECIMAL(10,2)'});
COPY (FROM (VALUES ('1', 'web', '2'), ('2', 'ios', 'two'), ('3', 'android', '1')))
  TO 'files/bad.csv' (HEADER false);
SELECT * FROM read_csv('files/bad.csv', columns = {order_id: 'INTEGER', channel: 'VARCHAR',
                                                   qty: 'SMALLINT'}, store_rejects = true);
SELECT line, column_name, error_type::VARCHAR AS error, csv_line FROM reject_errors;

Output

┌──────────┬───────────────┬───────────────────┐
│   read   │     type      │      revenue      │
│ varchar  │    varchar    │      double       │
├──────────┼───────────────┼───────────────────┤
│ inferred │ DOUBLE        │ 3474495.410002366 │
│ declared │ DECIMAL(10,2) │        3474495.41 │
└──────────┴───────────────┴───────────────────┘
...
│        3 │ android │     1 │
...
│      2 │ qty         │ CAST    │ 2,ios,two │

Summed as doubles, the revenue picked up 0.000002366 of rounding noise; the declared DECIMAL summed exactly (the union then cast it to DOUBLE for display). The bad CSV row went to the reject_errors table with its line number and cause, while the good rows loaded. In pipelines, declare types for the columns you rely on, keep inference for exploration, and count rejected rows as a data-quality check (Data Quality).

MENU