Обсуждение: Explicit Named Indexes for Constraints

I'm new to PosgtreSQL, a veteran with Informix. We are considering a migration.

In Informix, it is recommended to create explicit named indexes on
columns for primary and foreign keys prior to creating the
constraints. Otherwise, the server create the indexes for you with
meaningless names. This is not generally a problem, except when you
dump the schema, you get all the constraint indexes in the DDL,
exported as if they were explicitly created, but with the server
generated names. It's a mess to sort through.

What's the recommended procedure in PG?  At first glance it appears
that PG hides the implicit indexes from you at all times, including
pg_dump. So it appears that explicit index creation can be skipped
without leaving you with a mess later. Is this just a non-issue in PG?

Jeff

"Jeff Larsen" <jlar310@gmail.com> writes:
> In Informix, it is recommended to create explicit named indexes on
> columns for primary and foreign keys prior to creating the
> constraints. Otherwise, the server create the indexes for you with
> meaningless names. This is not generally a problem, except when you
> dump the schema, you get all the constraint indexes in the DDL,
> exported as if they were explicitly created, but with the server
> generated names. It's a mess to sort through.

Ugh.  In PG, you can specify the names for server-generated indexes;
they're just the same names given to the constraints:

CREATE TABLE foo (f1 int constraint foo_primary_key primary key);

The index underlying this constraint will be named foo_primary_key.
If you leave off the "constraint name" clause then you get an
autogenerated name, but it's not so meaningless that there's a strong
need to override it --- in this example it'd be "foo_pkey".

Manual creation of indexes duplicating a constraint is definitely
*not* the thing to do in PG; you'll end up with redundant indexes.

> What's the recommended procedure in PG?  At first glance it appears
> that PG hides the implicit indexes from you at all times, including
> pg_dump.

I wouldn't say they are "hidden", you just don't need to mention them
separately in the DDL commands.

            regards, tom lane