Double Quotes vs. Single Quotes

If you get errors like this:

$ sqlite3 --init hw10.sql
-- Loading resources from hw10.sql
...
Parse error near line **: no such column: "***" - should this be a string literal in single-quotes?
...

Or, you are curious about why the handout and the code use single quotes instead of double quotes to create string literals, you should read this section.

You may notice that the slides use double-quotes to create string literals, For example, the "Berkeley" in the following code.

CREATE TABLE cities AS
    SELECT 38 AS latitude, 122 AS longitude, "Berkeley" AS name;

But in the handout and code, we always use single quotes to create string literals. In fact, double-quoted string literals are considered a bad practice in SQL.

The SQL standard requires double-quotes around identifiers and single-quotes around string literals. For example:

• "this is a legal SQL column name"
• 'this is an SQL string literal'

Since the widely-used database "MySQL" supports double-quoted string literals 😈, SQLite also accepts double-quoted string literals for compatibility.

However, starting from SQLite 3.41.0 (2023-02-21), SQLite no longer accepts double-quoted string literals in the CLI by default. So, if you are using the sqlite3 CLI, please use single quotes to create string literals. You can refer to Quirks, Caveats, and Gotchas In SQLite for more information.

Anyway, we recommend always using single quotes for string literals in SQL.