Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend.
I am looking for something similar to what Google has for programming.
Thank you in advance.
put on hold as primarily opinion-based by dezso, Colin 't Hart, Paul White, ypercube, Vérace Jul 24 at 17:06
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.
Like all coding standards, every project is going to have preferences. I am not saying you will like my preferences but I will offer them. Keep in mind that LedgerSMB is heading towards a model where half of the code will be SQL so we have had to put some thought into these.
The first thing we did was decide that DDL should be documented for autodocumentation tools rather than human reviewers. If you are going to go the human reviewer route (which reduces your documentation), you do something like:
That is great and all, but it means that people have to read your DDL files to get a sense of what you want to tell them. If you go the autodoc documentation tool route, you get something different:
This makes the comments available through general documentation tools which is very helpful in a large project. The big limitation is that comments must follow definitions.
For DML we usually justify the queries, lining up the verbs on one side and conditions or identifiers on the other. An example might be:
Not everyone likes this style but we find it easy to read, follow, and maintain.
A few big points I would make: