SQL Dialects & Portability

Identifiers, Quoting, and Name Resolution

Identifier portability depends on lexical rules, quoting, case treatment, qualification, and the namespaces searched—not merely changing quote characters. Prefer simple lowercase unquoted names, avoid reserved words, qualify security-sensitive objects, and test metadata round trips.

Quoted and unquoted identifiers are different contracts.
Case behavior is product and configuration dependent.
Namespace vocabularies do not align.
Qualification is a correctness and security tool.
PostgreSQL search_path can select hostile objects.
Naming policy should survive future keywords.

Five identifier and qualification forms

CREATE SCHEMA commerce;
CREATE TABLE commerce.orders (order_id bigint PRIMARY KEY, "status" text);
-- commerce must not grant CREATE to untrusted roles.
SET search_path = commerce, pg_catalog;
SELECT o.order_id FROM commerce.orders AS o;
-- Unquoted Order_ID resolves as order_id; quoted spelling is exact.

Resolution distinctions
Dialect	Delimited identifier	Ordinary-name behavior	Qualification model
PostgreSQL	"name"	unquoted folds lowercase	database → schema; `search_path` for unqualified objects
MySQL	`name` (`"name"` only with ANSI_QUOTES)	object-specific; table/database case is configured/platform-sensitive	server → database → object
SQLite	"name" (also compatibility forms)	ASCII case-insensitive lookup in normal use	connection → attached database → object
SQL Server	[name] or "name" with QUOTED_IDENTIFIER	identifier collation determines comparison	server → database → schema → object
Oracle	"name"	nonquoted names uppercase	database/container → schema(owner) → object; synonyms may participate