SQLite Forum

Dot Command - Square Brackets
Login

Dot Command - Square Brackets

(1) By AAsk (aa2e72e) on 2020-09-02 14:20:44 [source]

My understanding that SQL names that

  1. are SQL keywords OR
  2. contain special character such as space or hypen
need to be wrapped in square brackets. Double quotes are sometimes used instead of square brackets.

The following work:

  1. .schema 1980-1982_F
  2. .schema "1980-1982_F"
but
  • .schema [1980-1982_F]
returns nothing i.e. fails.

Likewise for .dump

Is this inconsistency an -ism?

This statement

.schema 1980-1982_F

ought to return an error since 1980-1982_F contains special characters.

(2) By Richard Hipp (drh) on 2020-09-02 15:21:44 in reply to 1 [link] [source]

The [...] name quoting is a Sybase-ism. It is not standard SQL. SQLite does it for compatibility with (sybase-derived) MS SQL Server. The standard way to escape identifiers is double-quotes: "..."

The ".schema" command is part of the CLI, not the core SQLite database engine. The CLI does not implement the [...] Sybase-ism.

(3) By AAsk (aa2e72e) on 2020-09-02 15:26:44 in reply to 2 [link] [source]

Why does this work without double quotes?

.schema 1980-1982_F

(4) By Richard Hipp (drh) on 2020-09-02 15:49:11 in reply to 3 [link] [source]

Because the ".schema" command is not SQL. It is a CLI built-in. All of the "dot-commands" are special in that way.

I think you are confused about the difference between SQL and the dot-commands in the CLI. The dot-commands are wrappers that transform into other (rather more complex) SQL statements and/or C-apis against the SQLite core. They are not part of the language. They do not follow the same rules.