Ispirer Ispirer
Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page - Migration to PostgreSQL Request SQLWays

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

Quoted or Delimited Identifiers

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


Ispirer Home Page Ispirer SQLWays Product Page - Migration to PostgreSQL Request SQLWays

sqlways/postgresql/identifiers.txt · Last modified: March 20, 2013, 04:07:28 AM (external edit)
 
Ispirer Ispirer © 1999-2017, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement