13

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
);

closed as primarily opinion-based by Philᵀᴹ, Paul White, RolandoMySQLDBA, Max Vernon, Kin Shah Jun 16 '14 at 15:23

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

18

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.

  • 3
    +1 for "The really important bit is to ensure that you establish and use a specific style everywhere to be consistent." Consistency is the key. Without it, you have to think about stuff you should never have to think about. – Max Vernon Jun 17 '14 at 4:25
  • 4
    Well, camelCase and PascalCase in PostgreSQL are a bit painful. You have to quote these if you really want to have the names like that, otherwise the system silently lowercase them (I nearly wrote decapitalize, whatever associations it may arouse). – dezso Jun 17 '14 at 16:28
  • What about database names? Should I use database_name, database-name, DatabaseName, databaseName, etc.? – ma11hew28 Oct 17 '15 at 14:17
  • Is this answer actually for PostgreSQL? If you give the advice to use PascalCase for table names in a PG-specific answer, I think you should mention (a) how to deal with the fact that most examples use lowercase keywords and (b) whether to quote table names or to let PG fold them to lowercase. – AndreKR Jan 16 '17 at 16:34
  • @AndreKR here's the thing: I expect software developers to be adults, to know how to read the documentation, and to discuss with their team how to write code consistently. This answer is community wiki, meaning anyone is welcome to edit and improve it. I can't say exactly "this is the only way" and just because some people give examples all in lowercase does not mean that that is the only way in life. You gotta find your own path, which was the spirit of this answer. Please feel free to edit this community answer to improve it. Thanks! – jcolebrand Jan 16 '17 at 16:37
4

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).

  • Thanks, I've stumbled upon many in my own searches. I wanted to know if there is any official styleguide. – Adam Matan Jun 15 '14 at 8:57
  • There are many practitioners on both sides of the (singular_table_name/plural_table_name) debate whose opinions on other areas I respect. I'm a "single" man myself - if you're running a nuclear power plant, you might have a table called catastrophic_meltdown in which you never want to see any records at all! Give your primary keys a suffix _id and refer to them as Parent_Table_Name_FK in child tables - that's what I do. After that, it's easy-peasy! As for caps/no-caps , my SQL scripts have camel-case (unquoted), my statements may or may not. – Vérace Jun 15 '14 at 9:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.