Conditional CREATE

IF NOT EXISTS can help us especially with migration scripts. Both when they are external to the application and when they are part of the startup code.

In this example the first statement succeeds creating the table.

The second statement fails with an error.

The third statement fails but there is no error.

CREATE TABLE IF NOT EXISTS counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);

CREATE TABLE counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);


CREATE TABLE IF NOT EXISTS counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);

.schema

$ sqlite3 < examples/conditional-create.sql

Parse error near line 6: table counters already exists
  CREATE TABLE counters (     name TEXT UNIQUE NOT NULL,     number INTEGER NOT 
               ^--- error here
CREATE TABLE counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);