Postgres Pro
Downloads
Home   >   mailing lists

Thread: weird result by changing type enum array to text array of a column

weird result by changing type enum array to text array of a column

From
Thomas Poty
Date:
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

Re: weird result by changing type enum array to text array of acolumn

From
rob stone
Date:

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

Re: weird result by changing type enum array to text array of a column

From
Thomas Poty
Date:
Hello Rob,

I already read this.

I have already executed  this kind of procedure except with an array.

I don't know why but now it is working :-s

Thank you for your reply :-)

Thomas


2018-02-05 13:16 GMT+01:00 rob stone <floriparob@gmail.com>:


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