Re: Remove implicit unique index creation on SERIAL columns?

Rod Taylor wrote:
> I'd like to propose dropping the auto-creation of UNIQUE indexes on
> serial columns for the following reasons:
>
> 1. Serials with indexes are quite difficult to handle in pg_dump. It
> means that the implicitly created unique index must be destroyed prior
> to loading the data, then re-created afterward else risk a performance
> hit.
>
>
> 2. SERIAL columns are usually used as the primary key of the table. As
> such one must specify PRIMARY KEY at creation time to override the
> implicitly created UNIQUE index.
>
> 3. Consistency with other databases.
>
> MySQL's AUTO_INCREMENT suggests quite heavily that an index be applied,
> but it doesn't appear to do it for you.
>
> Oracle has SEQUENCES, but it is up to the user to associate them with a
> column as per my understanding. At least, thats all I could find.
>
> SAP -> SERIAL and SERIAL(n). No index is created (creates sequence /
> default)

FWIW, also MSSQL.

I agree 100%. If you want an index, unique constraint, or primary key on
a SERIAL, I think you should explicitly add it. SERIAL should give me a
column that automatically increments -- no more, no less.

Joe