On Thu, 2005-11-10 at 03:19, Andrus wrote:
> >> > How to create primary key without duplicate index on bar column ?
> >> >
> >> > Andrus.
> >> >
> >> >
> >>
> >> you can't.
> >> postgresql implements primary keys creating unique indexes and not
> >> null constraints on the pk columns.
> >
> > But, of course, you CAN delete that other index now that it's redundant.
>
> Scott,
>
> thank you. I don't understand how to delete primary key index without
> removing primary key constraint or how to force primary key to use foo_bar
> index.
>
> I tried
>
> CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY );
> CREATE UNIQUE INDEX foo_bar ON foo(bar bpchar_pattern_ops);
> DROP INDEX foo_pkey;
>
> but got
>
> ERROR: cannot drop index foo_pkey because constraint foo_pkey on table foo
> requires it
Sorry, I thought you were looking at removing the OTHER index, the
unique one. Now that I see you need it for the bpchar pattern ops, I
guess you're kinda stuck having two indexes if you want a primary key in
the table.
Note that if you don't use the pk in unnamed joins (i.e. you always
identify the field you're keying off of) then the primary key is
redundant and not needed, and you could just create the table without it
and then create the unique index.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
