Naming in Postgres

In both the SQL spec and various databases, many words are reserved and should not be used in identifiers (your names you assign to columns, tables, etc.). The Postgres 9 docs show a list of reserved words, a long list indeed.

In my own work, I don't want to be checking my possible new identifier names against that list all the time. Instead, I end all my SQL identifiers with an underscore. Examples:

• customer_
• first_name_
• last_name_
• city_
• state_
• zip_

This convention maintains readability without adding much to length. I've heard of people appending "_tbl " and "_fld" and so on to their identifiers to accomplish the same goal of avoiding conflicts with reserved words. But that is much harder to read, and gets old fast. The trailing underscore solves the problem, and is guaranteed to work. Section 4.1.1 of the Postgres 9 docs mention that the SQL spec will never define a keyword that starts or ends with an underscore.

Identifiers in Postgres 9 can be up to 63 characters long.

Identifiers can contain textual characters, even beyond the usual ASCII English letters, including accented characters. But personally I'd stick to ASCII letters to prevent discovery of bugs. An identifier can contain a digit, but must start with an alphabetic letter or underscore. The only allowed punctuation is underscore: No spaces, periods, quotes, etc. A dollar sign ($) is allowed by Postgres but not the SQL spec, so avoid it.

Identifiers in Postgres are case-insensitive. But for compatibility with other databases, use only lower-case letters.

You can create identifiers that contain spaces, ampersands, and other oddball characters by enclosing the identifier in quotes. But do not do this. Using quoted identifiers will cause nothing but pain over the long haul. For instance, give up the idea of having the column name be presentable as-is to a user. An app should always be responsible for taking the column name and then looking up a presentable name for display to users.