trouble with the automatic indexes on CREATE TABLE

From: Bill McGonigle <mcgonigle(at)medicalmedia(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: trouble with the automatic indexes on CREATE TABLE
Date: 2001-05-15 22:21:20
Message-ID: 200105152220.f4FMKgA21992@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

When doing CREATE TABLE (piped in from a file with my table definitions) I'
m getting errors that I think are related to the automatic index creation
on my UNIQUE columns:

ERROR: Relation 'phone_number_types' does not exist
ERROR: Cannot create index: 'phone_number__phone_number__key' already
exists
ERROR: Relation 'delivery_methods' does not exist
ERROR: Cannot create index: 'delivery_meth_delivery_meth_key' already
exists
ERROR: Relation 'phone_number_locations' does not exist
ERROR: Cannot create index: 'phone_number__phone_number__key' already
exists

I suspect that the name PostgreSQL (7.0.3) is picking for the
automatically created indexes may be colliding with other index names from
other colums. This is what I can't seem to figure out how to avoid.

For instance:

CREATE TABLE Delivery_Methods (
delivery_method_id INTEGER NOT NULL UNIQUE,
delivery_method VARCHAR(50) NOT NULL UNIQUE
);

I think it's having trouble when it truncates the column name and winds up
with the same key name for both columns. Is there a way to force it to
not truncate or to specify the index name for it to use?

Thanks,
-Bill

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alex Pilosov 2001-05-15 22:25:31 index doesn't work for null?
Previous Message Trygve Falch 2001-05-15 20:26:14 Re: SELECT from a table in another database