Check if a column exists in SQLite

Question

I need to check to see if a column exists and if it doesn't exist add it. From my research it looks like sqlite doesn't support IF statements and case statement should be used instead.

Here is what I have so far:

SELECT CASE WHEN exists(select * from qaqc.columns where Name = "arg" and Object_ID = Object_ID("QAQC_Tasks")) = 0 THEN ALTER TABLE QAQC_Tasks ADD arg INT DEFAULT(0); 

But I get the error: Near "ALTER": Syntax error.

Any ideas?

Answer 1

You cannot use ALTER TABLE withcase.

You are looking for getting the column names for a table::-

PRAGMA table_info(table-name); 

Check this tutorial on PRAGMA

This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

Answer 2

Althought this is an old question, I found at PRAGMA functions a simpler solution:

SELECT COUNT(*) AS CNTREC FROM pragma_table_info('tablename') WHERE name='column_name' 

If the result is more than zero then the column exists. Simple and one line query

The trick is to use

pragma_table_info('tablename') 

instead of

PRAGMA table_info(tablename) 

Edit: Please note that, as reported in PRAGMA functions:

This feature is experimental and is subject to change. Further documentation will become available if and when the table-valued functions for PRAGMAs feature becomes officially supported.

The table-valued functions for PRAGMA feature was added in SQLite version 3.16.0 (2017-01-02). Prior versions of SQLite cannot use this feature.