Official PostgreSQL Capitalization conventions

Question

Is there an official PostreSQL convention regarding capitalization in DB, Table and field names?

The examples on the official site suggest a lowercase and _ word separation, and I wonder whether this policy is official.

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

Answer 1

I'm going to basically mirror Verace's comments and state this, making it semi-official:

There is no one best practice that will cover every circumstance. What follows makes the following assumptions (and what to do if you haven't done this):

• You have already discussed this with your team (people working by themselves often just have to make up their mind)
• There is no formalized style definition for SQL already on your team (Otherwise you wouldn't be asking us)
• There is no formalized style definition for any code (Follow the same basic conventions already established for other languages and formalize a style)

So the rest of this is somewhat opinionated but based on experience

1. When it comes to table names
   1. You should go for singular entity names (this makes documentation easier)
   2. You should use Pascal Case here
2. When it comes to field names
   1. Use camelCase on your field names
   2. Use short singular names unless the definition definitely makes sense as a plural (it almost never does)
3. When it comes to your own function or stored procedure names
   1. Use underscore_separation
   2. Use field naming for the parameterization
4. When it comes to built in database functions or language names (eg SELECT)
   1. Unless there's a requirement for it to be capitalized a certain way, use ALL CAPS
   2. Know the APIs for your language to know what's reasonable or required
5. When it comes to spacing
   1. A lot of folks use column alignment for keywords and indentation for things which are not keywords
   2. A lot of folks use commas at the beginning of the row when the fields are separated on each line (This makes it easier to comment out a specific field from a selection list)
   3. Never use spaces as part of the names of things, not even for return value headers.
6. When it comes to punctuation
   1. Parentheses - USE THEM. They're FREE. I promise.
   2. Semicolons - USE THEM. They're not going to break you. They force you to think out your code. And they're good hygiene.
   3. Carriage Returns - Once again, they're free ;-) And make your code readable.

You should also recognize that while I'm trying to help you apply a generic style guide, that the community for Postgres generally doesn't use camelCase or PascalCase but instead uses underscore_separation. The really important bit is to ensure that you establish and use a specific style everywhere to be consistent.

Answer 2

A quick Google will reveal many sites which indicate best practices. I would say only two things - don't EVER use spaces "My Table Name" (porting becomes impossible due to different escaping mechanisms; same goes for any non-alphanumeric character). With these sorts of mechanims, you normally have to respect case also. There are enough letters and words in the English (or your own) language and identifier lengths are long enough (I don't know any system that has identifier_length < 32, PostgreSQL is 64). And never use SQL keywords (which vary by RDBMS) which will do the same thing.

Statments like

SELECT "Field" FROM "Table";

can be valid! The absolutely critical thing is to have a clear and relatively simple convention and then stick to it. People have differing opinions as you will find out - read around the topic and pick what "feels right" to you. See these sites 1, 2, 3, 4, 5,... (there are many more).