2

I'm trying to add a column if the column doesn't exist already using SQLite. After looking at a ton of stack overflow questions, I believe the only way to do this in an SQL statement is to use pragma_table_info (however this is only available for sqlite v3.16 and further).

So, I'm trying to run the ALTER query if the SELECT COUNT query is > 0.

Here's my attempts, all are throwing syntax error.

Try #1

SELECT COUNT(*) AS CNTREC 
   CASE CNTREC 
      WHEN CNTREC > 0 
      THEN
         ALTER TABLE runs ADD scheduledRun BOOLEAN NOT NULL DEFAULT 0
      END
FROM pragma_table_info('runs') 
WHERE name='scheduledRun';

Try #2

SELECT COUNT(*) AS CNTREC 
   CASE  
      WHEN CNTREC > 0 
      THEN
         ALTER TABLE runs ADD scheduledRun BOOLEAN NOT NULL DEFAULT 0
      END
FROM pragma_table_info('runs') 
WHERE name='scheduledRun';

Try #3

SELECT COUNT(*) AS CNTREC FROM pragma_table_info('runs') WHERE name='scheduledRun';
CASE CNTREC WHEN CNTREC > 0 
THEN
    ALTER TABLE runs ADD scheduledRun BOOLEAN NOT NULL DEFAULT 0
END

Try #4

SELECT COUNT(*) AS CNTREC FROM pragma_table_info('runs') WHERE name='scheduledRun'
CASE CNTREC WHEN CNTREC > 0 
THEN
    ALTER TABLE runs ADD scheduledRun BOOLEAN NOT NULL DEFAULT 0
END

Try #5

SELECT COUNT(*) AS CNTREC FROM pragma_table_info('runs') WHERE name='scheduledRun'
CASE WHEN CNTREC > 0 
THEN
    ALTER TABLE runs ADD scheduledRun BOOLEAN NOT NULL DEFAULT 0
END

Anyone see what I'm doing wrong?

Docs: Case, Select, Alter

Please do not give me any Java or C++ code, I'm looking for a pure sqlite solution only.

Katie
  • 45,622
  • 19
  • 93
  • 125
  • I am not sure you will be able to do it in pure SQL Script. If SQLite supports [`ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...`](https://www.postgresql.org/docs/11/sql-altertable.html) it would be easy. You need begin/end block and IF statement to conditionally run code. Case expression works on query level(it is only text) that is not executed. – Lukasz Szozda Jun 25 '19 at 21:34
  • bummer :( I was worried it wasn't possible. I've tried `ALTER TABLE results ADD api_description IF NOT EXISTS TEXT DEFAULT ''` but still get an error `Error: duplicate column name: api_description` @LukaszSzozda – Katie Jun 25 '19 at 21:36
  • [Conditionally add columns in SQLite](https://stackoverflow.com/questions/32891563/conditionally-add-columns-in-sqlite) – Lukasz Szozda Jun 25 '19 at 21:37
  • You will need a external script to run 1) query to check metadata 2) conditionally execute DDL statement. If only SQLite supports dynamic SQL :/ – Lukasz Szozda Jun 25 '19 at 21:38
  • Thank you @LukaszSzozda, yeah I think it's not possible to do it in pure sqlite T_T – Katie Jun 25 '19 at 21:39
  • Please leave the question open, I would really like to be wrong or read about some clever workaround :) – Lukasz Szozda Jun 25 '19 at 21:40
  • @LukaszSzozda hah that is true! Hopefully one day someone will have the answer :) – Katie Jun 25 '19 at 21:43

0 Answers0