Adding a column to a sqlite table makes the name appear without square brackets-CodePudding

I just added a column to my table in sqlite3:

ALTER TABLE venues ADD decent BOOLEAN NOT NULL DEFAULT 0;

The result if I do: .schema venues is:

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);

So the column I just added does not have square brackets around it while all the others do.

What does this mean? Do I need to do the alter in 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.