PostgreSQL naming conventions

postgresql naming-conventions
boj picture boj · May 21, 2010 · Viewed 110.6k times · Source

Where can I find a detailed manual about PostgreSQL naming conventions? (table names vs. camel case, sequences, primary keys, constraints, indexes, etc...)

Answer

leonbloy picture leonbloy · May 21, 2010

Regarding tables names, case, etc, the prevalent convention is:

  • SQL keywords: UPPER CASE
  • names (identifiers): lower_case_with_underscores

For example:

UPDATE my_table SET name = 5;

This is not written in stone, but the bit about identifiers in lower case is highly recommended, IMO. Postgresql treats identifiers case insensitively when not quoted (it actually folds them to lowercase internally), and case sensitively when quoted; many people are not aware of this idiosyncrasy. Using always lowercase you are safe. Anyway, it's acceptable to use camelCase or PascalCase (or UPPER_CASE), as long as you are consistent: either quote identifiers always or never (and this includes the schema creation!).

I am not aware of many more conventions or style guides. Surrogate keys are normally made from a sequence (usually with the serial macro), it would be convenient to stick to that naming for those sequences if you create them by hand (tablename_colname_seq).

See also some discussion here, here and (for general SQL) here, all with several related links.

Note: Postgresql 10 introduced identity columns as an SQL-compliant replacement for serial.

Related questions

PostgreSQL: default constraint names

When creating a table in PostgreSQL, default constraint names will assigned if not provided: CREATE TABLE example ( a integer, b integer, UNIQUE (a, b) ); But using ALTER TABLE to add a constraint it seems a name is mandatory: ALTER TABLE …

Read More
Underscores or camelCase in PostgreSQL identifiers, when the programming language uses camelCase?

This has been bothering me for a while, and I can't arrive at a solution that feels right... Given an OO language in which the usual naming convention for object properties is camelCased, and an example object like this: { id: 667, …

Read More
Using UNNEST with a JOIN

I want to be able to use unnest() function in PostgreSQL in a complicated SQL query that has many JOINs. Here's the example query: SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id …

Read More