How can I check If column already exists to avoid ALTER TABLE in an sql script file for SQLite

Question

I am adding versioning to my database a bit later than I should, and as such I have some tables with inconsistent states. I have a table that a column was added to in Java, but not all tables are guaranteed to have that column at this point.

What I had been doing is on the first run of the program, checking if the column existed, and adding it if it did not exist.

The library (flyway.org) I am using to deal with versioning takes in a bunch of .sql files in order to set up the database. For many tables, this is simple, I just have an sql file that has "CREATE TABLE IF NOT EXISTS XXX," which means it is easily handled, those can still be run.

I am wondering if there is some way to handle these alter tables without SQLite generating an error that I haven't thought of, or if I haven't found out how to do it.

I've tried looking to see if there is a command to add a column if it doesn't exist, but there doesn't seem to be one. I've tried to find a way to handle errors in sqlite, for example running the alter table anyways, and just ignoring the error, but there doesn't seem to be a way of doing that (as far as I can tell). Does anyone have any suggestions? I want a solution 100% in a .sql script if possible.

You would have to use dynamic sql for this level of flexibility. The ALTER TABLE statement would be inside the dynamic sql. — Sean Lange
– Sean Lange, Commented Jul 12, 2016 at 19:35
Have you seen ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite? — Feneric
– Feneric, Commented Jul 12, 2016 at 19:36
@Feneric, I saw that one, it doesn't offer a solution with just sql statements though. They're recommending versioning (which is what I'm trying to do) and saying that you can do it in python/etc. — Jdban101
– Jdban101, Commented Jul 12, 2016 at 20:01
@SeanLange I'm googling dynamic sql sqlite right now, but not really seeing anything. Any more tips/a nudge in the right direction? — Jdban101
– Jdban101, Commented Jul 12, 2016 at 20:02
This was originally tagged with sql server and that is what my comment was referencing. I have no clue if you can or need to do that with sqlite. — Sean Lange
– Sean Lange, Commented Jul 12, 2016 at 20:08

MPelletier · Accepted Answer · 2016-07-12 20:21:42Z

2

There is no "IF NOT EXIST" clause for Alter Tables in SQLite, it doesn't exist.

There is a way to interrogate the database on what columns a table contains with PRAGMA table_info(table_name);. But there is no 100% SQL way to take that information and apply it to an Alter Table statement.

Maybe one day, but not today.

answered Jul 12, 2016 at 20:21

MPelletier

16.8k18 gold badges90 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I check If column already exists to avoid ALTER TABLE in an sql script file for SQLite

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related