Fixed an issue where the default value of string for columns should w…

…rap in quotes in the create script. pgadmin-org#5577
akshay-joshi · Jan 4, 2023 · 61d91d6 · 61d91d6
1 parent 32b2f13
commit 61d91d6
Show file tree

Hide file tree

Showing 5 changed files with 13 additions and 3 deletions.
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py
@@ -217,6 +217,10 @@ def get_formatted_columns(conn, tid, data, other_columns,
     edit_types = {}
     # Add inherited from details from other columns - type, table
     for col in all_columns:
+        if col and 'defval' in col and col['defval']:
+            col['defval'] = int(col['defval']) if col[
+                'defval'].isnumeric() else col['defval']
+
         edit_types[col['atttypid']] = []
         for other_col in other_columns:
             if col['name'] == other_col['name']:

diff --git a/...er/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/create.sql b/...er/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/create.sql
@@ -42,7 +42,7 @@ CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE{% if add_not_exists
 {% if data.columns and data.columns|length > 0 %}
 {% for c in data.columns %}
 {% if c.name and c.cltype %}
-    {% if c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% endif %}{{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval is defined and c.defval is not none and c.defval != '' %} DEFAULT {{c.defval}}{% endif %}
+    {% if c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% endif %}{{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval is defined and c.defval is not none and c.defval != '' %} DEFAULT {% if c.defval is string %}'{{c.defval}}'{% else %}{{c.defval}}{%endif%}{% endif %}
 {% if c.colconstype == 'i' and c.attidentity and c.attidentity != '' %}
 {% if c.attidentity == 'a' %} GENERATED ALWAYS AS IDENTITY{% elif c.attidentity == 'd' %} GENERATED BY DEFAULT AS IDENTITY{% endif %}
 {% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %} ( {% endif %}

diff --git a/...er/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql b/...er/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql
@@ -46,7 +46,7 @@ CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE{% if add_not_exists
 {% if data.columns and data.columns|length > 0 %}
 {% for c in data.columns %}
 {% if c.name and c.cltype %}
-    {% if c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% endif %}{{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval is defined and c.defval is not none and c.defval != '' and c.colconstype != 'g' %} DEFAULT {{c.defval}}{% endif %}
+    {% if c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% endif %}{{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval is defined and c.defval is not none and c.defval != '' and c.colconstype != 'g' %} DEFAULT {% if c.defval is string and not c.defval.startswith('\'') %}'{{c.defval}}'{% else %}{{c.defval}}{%endif%}{% endif %}
 {% if c.colconstype == 'i' and c.attidentity and c.attidentity != '' %}
 {% if c.attidentity == 'a' %} GENERATED ALWAYS AS IDENTITY{% elif c.attidentity == 'd' %} GENERATED BY DEFAULT AS IDENTITY{% endif %}
 {% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %} ( {% endif %}

diff --git a/...er/server_groups/servers/databases/schemas/tables/templates/tables/sql/default/create.sql b/...er/server_groups/servers/databases/schemas/tables/templates/tables/sql/default/create.sql
@@ -42,7 +42,7 @@ CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE{% if add_not_exists
 {% if data.columns and data.columns|length > 0 %}
 {% for c in data.columns %}
 {% if c.name and c.cltype %}
-    {% if c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% endif %}{{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval is defined and c.defval is not none and c.defval != '' %} DEFAULT {{c.defval}}{% endif %}
+    {% if c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% endif %}{{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval is defined and c.defval is not none and c.defval != '' %} DEFAULT {% if c.defval is string %}'{{c.defval}}'{% else %}{{c.defval}}{%endif%}{% endif %}
 {% if c.colconstype == 'i' and c.attidentity and c.attidentity != '' %}
 {% if c.attidentity == 'a' %} GENERATED ALWAYS AS IDENTITY{% elif c.attidentity == 'd' %} GENERATED BY DEFAULT AS IDENTITY{% endif %}
 {% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %} ( {% endif %}

diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
@@ -1561,6 +1561,12 @@ def get_sql(self, did, scid, tid, data, res, add_not_exists_clause=False,
             if with_drop:
                 sql = self.get_delete_sql(data) + '\n\n'
 
+            if 'columns' in data and data['columns']:
+                for col in data['columns']:
+                    if col and 'defval' in col and col['defval']:
+                        col['defval'] = int(col['defval']) if col[
+                            'defval'].isnumeric() else col['defval']
+
             sql += render_template("/".join([self.table_template_path,
                                             self._CREATE_SQL]),
                                    data=data, conn=self.conn,