PostgreSQL – Naming Conventions

PostgreSQL is one of the most advanced general-purpose object-relational database management systems, and it is open-source. PostgreSQL objects include tables, views, and columns. In this article, we will learn about the naming conventions followed in PostgreSQL.

• Names in PostgreSQL must begin with a letter (a-z) or underscore (_).
• The subsequent characters in a name can be letters, digits (0-9), or underscores.
• The names containing other characters can be formed by surrounding them with double quotes (“).  For example, Table or column names may contain other disallowed characters such as spaces, ampersands(&), etc., we can use these characters if quoted.
• PostgreSQL stores all the table and column names in lowercase. If we run a select query with uppercase against Postgres, the query will fail saying the column or the table doesn’t exist. For example: table and column names.

Postgres table and column names

• Quoting a name also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the names DESCRIPTION, description, and “description” are considered the same by Postgres, but “Description” is considered a different name.
• Double quotes can also be used to protect a name that would otherwise be taken to be an SQL keyword. Table or Column names cannot be a PostgreSQL reserved keyword, such as USER or PLACING, etc. Reserved keywords are the only real keywords; they are never allowed as identifiers unless double-quoted. For example, IN is a keyword but “IN” is considered a name. 
• Also, Postgres reserves system column names for internal use in every table, these columns are implicitly defined by the system. Therefore, these names cannot be used as names of user-defined columns.
• Names in Postgres must be unique within each type of each object. They cannot be the same as another PostgreSQL object that has the same type.
• The Postgres system uses no more than NAMEDATALEN-1 characters of a name. We can provide longer names in queries, but they will be truncated. By default, NAMEDATALEN is 32 so the maximum name length is 31 but at the time the system is built, NAMEDATALEN can be changed in src/include/postgres_ext.h.

NAMEDATALEN is a constant which defines the maximum length of database identifiers such as the names of databases, tables, columns, etc. in single-byte characters. This is used by the instance that is stored in the pg_control file and is reported by pg_controldata in the field “Maximum length of identifiers.