PostgreSQL naming conventions
naming-conventions, postgresql
Solution
Regarding tables names, case, etc, the prevalent convention is:
- SQL keywords: `UPPER CASE`
- identifiers (names of databases, tables, columns, etc): `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.
Problem
Where can I find a detailed manual about PostgreSQL naming conventions? (table names vs. camel case, sequences, primary keys, constraints, indexes, etc...)