PostgreSQL Identifiers

This article describes identifiers in PostgreSQL.

Identifier Rules

PostgreSQL - Identifier Rules
First Character	An alphabetic character and underscore (_)
Subsequent Characters	Alphanumeric characters, underscore (_) and dollar sign ($)
Maximum Length	63 characters Maximum length is defined by NAMEDATALEN-1, longer names can be used in scripts but they are truncated. By default, NAMEDATALEN is 64 and it can changed in src/include/pg_config_manual.h
Case Sensitivity	Identifiers (not quoted) are case insensitive
Standards	ANSI SQL - $ is not allowed

Identifiers - Differences between PostgreSQL and other Databases

Database	Identifiers	First Character	Subsequent Characters	Max Length
SQL Server	SQL Server Identifiers	Allowed: @ and #	Allowed: @ and #	128
Oracle	Oracle Identifiers	Not allowed: _	Allowed: #	30
IBM DB2	IBM DB2 Identifiers	Not allowed: _	Not allowed: $	128
MySQL	MySQL Identifiers	Allowed: digit Not allowed: _	No differences	64
Sybase ASE	Sybase ASE Identifiers	Allowed: @ and #	Allowed: @ and #	255
Sybase ASA	Sybase ASA Identifiers	Allowed: $, @ and #	Allowed: @ and #	128
Informix	Informix Identifiers	No differences	No differences	128
Teradata	Teradata Identifiers	Allowed: $ and #	Allowed: #	30
HP Neoview	HP Neoview Identifiers	Not allowed: _	Not allowed: $	128
Interbase/Firebird	Interbase/Firebird Identifiers	Not allowed: _	No differences	67

PostgreSQL - Quoted Identifier Rules
Quotation Marks	Double quotation marks (“”)
Characters	Any character except the character with code zero. 2 double quotes can be used to include a double quote
Case Sensitivity	Case sensitive
Notes	Unquoted identifiers are always converted to lower case, so the identifiers ID, id, and “id” are considered the same
Standards	ANSI SQL - Unquoted identifiers should be converted to upper case

Quoted Identifiers - Differences between PostgreSQL and other Databases

Database	Identifiers	Quotation Marks	Characters	Case Sensitivity
Oracle	Oracle Quoted Identifiers	“”	Any except quotes	Case sensitive
SQL Server	SQL Server Quoted Identifiers	[] and “”	Any except quotes	Case insensitive
IBM DB2	IBM DB2 Quoted Identifiers	“”	Any including quotes	Case sensitive
MySQL	MySQL Quoted Identifiers	`` and “”	Any except quotes	Case sensitive
Sybase ASE	Sybase ASE Quoted Identifiers	[] and “”	Any except pound sign (#) as the first character	Case insensitive
Sybase ASA	Sybase ASA Quoted Identifiers	[] and “”	Any	Case insensitive
Informix	Informix Quoted Identifiers	“”	Any including quotes	Case sensitive
Teradata	Teradata Quoted Identifiers	“”	Any including quotes	Case insensitive
HP Neoview	HP Neoview Quoted Identifiers	“”	Any except at sign (@), forward slash (/), circumflex character (^) as the first character and space(s) as the last character(s)	Case sensitive
Interbase/Firebird	Interbase/Firebird Quoted Identifiers	“”	Any character except space(s) as the last character(s)	Case sensitive