How to Add a New Column Between Two Columns in SQLite

SQLite does not provide a position option for ALTER TABLE ... ADD COLUMN: the new column is appended after the existing columns. To put it between two columns, create a replacement table with the desired column order, copy the old data into it, then replace the original table. The example below adds phone_number between first_name and last_name in a customers table.

Why SQLite Cannot Place an Added Column Between Existing Columns

SQLite’s ALTER TABLE documentation describes ADD COLUMN; this operation adds the column at the end of the table definition. There is no FIRST or AFTER clause for choosing its position. For example, ALTER TABLE customers ADD COLUMN phone_number TEXT appends phone_number; it cannot place it between first_name and last_name.

If the new column only needs to exist, appending it is simpler and avoids rebuilding the table. When its position matters to a consumer that reads columns by ordinal position, rebuild the table. Prefer naming columns explicitly in application queries so their behavior does not depend on physical order.

Rebuild the Table with the Column in the Desired Position

Write the replacement table definition with every required column in the intended order. Preserve the original types, primary keys, nullability, defaults, uniqueness rules, and other constraints. This small example uses an in-memory database and a standalone table, so it can demonstrate the migration without external files or services.

Run this complete Python example:

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL UNIQUE)")
connection.executemany(
    "INSERT INTO customers (customer_id, first_name, last_name, email) VALUES (?, ?, ?, ?)",
    [(1, "Ada", "Lovelace", "ada@example.com"), (2, "Grace", "Hopper", "grace@example.com")],
)
connection.execute("CREATE TABLE customers_new (customer_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, phone_number TEXT NOT NULL DEFAULT '', last_name TEXT NOT NULL, email TEXT NOT NULL UNIQUE)")
connection.execute("INSERT INTO customers_new (customer_id, first_name, phone_number, last_name, email) SELECT customer_id, first_name, '', last_name, email FROM customers")
connection.execute("DROP TABLE customers")
connection.execute("ALTER TABLE customers_new RENAME TO customers")
print([column[1] for column in connection.execute("PRAGMA table_info(customers)")])
print(connection.execute("SELECT customer_id, first_name, phone_number, last_name, email FROM customers ORDER BY customer_id").fetchall())
connection.close()

['customer_id', 'first_name', 'phone_number', 'last_name', 'email']
[(1, 'Ada', '', 'Lovelace', 'ada@example.com'), (2, 'Grace', '', 'Hopper', 'grace@example.com')]

The supplied runtime run prints the column order and rows shown above. It uses an empty string for existing customers’ new phone numbers; replace that expression with an appropriate value or migration rule for your data. The table definition and both column lists are explicit so that the new column lands in the intended position and each old value maps to its matching destination column.

Copy the Existing Data into the Rebuilt Table

The INSERT INTO ... SELECT statement copies rows from the original table. Its destination list names the replacement columns, and its SELECT list supplies values in the same order. Here, phone_number has no source column, so the query supplies '' for existing rows. You can instead use a suitable default, a fixed value, or a transformation based on existing data. Decide how the new column should be populated before migrating, especially if it is required by a NOT NULL constraint.

Avoid SELECT *: the old and new tables have different column layouts. Explicit lists make the mapping clear and prevent values from shifting into the wrong columns when schemas change.

Replace the Old Table and Verify the Result

Do not drop the original table until the replacement is populated and you have a recovery plan. A production table may also have indexes, triggers, foreign-key relationships, or dependent views. Recreate the required schema objects and account for foreign-key dependencies as part of the migration; the compact example does not model them. Back up important data and perform the rebuild in a transaction appropriate to the surrounding schema.

After replacement, inspect the schema with PRAGMA table_info(customers) and verify representative rows with a SELECT that names the columns explicitly. Also check row counts and any constraints or dependent objects relevant to your application. These checks confirm that the column order changed while the original values and required schema behavior were retained.