Sqlite – ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

alter-tablesqlite

We've recently had the need to add columns to a few of our existing SQLite database tables. This can be done with ALTER TABLE ADD COLUMN. Of course, if the table has already been altered, we want to leave it alone. Unfortunately, SQLite doesn't support an IF NOT EXISTS clause on ALTER TABLE.

Our current workaround is to execute the ALTER TABLE statement and ignore any "duplicate column name" errors, just like this Python example (but in C++).

However, our usual approach to setting up database schemas is to have a .sql script containing CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS statements, which can be executed using sqlite3_exec or the sqlite3 command-line tool. We can't put ALTER TABLE in these script files because if that statement fails, anything after it won't be executed.

I want to have the table definitions in one place and not split between .sql and .cpp files. Is there a way to write a workaround to ALTER TABLE ADD COLUMN IF NOT EXISTS in pure SQLite SQL?

Best Answer

I have a 99% pure SQL method. The idea is to version your schema. You can do this in two ways:

Use the 'user_version' pragma command (PRAGMA user_version) to store an incremental number for your database schema version.
Store your version number in your own defined table.

In this way, when the software is started, it can check the database schema and, if needed, run your ALTER TABLE query, then increment the stored version. This is by far better than attempting various updates "blind", especially if your database grows and changes a few times over the years.

Related Solutions

SQLite – UPSERT not INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Sql-server – Check if a temporary table exists and delete if it exists before creating a temporary table

I cannot reproduce the error.

Perhaps I'm not understanding the problem.

The following works fine for me in SQL Server 2005, with the extra "foo" column appearing in the second select result:

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO
CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT )
GO
select company, stepid, fieldid from #Results
GO
ALTER TABLE #Results ADD foo VARCHAR(50) NULL
GO
select company, stepid, fieldid, foo from #Results
GO
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO

Best Answer

Related Solutions

SQLite – UPSERT *not* INSERT or REPLACE

Sql-server – Check if a temporary table exists and delete if it exists before creating a temporary table

Related Topic

SQLite – UPSERT not INSERT or REPLACE