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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…