SQLite If Column Exists

Question

I was wondering if there is a nice IF NOT EXISTS for checking columns and indexes in SQLite, or do I need to bring back the entire database schema and validate against that?

Answer 1

Yes the following syntax is supported in sqlite: CREATE INDEX IF NOT EXISTS ...

See here

To check existence of a column you could simply try to do something like SELECT col from TABLE. If it does not return an error your table contains col.

Answer 2

There is a system catalog table called sqlite_master that you can use to check index (or other) names:

SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;

You can use a pragma to get the indexed columns:

PRAGMA index_info(index-name);

And this one to get the column names for a table:

PRAGMA table_info(table-name);