Tables, columns and other objects have names, which SQL calls identifiers. A name can be written bare, or between quotes so that it can hold any character or be a word that SQL reserves.
Quoting
| Written | Used by | A quote inside the name |
|---|---|---|
"name" | Standard SQL, PostgreSQL, SQLite, and MySQL with ANSI_QUOTES | Doubled: "" |
`name` | MySQL, MariaDB, and accepted by SQLite | Doubled: `` |
[name] | SQL Server, and accepted by SQLite | A closing bracket is doubled: ]] |
CREATE TABLE "order" ("select" integer);
CREATE TABLE `order` (`select` int);
CREATE TABLE [order] ([select] int);mysqldump quotes every name with backticks. Management Studio puts every name in
brackets, including the names of types. pg_dump quotes a name only when it
must, for example when it is a reserved word or has capital letters.
Case
An unquoted name in PostgreSQL is folded to lower case, so Fruit and
fruit are the same, while "Fruit" is not. MySQL’s table names
follow the case rules of the file system on most platforms. SQL Server and SQLite do
not distinguish case in names by default.
Qualified names
A name can be preceded by the things that hold it, with dots between them.
| Form | Example |
|---|---|
| Schema and table, in PostgreSQL | public.fruit |
| Schema and table, in SQL Server | [dbo].[fruit] |
| Database and table, in MySQL | `shop`.`fruit` |
pg_dump qualifies every table with its schema and sets
search_path to empty, so the file does not depend on the session.