Re: [pgsql-general] Daily digest v1.13732 (15 messages)

On Tue, 2015-08-25 at 15:41 +0000,  Neil Tiffin <neilt@neiltiffin.com>
wrote:

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

I prefer to take control of names in order to be certain that on
multiple database instances, the names will *always* be the same.  This
allows schema-diff tools (like my own skit) to provide more useful
results.  Although, as you point out, Postgres does a pretty good job of
naming things, there are (or at least have been in the past) cases where
names have not been predictable.

Furthermore, a policy of explicit naming seems to me a relatively light
burden on a developer or DBA, and one that may even lead to more thought
being applied during database object design.  If the developer has to
think of a name, they may be more inclined to think more deeply about
the purpose of that object.

For the record, I favour using a double underscore to separate the
table_name part of constraints, etc from any other parts of the name.
So:

        account__name_idx would be an index on the name field of the
        accounts table;

        account_name__pk would be a primary key on the account_names
        table.

It's a personal preference and works for me, your mileage may vary.

__
Marc