After upgrade from version9.4.1 to 10.15, changes on the table structure

From: "Mundla, Sankar" <Sankar(dot)Mundla(at)rsa(dot)com>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: After upgrade from version9.4.1 to 10.15, changes on the table structure
Date: 2021-03-24 11:09:03
Message-ID: MN2PR19MB2894A88649F46228653B64EAE0639@MN2PR19MB2894.namprd19.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bryan Staley 2021-03-24 13:41:03 Re: BUG #16941: ECPG add support for const void*
Previous Message Fujii Masao 2021-03-24 10:25:54 Re: BUG #16931: source code problem about commit_ts