Resolving Column Mismatch Errors When Inserting into Tables with Generated Columns and INTEGER PRIMARY KEY

Column Mismatch During INSERT-SELECT Operations Involving Generated Columns and Auto-Incrementing Keys

Issue Overview: Mismatched Column Counts in INSERT-SELECT Statements

The core problem arises when attempting to populate a table containing generated columns (specifically STORED generated columns) and an INTEGER PRIMARY KEY column using an INSERT INTO ... SELECT statement. The error message table has X columns but Y values were supplied occurs even when the generated column and primary key are intentionally excluded from the SELECT clause. This is a direct result of SQLite’s handling of column lists during insert operations and the interaction between auto-incrementing keys, generated columns, and implicit column binding.

A typical scenario involves a table structure like this:

CREATE TABLE meter_readings (
  id INTEGER PRIMARY KEY,
  date TEXT NOT NULL,
  time TEXT NOT NULL,
  jhb_meter_reading INTEGER,
  check_meter_reading INTEGER,
  notes TEXT,
  julian_date REAL GENERATED ALWAYS AS (julianday(date || time)) STORED
);

When inserting data from another table using:

INSERT INTO meter_readings 
SELECT date, time, jhb_meter_reading, check_meter_reading, notes 
FROM source_table;

The error table meter_readings has 6 columns but 5 values were supplied appears. This occurs because SQLite interprets the INSERT operation as an attempt to populate all non-autoincrement columns unless explicitly directed otherwise. The id column (INTEGER PRIMARY KEY) is auto-filled, but the julian_date generated column is still treated as a regular column in the absence of explicit column targeting. This creates a mismatch: the SELECT provides five values, but SQLite expects six (five normal columns + one generated column).

Possible Causes: Implicit Column Mapping and Version Compatibility

Three primary factors contribute to this error:

1. Implicit Column List Assumptions in INSERT-SELECT
   When the target column list is omitted in an INSERT statement (e.g., INSERT INTO table SELECT ...), SQLite assumes the SELECT provides values for all columns except the INTEGER PRIMARY KEY. Generated columns are not automatically excluded from this expectation, even though they are designed to be computed internally. This leads to a column count mismatch if the generated column is not explicitly omitted from the target column list.

2. INTEGER PRIMARY KEY Misinterpretation
   Developers often assume that omitting the id column (INTEGER PRIMARY KEY) in the SELECT clause is sufficient, as SQLite auto-populates it. However, the id column’s auto-increment behavior does not exempt other columns (including generated columns) from being included in the implicit column list. The generated column julian_date is still counted as a required column unless explicitly excluded via an explicit INSERT column list.

3. SQLite Version and Generated Column Support
   Generated columns were introduced in SQLite 3.31.0 (2020-01-22). If the database or application uses an older version, the GENERATED clause is treated as a syntax error or ignored, causing the column to behave as a regular column. This forces the user to supply values for it, which may not have been accounted for in the SELECT statement. The original poster confirmed using SQLite 3.43.1, ruling out version incompatibility, but this remains a critical consideration in other environments.

Troubleshooting Steps, Solutions & Fixes

Step 1: Validate Column Counts and Table Structure

Confirm the actual number of columns in the target table using:

PRAGMA table_info(meter_readings);

This returns metadata for all columns, including whether they are generated or auto-incrementing. Verify that the julian_date column is correctly defined as GENERATED ... STORED and that the id column is marked as INTEGER PRIMARY KEY.

Step 2: Explicitly Define Target Columns in INSERT Statements

Rewrite the INSERT statement to explicitly list the target columns, excluding both the id (auto-increment) and julian_date (generated):

INSERT INTO meter_readings 
  (date, time, jhb_meter_reading, check_meter_reading, notes)
SELECT 
  date, time, jhb_meter_reading, check_meter_reading, notes 
FROM source_table;

This explicitly maps the five SELECT columns to the five non-generated, non-autoincrement columns in the target table. SQLite will auto-populate id and compute julian_date from the provided date and time.

Step 3: Handle Generated Columns in Schema Migrations

When copying data between tables with identical schemas (e.g., meter_readings1 to meter_readings2), ensure generated columns are not included in the SELECT list. For example:

-- Correct approach: Exclude 'id' and 'julian_date' in both INSERT and SELECT
INSERT INTO meter_readings2 
  (date, time, jhb_meter_reading, check_meter_reading, notes)
SELECT 
  date, time, jhb_meter_reading, check_meter_reading, notes 
FROM meter_readings1;

Step 4: Verify SQLite Version Compatibility

Ensure the SQLite version supports generated columns:

SELECT sqlite_version();

For versions <3.31.0, generated columns are unsupported. Upgrade SQLite or redefine the column as a regular column with triggers to simulate the behavior.

Step 5: Debugging Column Mismatches with Temporary Simplification

If the error persists, temporarily remove the generated column and INTEGER PRIMARY KEY to isolate the issue:

-- Test table without generated columns or auto-increment keys
CREATE TABLE test (
  date TEXT,
  time TEXT,
  jhb_meter_reading INTEGER,
  check_meter_reading INTEGER,
  notes TEXT
);

INSERT INTO test SELECT date, time, jhb, check, notes FROM source_table;

Gradually reintroduce the id and julian_date columns to identify the exact point of failure.

By addressing implicit column mapping assumptions, explicitly defining target columns, and validating SQLite’s capabilities, developers can avoid column count mismatches in tables with generated columns and auto-incrementing keys. This approach ensures robustness against schema changes and compatibility across SQLite versions.