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