Re: ALTER TABLE fails when changing column type due to index with bit_ops opclass

I see, thanks for the explanation!

From my perspective, it is not necessary to document this, as probably
not many people would have such a use case.

Best,
Manuel

On Wed, Nov 20, 2019 at 7:16 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com> writes:
> > Consider the following statements:
>
> > CREATE TABLE t0(c0 BIT VARYING(1));
> > CREATE INDEX i0 ON t0(c0 bit_ops);
> > ALTER TABLE t0 ALTER c0 TYPE TEXT; -- ERROR: operator class "bit_ops"
> > does not accept data type text
>
> > Altering the column type fails, which is somewhat unexpected, since it
> > does not seem to cause problems for other opclasses. For example, the
> > following executes without errors:
>
> > CREATE TABLE t0(c0 TEXT);
> > CREATE INDEX i0 ON t0(c0 text_ops);
> > ALTER TABLE t0 ALTER c0 TYPE BIT VARYING(1) USING c0::bit varying(1);
>
> > Is this a bug or expected?
>
> I think this is expected behavior, more or less. The critical difference
> is that you specified a non-default opclass in the first example (the
> default choice for that column datatype is varbit_ops not bit_ops).
> ALTER TABLE figures that it's okay to replace the default opclass for
> the original type with the default opclass for the new type, but it's
> not willing to guess about what you want if the index has a non-default
> opclass. So the conversion only goes through if the specified opclass
> also accepts the new datatype, which typically it wouldn't.
>
> This is probably not documented anyplace. Should it be? If so, what
> should we say and where?
>
> regards, tom lane