Re: PostgreSQL Developer Best Practices

> On Aug 22, 2015, at 10:15 AM, Melvin Davidson <melvin6925(at)gmail(dot)com> wrote:
> 6. Although it is legal to use the form "column TYPE PRIMARY KEY," It is best to specify as a CONSTRAINT,
> that way YOU get to choose the name, otherwise postgres assigns a default name which may not be to your liking.
> EG: , CONSTRAINT accounts_pk PRIMARY KEY (account_id)

> 10. Standardize Index names with the form table_name + col(s) + “idx”
> EG: For accounts table:
> accounts_name_idx
> accounts_city_state_idx
>

I really like the standardization that PostgreSQL uses in auto generating default names. The rule I use is to always use the auto generated names unless the object is referenced routinely in code. In most cases developers don’t care about index, unique, foreign key, or primary key names (from a coding standpoint) so why should they be creating the names. Since the postgresql standard uses auto generated names with ‘_pkey’ for PRIMARY KEY ‘_fkey’ for FOREIGN KEY, and ‘_key’ for UNIQUE, why not use the same rules for consistency? So I disagree with 6 and would extend 10 to include these other names if they are manually generated.

interestingly enough, when I searched 9.5 docs I could not find a description of these postgreSQL naming convention. Probably because the developers consider it an internal detail that could change which is fine, since the names usually don’t matter, until they do.

I would say use “column TYPE PRIMARY KEY”, “column TYPE UNIQUE”, and ‘column TYPE REFERENCES …’ every place you can and only create manual names when absolutely necessary. When you do create manual names follow the standard PostgreSQL convention.

Now I have worked on mostly smaller installations so maybe someone should chime in if this is a bad best practice.

Neil