Thread: After upgrade from version9.4.1 to 10.15, changes on the table structure
After upgrade from version9.4.1 to 10.15, changes on the table structure
Hi,
I have created the table with the constraint like below from the where my postgres version is 9.4.1.
CREATE TABLE com_rep.am_dummy_simulate (
id character varying(32) NOT NULL,
label_key character varying(1020) NOT NULL,
is_editable_ind boolean NOT NULL,
domain_object_type character varying(32) NOT NULL
);
ALTER TABLE com_rep.am_dummy_simulate ADD CONSTRAINT CK_am_dummy_simulation CHECK(DOMAIN_OBJECT_TYPE IN ('PRINCIPAL','TOKEN') );
Below my table structure:
db=# \d com_rep.am_dummy_simulate
Table "com_rep.am_dummy_simulate"
Column | Type | Collation | Nullable | Default
--------------------+-------------------------+-----------+----------+---------
id | character varying(32) | | not null |
label_key | character varying(1020) | | not null |
is_editable_ind | boolean | | not null |
domain_object_type | character varying(32) | | not null |
Check constraints:
"ck_am_dummy_simulation" CHECK (domain_object_type::text = ANY (ARRAY['PRINCIPAL'::character varying, 'TOKEN'::character varying]::text[]))
Now I upgrade to 10.5 using pg_upgrade utility.
db-# \d am_dummy_simulate
Table "com_rep.am_dummy_simulate"
Column | Type | Modifiers
--------------------+-------------------------+-----------
id | character varying(32) | not null
label_key | character varying(1020) | not null
is_editable_ind | boolean | not null
domain_object_type | character varying(32) | not null
Check constraints:
"ck_am_dummy_simulation" CHECK (domain_object_type::text = ANY (ARRAY['PRINCIPAL'::character varying::text, 'TOKEN'::character varying::text]))
Why is this behavior ? I am suspecting pg_restore is doing some manipulation here.
When I checked the upgrade log,pg_dump is exporting like below.
CREATE TABLE com_rep.am_dummy_simulate (
id character varying(32) NOT NULL,
label_key character varying(1020) NOT NULL,
is_editable_ind boolean NOT NULL,
domain_object_type character varying(32) NOT NULL,
CONSTRAINT ck_am_dummy_simulation CHECK (((domain_object_type)::text = ANY ((ARRAY['PRINCIPAL'::character varying, 'TOKEN'::character varying])::text[])))
);
Any explanation will be help full.
Regards,
Sankar
"Mundla, Sankar" <Sankar.Mundla@rsa.com> writes:
> [ dump and reload converts ]
> "ck_am_dummy_simulation" CHECK (domain_object_type::text = ANY (ARRAY['PRINCIPAL'::character varying,
'TOKEN'::charactervarying]::text[]))
> [ into ]
> "ck_am_dummy_simulation" CHECK (domain_object_type::text = ANY (ARRAY['PRINCIPAL'::character varying::text,
'TOKEN'::charactervarying::text]))
> Why is this behavior ?
pg_dump, like psql, prefers to show implicit casts explicitly, to
reduce the risk of the expression being interpreted differently
when reloaded. So while you wrote the array without any cast
to begin with, you get the first form in the dump.
However, the parser treats array[...]::something[] differently
from a bare array[]. The cast to "something" is applied to each
array element immediately. The reason for that is that if we
just transformed array[...] without applying the knowledge that
the final type must be "something", we might get an unnecessary
parse failure from inability to resolve a common type for the
array elements. (The parser has no way to know, of course, that
some previous cycle of processing successfully resolved a common
type for the elements. This behavior is unnecessary in this
context, but it does help for manually-entered expressions.)
So you end up with something that looks a little different.
It's semantically equivalent though, so I see no bug here.
regards, tom lane