I added a column to my table :
ALTER TABLE venues ADD decent BOOLEAN NOT NULL DEFAULT 0;
.schema venues
returns :
CREATE TABLE [venues] (
[id] INTEGER PRIMARY KEY,
[foursquare_id] TEXT NOT NULL,
[pick] INTEGER NOT NULL DEFAULT 0,
[foursquare_data] TEXT NOT NULL,
[lat] FLOAT NOT NULL,
[lng] FLOAT NOT NULL,
[name] TEXT NOT NULL,
[closed] INTEGER NOT NULL DEFAULT 0,
[google_data] TEXT NOT NULL,
[google_place_id] TEXT NOT NULL,
[google_name] TEXT NOT NULL
, decent BOOLEAN NOT NULL DEFAULT 0);
Column I added does not have square brackets while all others do. What does this mean? Do I need to do this some other way?
CodePudding user response:
The dot-command .schema venues
returns the sql
column of the Schema Table for the row that corresponds to the table venues
.
The equivalent SQL statement would be:
SELECT sql FROM sqlite_schema WHERE name = 'venues';
As it is explained in Interpretation Of The Schema Table:
The text in the sqlite_schema.sql column is a copy of the original CREATE statement text that created the object, except...
which means that if you used square brackets in all or some column names in the CREATE TABLE
statement then this is what you get when you execute .schema venues
.
If you add a new column, then its definition is added in the original CREATE TABLE
statement inside the sql
column as it is written in the ALTER TABLE
statement.
If you want square brackets for all the column names then use them when you add the column:
ALTER TABLE venues ADD [decent] BOOLEAN NOT NULL DEFAULT 0;
See the demo.