This is an old revision of the document!

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

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 Identifiers	“”	Any including quotes	Case sensitive
MySQL	MySQL Identifiers	`` and “”	Any except quotes	Case sensitive
Sybase ASE	Sybase ASE Quoted Identifiers	[] and “”	Any except quotes	Case insensitive