Re: [pgAdmin4][Patch] - RM 4555 - RE-SQL for Exclusion Constraint
Khushboo Vashi <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.pgadmin.devel |
|---|---|
| Message-ID | <CAFOhELebuQXDKz850J0QVF8wywLXG1EShktTkU19wfgxWbYYLQ@mail.gmail.com> |
Hi Akshay, Please find the attached updated patch. On Mon, Aug 5, 2019 at 1:20 PM Akshay Joshi <[email protected]> wrote: > Hi Khushboo > > Test case are working fine, some minor review comments: > > - Scenario name "Create/Alter Exclusion Constraint" repeating twice > instead of that it should be more understandable. > - File name should not be like > "create/alter_exclusion_constraint_1.sql" it should be like > 'alter_comment.sql'. > > > Fixed. Thanks, Khushboo > On Mon, Aug 5, 2019 at 12:30 PM Khushboo Vashi < > [email protected]> wrote: > >> Hi, >> >> Please find the attached patch for the RE-SQL tests of the Exclusion >> constraint node with some bug fixes in SQL. >> >> Thanks, >> Khushboo >> > > > -- > *Thanks & Regards* > *Akshay Joshi* > > *Sr. Software Architect* > *EnterpriseDB Software India Private Limited* > *Mobile: +91 976-788-8246* >
RM_4555_v1.patch
(application/octet-stream, 22.5 KB)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/alter_exclusion_constraint.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/alter_exclusion_constraint.sql
new file mode 100644
index 00000000..5fe0272f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/alter_exclusion_constraint.sql
@@ -0,0 +1,14 @@
+-- Constraint: Exclusion_$%{}[]()&*^!@"'`\/#a
+
+-- ALTER TABLE testschema.tableforexclusion DROP CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#a";
+
+ALTER TABLE testschema.tableforexclusion
+ ADD CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#a" EXCLUDE USING btree (
+ col2 text_pattern_ops WITH =)
+ INCLUDE (col1)
+ WITH (FILLFACTOR=98)
+ WHERE (col1 > 1)
+ DEFERRABLE INITIALLY DEFERRED;
+
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#a" ON testschema.tableforexclusion
+ IS 'Comment for alter';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/create_exclusion_constraint.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/create_exclusion_constraint.sql
new file mode 100644
index 00000000..88cb3d58
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/create_exclusion_constraint.sql
@@ -0,0 +1,15 @@
+-- Constraint: Exclusion_$%{}[]()&*^!@"'`\/#
+
+-- ALTER TABLE testschema.tableforexclusion DROP CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#";
+
+ALTER TABLE testschema.tableforexclusion
+ ADD CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#" EXCLUDE USING btree (
+ col2 text_pattern_ops WITH =)
+ INCLUDE (col1)
+ WITH (FILLFACTOR=12)
+ WHERE (col1 > 1)
+ DEFERRABLE INITIALLY DEFERRED;
+
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#" ON testschema.tableforexclusion
+ IS 'Comment for create';
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/msql_exclusion_constraint.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/msql_exclusion_constraint.sql
new file mode 100644
index 00000000..38adeec0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/msql_exclusion_constraint.sql
@@ -0,0 +1,6 @@
+ALTER TABLE testschema.tableforexclusion
+ RENAME CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#" TO "Exclusion_$%{}[]()&*^!@""'`\/#a";
+ALTER INDEX testschema."Exclusion_$%{}[]()&*^!@""'`\/#a"
+ SET (FILLFACTOR=98);
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#a" ON testschema.tableforexclusion
+ IS 'Comment for alter';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/test.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/test.json
new file mode 100644
index 00000000..11d14487
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/11_plus/test.json
@@ -0,0 +1,81 @@
+{
+ "scenarios": [
+ {
+ "type": "create",
+ "name": "Create Table",
+ "endpoint": "NODE-table.obj",
+ "sql_endpoint": "NODE-table.sql_id",
+ "data": {
+ "name": "tableforexclusion",
+ "columns": [{
+ "name": "col1",
+ "cltype": "integer",
+ "is_primary_key": true
+ }, {
+ "name": "col2",
+ "cltype": "text"
+ }],
+ "is_partitioned": false,
+ "schema": "testschema",
+ "spcname": "pg_default"
+ },
+ "store_table_id": true
+ }, {
+ "type": "create",
+ "name": "Create Extension",
+ "endpoint": "NODE-extension.obj",
+ "sql_endpoint": "NODE-extension.sql_id",
+ "data": {
+ "name": "btree_gist",
+ "version": "",
+ "relocatable": true
+ }
+ }, {
+ "type": "create",
+ "name": "Create Exclusion Constraint",
+ "endpoint": "NODE-exclusion_constraint.obj",
+ "sql_endpoint": "NODE-exclusion_constraint.sql_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#",
+ "comment": "Comment for create",
+ "fillfactor": "12",
+ "amname": "btree",
+ "condeferrable": true,
+ "condeferred": true,
+ "indconstraint": "col1 > 1",
+ "columns": [
+ {
+ "column": "col2",
+ "col_type": "text",
+ "order": false,
+ "nulls_order": false,
+ "operator": "=",
+ "oper_class": "text_pattern_ops"
+ }
+ ],
+ "include": ["col1"]
+ },
+ "expected_sql_file": "create_exclusion_constraint.sql"
+ }, {
+ "type": "alter",
+ "name": "Alter Exclusion Constraint",
+ "endpoint": "NODE-exclusion_constraint.obj_id",
+ "sql_endpoint": "NODE-exclusion_constraint.sql_id",
+ "msql_endpoint": "NODE-exclusion_constraint.msql_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#a",
+ "comment": "Comment for alter",
+ "fillfactor": "98"
+ },
+ "expected_sql_file": "alter_exclusion_constraint.sql",
+ "expected_msql_file": "msql_exclusion_constraint.sql"
+ }, {
+ "type": "delete",
+ "name": "Drop Exclusion Constraint",
+ "endpoint": "NODE-exclusion_constraint.delete_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#a"
+ }
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/alter_exclusion_constraint.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/alter_exclusion_constraint.sql
new file mode 100644
index 00000000..ecc1f9f6
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/alter_exclusion_constraint.sql
@@ -0,0 +1,13 @@
+-- Constraint: Exclusion_$%{}[]()&*^!@"'`\/#a
+
+-- ALTER TABLE testschema.tableforexclusion DROP CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#a";
+
+ALTER TABLE testschema.tableforexclusion
+ ADD CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#a" EXCLUDE USING gist (
+ col2 WITH <>)
+ WITH (FILLFACTOR=98)
+ WHERE (col1 > 1)
+ DEFERRABLE INITIALLY DEFERRED;
+
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#a" ON testschema.tableforexclusion
+ IS 'Comment for alter';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/alter_exclusion_constraint_without_fillfactor.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/alter_exclusion_constraint_without_fillfactor.sql
new file mode 100644
index 00000000..529715b0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/alter_exclusion_constraint_without_fillfactor.sql
@@ -0,0 +1,10 @@
+-- Constraint: Exclusion_$%{}[]()&*^!@"'`\/#_1a
+
+-- ALTER TABLE testschema.tableforexclusion DROP CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#_1a";
+
+ALTER TABLE testschema.tableforexclusion
+ ADD CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#_1a" EXCLUDE USING gist (
+ col2 WITH <>);
+
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#_1a" ON testschema.tableforexclusion
+ IS 'Comment for alter';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/create_exclusion_constraint.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/create_exclusion_constraint.sql
new file mode 100644
index 00000000..765ab93f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/create_exclusion_constraint.sql
@@ -0,0 +1,13 @@
+-- Constraint: Exclusion_$%{}[]()&*^!@"'`\/#
+
+-- ALTER TABLE testschema.tableforexclusion DROP CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#";
+
+ALTER TABLE testschema.tableforexclusion
+ ADD CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#" EXCLUDE USING gist (
+ col2 WITH <>)
+ WITH (FILLFACTOR=12)
+ WHERE (col1 > 1)
+ DEFERRABLE INITIALLY DEFERRED;
+
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#" ON testschema.tableforexclusion
+ IS 'Comment for create';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/create_exclusion_constraint_without_fillfactor.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/create_exclusion_constraint_without_fillfactor.sql
new file mode 100644
index 00000000..d7c463c3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/create_exclusion_constraint_without_fillfactor.sql
@@ -0,0 +1,11 @@
+-- Constraint: Exclusion_$%{}[]()&*^!@"'`\/#_1
+
+-- ALTER TABLE testschema.tableforexclusion DROP CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#_1";
+
+ALTER TABLE testschema.tableforexclusion
+ ADD CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#_1" EXCLUDE USING gist (
+ col2 WITH <>)
+ WITH (FILLFACTOR=12);
+
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#_1" ON testschema.tableforexclusion
+ IS 'Comment for create';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/msql_exclusion_constraint.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/msql_exclusion_constraint.sql
new file mode 100644
index 00000000..38adeec0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/msql_exclusion_constraint.sql
@@ -0,0 +1,6 @@
+ALTER TABLE testschema.tableforexclusion
+ RENAME CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#" TO "Exclusion_$%{}[]()&*^!@""'`\/#a";
+ALTER INDEX testschema."Exclusion_$%{}[]()&*^!@""'`\/#a"
+ SET (FILLFACTOR=98);
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#a" ON testschema.tableforexclusion
+ IS 'Comment for alter';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/msql_exclusion_constraint_without_fillfactor.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/msql_exclusion_constraint_without_fillfactor.sql
new file mode 100644
index 00000000..a0190e47
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/msql_exclusion_constraint_without_fillfactor.sql
@@ -0,0 +1,6 @@
+ALTER TABLE testschema.tableforexclusion
+ RENAME CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#_1" TO "Exclusion_$%{}[]()&*^!@""'`\/#_1a";
+ALTER INDEX testschema."Exclusion_$%{}[]()&*^!@""'`\/#_1a"
+ RESET (FILLFACTOR);
+COMMENT ON CONSTRAINT "Exclusion_$%{}[]()&*^!@""'`\/#_1a" ON testschema.tableforexclusion
+ IS 'Comment for alter';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/test.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/test.json
new file mode 100644
index 00000000..15605a29
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/tests/default/test.json
@@ -0,0 +1,120 @@
+{
+ "scenarios": [
+ {
+ "type": "create",
+ "name": "Create Table",
+ "endpoint": "NODE-table.obj",
+ "sql_endpoint": "NODE-table.sql_id",
+ "data": {
+ "name": "tableforexclusion",
+ "columns": [{
+ "name": "col1",
+ "cltype": "integer",
+ "is_primary_key": true
+ }, {
+ "name": "col2",
+ "cltype": "text"
+ }],
+ "is_partitioned": false,
+ "schema": "testschema",
+ "spcname": "pg_default"
+ },
+ "store_table_id": true
+ }, {
+ "type": "create",
+ "name": "Create Extension",
+ "endpoint": "NODE-extension.obj",
+ "sql_endpoint": "NODE-extension.sql_id",
+ "data": {
+ "name": "btree_gist",
+ "version": "",
+ "relocatable": true
+ }
+ }, {
+ "type": "create",
+ "name": "Create Exclusion Constraint",
+ "endpoint": "NODE-exclusion_constraint.obj",
+ "sql_endpoint": "NODE-exclusion_constraint.sql_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#",
+ "comment": "Comment for create",
+ "fillfactor": "12",
+ "amname": "gist",
+ "condeferrable": true,
+ "condeferred": true,
+ "indconstraint": "col1 > 1",
+ "columns": [
+ {
+ "column": "col2",
+ "order": false,
+ "nulls_order": false,
+ "operator": "<>",
+ "is_sort_nulls_applicable": false
+ }
+ ]
+ },
+ "expected_sql_file": "create_exclusion_constraint.sql"
+ }, {
+ "type": "alter",
+ "name": "Alter Exclusion Constraint",
+ "endpoint": "NODE-exclusion_constraint.obj_id",
+ "sql_endpoint": "NODE-exclusion_constraint.sql_id",
+ "msql_endpoint": "NODE-exclusion_constraint.msql_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#a",
+ "comment": "Comment for alter",
+ "fillfactor": "98"
+ },
+ "expected_sql_file": "alter_exclusion_constraint.sql",
+ "expected_msql_file": "msql_exclusion_constraint.sql"
+ }, {
+ "type": "delete",
+ "name": "Drop Exclusion Constraint",
+ "endpoint": "NODE-exclusion_constraint.delete_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#a"
+ }
+ }, {
+ "type": "create",
+ "name": "Create Exclusion Constraint to remove Fillfactor",
+ "endpoint": "NODE-exclusion_constraint.obj",
+ "sql_endpoint": "NODE-exclusion_constraint.sql_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#_1",
+ "comment": "Comment for create",
+ "fillfactor": "12",
+ "amname": "gist",
+ "columns": [
+ {
+ "column": "col2",
+ "order": false,
+ "nulls_order": false,
+ "operator": "<>",
+ "is_sort_nulls_applicable": false
+ }
+ ]
+ },
+ "expected_sql_file": "create_exclusion_constraint_without_fillfactor.sql"
+ }, {
+ "type": "alter",
+ "name": "Alter Exclusion Constraint to remove Fillfactor",
+ "endpoint": "NODE-exclusion_constraint.obj_id",
+ "sql_endpoint": "NODE-exclusion_constraint.sql_id",
+ "msql_endpoint": "NODE-exclusion_constraint.msql_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#_1a",
+ "comment": "Comment for alter",
+ "fillfactor": ""
+ },
+ "expected_sql_file": "alter_exclusion_constraint_without_fillfactor.sql",
+ "expected_msql_file": "msql_exclusion_constraint_without_fillfactor.sql"
+ }, {
+ "type": "delete",
+ "name": "Drop Exclusion Constraint",
+ "endpoint": "NODE-exclusion_constraint.delete_id",
+ "data": {
+ "name": "Exclusion_$%{}[]()&*^!@\"'`\\/#_1a"
+ }
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/11_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/11_plus/create.sql
index 1f4274b4..e7a6f489 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/11_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/11_plus/create.sql
@@ -1,14 +1,14 @@
ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }}
ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} (
{% for col in data.columns %}{% if loop.index != 1 %},
- {% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined and col.is_sort_nulls_applicable %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined and col.is_sort_nulls_applicable %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %})
-{% if data.include|length > 0 %}
- INCLUDE({% for col in data.include %}{% if loop.index != 1 %}, {% endif %}{{conn|qtIdent(col)}}{% endfor %}){% endif %}
-{% if data.fillfactor %}
+ {% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined and col.is_sort_nulls_applicable %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined and col.is_sort_nulls_applicable %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.include|length > 0 %}
+
+ INCLUDE ({% for col in data.include %}{% if loop.index != 1 %}, {% endif %}{{conn|qtIdent(col)}}{% endfor %}){% endif %}{% if data.fillfactor %}
WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %}
USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %}{% if data.indconstraint %}
+
WHERE ({{data.indconstraint}}){% endif%}
{% if data.condeferrable %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/11_plus/properties.sql
index d700de19..d2bdc82f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/11_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/11_plus/properties.sql
@@ -16,7 +16,7 @@ SELECT cls.oid,
condeferrable,
condeferred,
substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor,
- pg_get_expr(idx.indpred, idx.indrelid) AS indconstraint
+ pg_get_expr(idx.indpred, idx.indrelid, true) AS indconstraint
FROM pg_index idx
JOIN pg_class cls ON cls.oid=indexrelid
JOIN pg_class tab ON tab.oid=indrelid
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/create.sql
index 2990446d..0e8264a1 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/create.sql
@@ -2,6 +2,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }}
ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} (
{% for col in data.columns %}{% if loop.index != 1 %},
{% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined and col.is_sort_nulls_applicable %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined and col.is_sort_nulls_applicable %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.fillfactor %}
+
WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %}
USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %}{% if data.indconstraint %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql
index 919181b1..7feaeb7d 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql
@@ -16,7 +16,7 @@ SELECT cls.oid,
condeferrable,
condeferred,
substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor,
- pg_get_expr(idx.indpred, idx.indrelid) AS indconstraint
+ pg_get_expr(idx.indpred, idx.indrelid, true) AS indconstraint
FROM pg_index idx
JOIN pg_class cls ON cls.oid=indexrelid
JOIN pg_class tab ON tab.oid=indrelid
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/update.sql
index 4d70f215..0a8fa9c9 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/update.sql
@@ -14,9 +14,13 @@ ALTER INDEX {{ conn|qtIdent(data.schema, data.name) }}
ALTER INDEX {{ conn|qtIdent(data.schema, data.name) }}
SET (FILLFACTOR={{ data.fillfactor }});
{% endif %}
+{% if data.fillfactor == "" and data.fillfactor != o_data.fillfactor %}
+ALTER INDEX {{ conn|qtIdent(data.schema, data.name) }}
+ RESET (FILLFACTOR);
+{% endif %}
{# ==== To update exclusion constraint comments ==== #}
{% if data.comment is defined and data.comment != o_data.comment %}
COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }}
IS {{ data.comment|qtLiteral }};
{% endif %}
-{% endif %}
\ No newline at end of file
+{% endif %}