PostgreSQL naming conventions

The only two answers here are 6 years old idk if snake_case being the best case is true anymore. Here's my take on modern times. Also, forgoing any extra complication of needing to double-quote. I think flow is more important than trying to avoid a minor inconvenience.

Provided by the fact that there are no strict guidelines/style guides, I'd say it is best to use the same case as project code. So for example, using OOP approach in languages like JavaScript, table names would be in PascalCase where as attributes would be in camelCase. Where as if you're taking the functional approach, they'd both be camelCase. Also, by convention JS classes are PascalCase and attributes are camelCase so it makes sense anyways.

On the other hand, if you are coding in Python using SqlAlchemy then it only makes sense to use snake_case names for function-derived models and PascalCase names for class-derived models. In both cases, attributes/columns should be snake_case.

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.

There isn't really a formal manual, because there's no single style or standard.

So long as you understand the rules of identifier naming you can use whatever you like.

In practice, I find it easier to use lower_case_underscore_separated_identifiers because it isn't necessary to "Double Quote" them everywhere to preserve case, spaces, etc.

If you wanted to name your tables and functions "@MyAṕṕ! ""betty"" Shard$42" you'd be free to do that, though it'd be pain to type everywhere.

The main things to understand are:

• Unless double-quoted, identifiers are case-folded to lower-case, so MyTable, MYTABLE and mytable are all the same thing, but "MYTABLE" and "MyTable" are different;

• Unless double-quoted:

  SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).

• You must double-quote keywords if you wish to use them as identifiers.

In practice I strongly recommend that you do not use keywords as identifiers. At least avoid reserved words. Just because you can name a table "with" doesn't mean you should.