PostgreSQL naming conventions

Question

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

Ineffective

Well, if we'll go a bit further and look into generic naming convension I highly recommend checking this answer: #4703228 – Eleven

Answer 1

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.

Swellhead

Are there any conventions regarding referencing data types in uppercase or lowercase, and reference functions? (i.e. regclass or REGCLASS and to_timestamp(0) or TO_TIMESTAMP(0))? I guess we consider types and functions as identifiers, and therefore lowercase? – Sumption

FWIW, the only idiosyncrasy is that Pg folds to lower case, where the SQL standard says that it should fold to upper case. DBMSs that fail to case fold are the weird nonstandard ones. – Sharisharia

As a new user of Postgres, this is pretty frustrating. Having to chose between typing quotes all the time or using an ugly naming convention sucks. It sucks ass. – Scrubland

@user1334007 The convention is not ugly - and read Craig's comment above. And there's no need to quote if you didn't quote when you created the tables (that is, if you are consistent). – Swellhead

@leonbloy, if you don't quote when you create the table, then Postgres will lower case your table names and field names. You can use camel case when you write your queries but your result will show up all lowercase, which is hard to read when fields consist of multiple words (lastupdateddate). If you want your column names to be readable in the query results you have to either quote everything or use snake case, which, IMO, is ugly. It would much better if Postgres left your names alone and didn't require you to supply quotes. – Scrubland

It's the SQL standard. https://mcmap.net/q/48651/-is-sql-syntax-case-sensitive – Swellhead

I hate keywords in upper case, just hate it, convention or no. There's no case-dependency with regard to keywords. I prefer all lower case. Yes, I know it's a mere preference, but this is also a mere comment. ;-) – Derinna

They just had to bring back this tired old convention. camelCase and PascalCase are much cleaner and don't require me to add unnecessary underscores everywhere. What's worse is there isn't really a way around this. It would be nice if the case I provide is the case I keep. Well it is what it is. – Ambiguous

Answer 2

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.

Sharisharia

Thanks for linking to the doc on rules of identifier naming. I had difficulty finding that particular topic. – Fullgrown

"I find it easier to use lower_case_underscore_separated_identifiers"... recently, I heard this described as "snake case" – Ambages

Answer 3

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.

Trifocal

Ideally, databases should be independent to a specific backend. Different backends(Javascript / Go) may read from the same DB, and similarly the current backend may be rewritten with a different language/framework. – Actuary