SQLite - create table if not exists

Question

What is the create table statement in SQLite meant to return?

I have observed create table if not exists returning both a 0 and 1 when the table does in fact exist. Is the return value a reliable indication of whether the table does exist or not? I would expect the statement to return a 0 if the table already exists and a 1 when it does not, similar to an insert statement.

Answer 1

Changes returns the number of affected rows. This values is meaningless for CREATE TABLE statements.

There is no easy way to determine whether the CREATE TABLE IF NOT EXISTS statement did the creation or not. You should check beforehand with PRAGMA table_info.

Answer 2

The if not exists syntax makes the command succeed even if the table already exists. It just doesn't do anything.

I'm not sure what you're referring to with "returns 1" unless you're talking about the command-line client. In that case, if you just remove the if not exists from your create statement, the command will return 1 (indicating failure) if the table exists.