Postgres DDL and DML best practices and coding standards [closed]

Question

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.

Answer 1

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:

CREATE TABLE asset_item (
    id serial not null unique, -- internal id
    tag text primary key, -- accounting control code
    description text not null,
...
);

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:

CREATE TABLE asset_item (
    id serial not null unique,
    tag text primary key,
    description text not null,
...
);

COMMENT ON TABLE asset_item IS $$ This table stores the fixed asset items.$$;
COMMENT ON COLUMN  asset_item.id IS $$Internal id, used for joins$$;
COMMENT ON COLUMN asset_item.tag IS $$Accounting control code$$;

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:

   SELECT ai.id, ai.tag, ai.description, coalesce(ai.start_depreciation, ai.purchase_date),
          adm.short_name, ai.usable_life
           - months_passed(coalesce(ai.start_depreciation, ai.purchase_date),
                                  coalesce(max(r.report_date),
                                           ai.start_depreciation,
                                           ai.purchase_date))/ 12,
          ai.purchase_value - ai.salvage_value, ai.salvage_value, max(r.report_date),
          sum(rl.amount), ai.purchase_value - sum(rl.amount)
     FROM asset_item ai
     JOIN asset_class ac ON (ai.asset_class_id = ac.id)
     JOIN asset_dep_method adm ON (adm.id = ac.method)
LEFT JOIN asset_report_line rl ON (ai.id = rl.asset_id)
LEFT JOIN asset_report r on (rl.report_id = r.id)
    WHERE r.id IS NULL OR r.approved_at IS NOT NULL
 GROUP BY ai.id, ai.tag, ai.description, ai.start_depreciation, ai.purchase_date,
          adm.short_name, ai.usable_life, ai.purchase_value, salvage_value
   HAVING (NOT 2 = ANY(as_array(r.report_class)))
          AND (NOT 4 = ANY(as_array(r.report_class)))
          OR max(r.report_class) IS NULL
 ORDER BY ai.id, ai.tag, ai.description;

Not everyone likes this style but we find it easy to read, follow, and maintain.

A few big points I would make:

1. Avoid inline views if you can. Formally define them or use CTE's if you need them. Inline views make maintenance harder because the simple structure of the query and rigid order is degraded.

2. Sometimes subqueries are needed but try to avoid them where you can for the same reason.

3. When deciding whether to use select * or a named column list consider whether you expect a named type back. If you do, then select * is preferable. Otherwise typically you want to avoid select *.

4. If you are going to use stored procedures consider your contracts carefully. Changes to interfaces in these are often not as easy as you'd like unless you have some sort of service discovery framework in between.

Answer 2

1. Make all identifiers snake_case

2. Don't use keywords for identifiers.

3. Name function params with a leading underscore to avoid ambiguities with column names.

4. Use := for assignment in PL/pgSQL instead of = (both are allowed but := is official)

5. Maximum identifier length is 63 bytes. PostgreSQL will silently truncate if too long, so don't make them any longer than 63 bytes (63 ASCII characters).