Обсуждение: weird result by changing type enum array to text array of a column

On Mon, 2018-02-05 at 11:26 +0100, Thomas Poty wrote:
> Hello,
> 
> I m running 9.5.10 on centos 7.
> 
> I have a colmun "TestFields", its type is an array of an enum.
> 
> I would like to change the type of this column by an array of text.
> 
> So i execute  -> alter table "controldetailpromoters" alter column
> "TestFields" type text[]
> The command seems to be correctly executed
>  but  when I execute a "\d" of my table, the column "TestFields" is
> always an array of the enum....
> 
> 
> Do you have any idea of what is going wrong?
> 
> Thank you.
> Thomas


Hello Thomas,

See chapter 8.7.

Your array "TestFields" contains the internal enum references that are
converted to the defined values by cross referencing to the catalogue
table pg_enum.
I think you will have to add another column to the table as an array of
text, write a little program to cross reference the TestFields array to
pg_enum and update the new column with the textual representation of
the "enum's", then alter your app accordingly.

HTH,
Rob