Re: ALTER TABLE fails when changing column type due to index withbit_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@sss.pgh.pa.us> wrote:
>
> Manuel Rigger <rigger.manuel@gmail.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