What SQLite column name can be/cannot be?

Question

Is there any rule for the SQLite's column name?

• Can it have characters like '/'?
• Can it be UTF-8?

Answer 1

Can it have characters like '/'?

All examples are from SQlite 3.5.9 running on Linux.

If you surround the column name in double quotes, you can:

> CREATE TABLE test_forward ( /test_column INTEGER ); SQL error: near "/": syntax error > CREATE TABLE test_forward ("/test_column" INTEGER ); > INSERT INTO test_forward("/test_column") VALUES (1); > SELECT test_forward."/test_column" from test_forward; 1 

That said, you probably shouldn't do this.

Answer 2

The following answer is based on the SQLite source code, mostly relying on the file parse.y (input for the lemon parser).

TL;DR:

The allowed series of characters for column and table names in CREATE TABLE statements are

• '-escaped strings of any kind (even keywords)
• Identifiers, which means
  • ``` and "-escaped strings of any kind (even keywords)
  • a series of the MSB=1 8-bit ASCII characters or 7-bit ASCII characters with 1 in the following table that doesn't form a keyword:
• Keyword INDEXED because it's non-standard
• Keyword JOIN for reason that is unknown to me.

The allowed series of characters for result columns in a SELECT statement are

• Either a string or an identifier as described above
• All of the above if used as a column alias written after AS

Now to the exploration process itself

1. let's look at the syntax for CREATE TABLE columns

   // The name of a column or table can be any of the following: // %type nm {Token} nm(A) ::= id(X).         {A = X;} nm(A) ::= STRING(X).     {A = X;} nm(A) ::= JOIN_KW(X).    {A = X;} 

2. digging deeper, we find out that

   // An IDENTIFIER can be a generic identifier, or one of several // keywords.  Any non-standard keyword can also be an identifier. // %type id {Token} id(A) ::= ID(X).         {A = X;} id(A) ::= INDEXED(X).    {A = X;} 

   "Generic identifier" sounds unfamiliar. A quick look into tokenize.c however brings forth the definition

   /* ** The sqlite3KeywordCode function looks up an identifier to determine if ** it is a keyword.  If it is a keyword, the token code of that keyword is  ** returned.  If the input is not a keyword, TK_ID is returned. */  /* ** If X is a character that can be used in an identifier then ** IdChar(X) will be true.  Otherwise it is false. ** ** For ASCII, any character with the high-order bit set is ** allowed in an identifier.  For 7-bit characters,  ** sqlite3IsIdChar[X] must be 1. ** ** Ticket #1066.  the SQL standard does not allow '$' in the ** middle of identfiers.  But many SQL implementations do.  ** SQLite will allow '$' in identifiers for compatibility. ** But the feature is undocumented. */ 

   For a full map of identifier characters, please consult the tokenize.c.

3. It is still unclear what are the available names for a result-column (i. e. the column name or alias assigned in the SELECT statement). parse.y is again helpful here.

   // An option "AS <id>" phrase that can follow one of the expressions that // define the result set, or one of the tables in the FROM clause. // %type as {Token} as(X) ::= AS nm(Y).    {X = Y;} as(X) ::= ids(Y).      {X = Y;} as(X) ::= .            {X.n = 0;}