feat: use correct CREATE and ALTER syntax for PostgreSQL ENUM columns, and provide ENUM labels in grid cells

ansgarbecker · ansgarbecker · commit 5150a7ff97e3 · 2026-03-16T11:04:23.000+01:00
Refs #1125
diff --git a/source/dbconnection.pas b/source/dbconnection.pas
@@ -490,6 +490,7 @@   TDBConnection = class(TComponent)
       FMaxRowsPerInsert: Int64;
       FCaseSensitivity: Integer;
       FSQLFunctions: TSQLFunctionList;
+      FNamedEnums: TStringList;
       procedure SetActive(Value: Boolean); virtual; abstract;
       procedure DoBeforeConnect; virtual;
       procedure StartSSHTunnel(var FinalHost: String; var FinalPort: Integer);
@@ -624,6 +625,7 @@   TDBConnection = class(TComponent)
       function IsHex(Text: String): Boolean;
       function Has(Item: TFeatureOrRequirement): Boolean;
       property SqlProvider: TSqlProvider read FSqlProvider;
+      property NamedEnums: TStringList read FNamedEnums;
     published
       property Active: Boolean read FActive write SetActive default False;
       property Database: String read FDatabase write SetDatabase;
@@ -2147,6 +2149,9 @@ constructor TDBConnection.Create(AOwner: TComponent);
   FStringQuoteChar := '''';
   FCollationTable := nil;
   FCharsetTable := nil;
+  FQuoteChar := '"';
+  FQuoteChars := '"[]';
+  FNamedEnums := TStringList.Create;
 end;
 
 
@@ -2171,8 +2176,6 @@ constructor TAdoDBConnection.Create(AOwner: TComponent);
   i: Integer;
 begin
   inherited;
-  FQuoteChar := '"';
-  FQuoteChars := '"[]';
   SetLength(FDatatypes, Length(MSSQLDatatypes));
   for i:=0 to High(MSSQLDatatypes) do
     FDatatypes[i] := MSSQLDatatypes[i];
@@ -2186,7 +2189,6 @@ constructor TPgConnection.Create(AOwner: TComponent);
   i: Integer;
 begin
   inherited;
-  FQuoteChar := '"';
   FQuoteChars := '"';
   SetLength(FDatatypes, Length(PostGreSQLDatatypes));
   for i:=0 to High(PostGreSQLDatatypes) do
@@ -2203,8 +2205,6 @@ constructor TSQLiteConnection.Create(AOwner: TComponent);
   i: Integer;
 begin
   inherited;
-  FQuoteChar := '"';
-  FQuoteChars := '"[]';
   SetLength(FDatatypes, Length(SQLiteDatatypes));
   for i:=0 to High(SQLiteDatatypes) do
     FDatatypes[i] := SQLiteDatatypes[i];
@@ -2218,8 +2218,6 @@ constructor TInterbaseConnection.Create(AOwner: TComponent);
   i: Integer;
 begin
   inherited;
-  FQuoteChar := '"';
-  FQuoteChars := '"[]';
   SetLength(FDatatypes, Length(InterbaseDatatypes));
   for i:=0 to High(InterbaseDatatypes) do
     FDatatypes[i] := InterbaseDatatypes[i];
@@ -2234,6 +2232,7 @@ destructor TDBConnection.Destroy;
   FKeepAliveTimer.Free;
   FFavorites.Free;
   FInformationSchemaObjects.Free;
+  FNamedEnums.Free;
   if FOwnsParameters then
     FParameters.Free;
   inherited;
@@ -2309,7 +2308,7 @@ function TDBConnection.GetDatatypeByName(var DataType: String; DeleteFromSource:
       TypesSorted.Free;
     end;
 
-    rx.Expression := '^('+Types+')\b(\[\])?';
+    rx.Expression := '\b('+Types+')\b(\[\])?';
     Match := rx.Exec(DataType);
     // Prefer a later match which is longer than the one found before.
     // See http://www.heidisql.com/forum.php?t=17061
@@ -3389,8 +3388,8 @@ procedure TDBConnection.DoAfterConnect;
 var
   i: Integer;
   TypeOid: String;
-  SubTypes: TDBQuery;
-  SubTypesList: TStringList;
+  AllEnums: TDBQuery;
+  AllEnumsList: TStringList;
   SQLFunctionsFileOrder: String;
   MajorMinorVer, MajorVer: String;
   StartupScript: String;
@@ -3416,19 +3415,26 @@ procedure TDBConnection.DoAfterConnect;
       end;
     end
 
-    else if ExecRegExprI('^[a-z]$', Datatypes[i].NativeTypes) then begin
+    else if (Datatypes[i].NativeTypes = 'e') and FSqlProvider.Has(qGetEnumTypes) then begin
       // PG ENUM types populated via 'e'
-      if FSqlProvider.Has(qGetSubDataTypes) then begin
-        SubTypes := GetResults(FSqlProvider.GetSql(qGetSubDataTypes, [Datatypes[i].NativeTypes]));
-        SubTypesList := TStringList.Create;
-        while not SubTypes.Eof do begin
-          SubTypesList.Add(SubTypes.Col('enum_name'));
-          SubTypesList.Add(SubTypes.Col('enum_schema') + '.' + SubTypes.Col('enum_name'));
-          SubTypes.Next;
-        end;
-        SubTypes.Free;
-        Datatypes[i].Names := Implode('|', SubTypesList);
+      AllEnums := GetResults(FSqlProvider.GetSql(qGetEnumTypes));
+      AllEnumsList := TStringList.Create;
+      while not AllEnums.Eof do begin
+        AllEnumsList.Add(AllEnums.Col('enum_name'));
+        AllEnumsList.Add(AllEnums.Col('enum_schema') + '.' + AllEnums.Col('enum_name'));
+        FNamedEnums.AddPair(
+          AllEnums.Col('enum_name'),
+          AllEnums.Col('enum_labels')
+          );
+        FNamedEnums.AddPair(
+          AllEnums.Col('enum_schema') + '.' + AllEnums.Col('enum_name'),
+          AllEnums.Col('enum_labels')
+          );
+        AllEnums.Next;
       end;
+      AllEnums.Free;
+      Datatypes[i].Names := Implode('|', AllEnumsList);
+      AllEnumsList.Free;
     end;
 
   end;
@@ -9131,19 +9137,33 @@ function TDBQuery.ValueList(Column: Integer): TStringList;
   i: Integer;
 begin
   Result := TStringList.Create;
-  Result.QuoteChar := '''';
-  Result.Delimiter := ',';
   ColAttr := ColAttributes(Column);
   if Assigned(ColAttr) then case ColAttr.DataType.Index of
+
     dbdtEnum, dbdtSet: begin
-      Result.DelimitedText := ColAttr.LengthSet;
-      // Take care for escaped ENUM definitions, see issue #799
+      // Lool up PostgreSQL enum labels in prefetched list
+      i := FConnection.NamedEnums.IndexOfName(ColAttr.LengthSet);
+      if i > -1 then begin
+        Result.Delimiter := '|';
+        Result.DelimitedText := FConnection.NamedEnums.ValueFromIndex[i];
+      end
+      else begin
+        // .. or in MySQL Length/Set
+        Result.QuoteChar := '''';
+        Result.Delimiter := ',';
+        Result.DelimitedText := ColAttr.LengthSet;
+      end;
+      // In any case, take care for escaped ENUM definitions, see issue #799
       for i:=0 to Result.Count-1 do begin
         Result[i] := FConnection.UnescapeString(Result[i]);
       end;
     end;
-    dbdtBool:
+
+    dbdtBool: begin
+      Result.Delimiter := ',';
       Result.DelimitedText := 'true,false';
+    end;
+
   end;
 end;
 
@@ -10754,25 +10774,29 @@ function TTableColumn.SQLCode(OverrideCollation: String=''; Parts: TColumnParts=
   end;
 
   if InParts(cpType) then begin
-    case FConnection.Parameters.NetTypeGroup of
-      ngPgSQL: begin
-        if DefaultType = cdtAutoInc then
-          Result := Result + 'SERIAL'
-        else
-          Result := Result + DataType.Name;
+
+    if FConnection.Parameters.IsAnyPostgreSQL and (DefaultType = cdtAutoInc) then begin
+      Result := Result + 'SERIAL';
+    end
+    else begin
+
+      if (DataType.Index = dbdtEnum) and (FConnection.NamedEnums.IndexOfName(LengthSet) > -1) then begin
+        Result := Result + LengthSet;
+      end
+      else begin
+        Result := Result + DataType.Name;
+        if (LengthSet <> '') and DataType.HasLength then
+          Result := Result + '(' + LengthSet + ')';
       end;
-      else Result := Result + DataType.Name;
-    end;
 
-    if (LengthSet <> '') and DataType.HasLength then
-      Result := Result + '(' + LengthSet + ')';
-    if (DataType.Category in [dtcInteger, dtcReal]) and Unsigned then
-      Result := Result + ' UNSIGNED';
-    if (DataType.Category in [dtcInteger, dtcReal]) and ZeroFill then
-      Result := Result + ' ZEROFILL';
-    if Compressed and FConnection.Parameters.IsMariaDB then
-      Result := Result + ' /*!100301 COMPRESSED*/';
-    Result := Result + ' '; // Add space after each part
+      if (DataType.Category in [dtcInteger, dtcReal]) and Unsigned then
+        Result := Result + ' UNSIGNED';
+      if (DataType.Category in [dtcInteger, dtcReal]) and ZeroFill then
+        Result := Result + ' ZEROFILL';
+      if Compressed and FConnection.Parameters.IsMariaDB then
+        Result := Result + ' /*!100301 COMPRESSED*/';
+      Result := Result + ' '; // Add space after each part
+    end;
   end;
 
   if InParts(cpAllowNull) and (not IsVirtual) and (not FConnection.Parameters.IsAnyMSSQL) then begin
@@ -10883,8 +10907,11 @@ procedure TTableColumn.ParseDatatype(Source: String);
       LengthSet := '';
   end else begin
     LengthSet := '';
-    if DataType.Index = dbdtEnum then
-      LengthSet := OrgSource;
+    if DataType.Index = dbdtEnum then begin
+      // Assign PostgreSQL enum type to LengthSet, so we can provide it in table editor
+      // Some enum types are wrapped in double quotes
+      LengthSet := OrgSource.Trim([FConnection.QuoteChar]);
+    end;
   end;
   Unsigned :=  ExecRegExpr('\bunsigned\b', Source.ToLowerInvariant);
   ZeroFill := ExecRegExpr('\bzerofill\b', Source.ToLowerInvariant);
@@ -10927,8 +10954,12 @@ function TTableColumn.AutoIncName: String;
 function TTableColumn.FullDataType: String;
 begin
   Result := DataType.Name;
-  if not LengthSet.IsEmpty then
-    Result := Result + '(' + LengthSet + ')';
+  if not LengthSet.IsEmpty then begin
+    if (DataType.Index = dbdtEnum) and (FConnection.NamedEnums.IndexOfName(LengthSet) > -1) then
+      Result := LengthSet
+    else
+      Result := Result + '(' + LengthSet + ')';
+  end;
 end;
 
 
diff --git a/source/dbstructures.pas b/source/dbstructures.pas
@@ -50,7 +50,7 @@ interface
     qForeignKeyDrop, qGetTableColumns, qGetCollations, qGetCollationsExtended, qGetCharsets,
     qGetReverseForeignKeys, qExplain, qSetTimezone,
     qShowFunctionStatus, qShowProcedureStatus, qShowTriggers, qShowEvents, qShowCreateTrigger,
-    qHelpKeyword, qShowWarnings, qGetSubDataTypes);
+    qHelpKeyword, qShowWarnings, qGetEnumTypes);
   TSqlProvider = class
     strict protected
       FNetType: TNetType;
diff --git a/source/dbstructures.postgresql.pas b/source/dbstructures.postgresql.pas
@@ -727,20 +727,20 @@ function TPostgreSQLProvider.GetSql(AId: TQueryId): string;
       ' WHERE pg_class.relkind=''r'''+
       '   AND pg_namespace.nspname=:EscapedDatabase'+
       '   AND pg_class.relname=:EscapedName';
-    qGetSubDataTypes: Result := IfThen(
+    qGetEnumTypes: Result := IfThen(
       FServerVersion >= 90000,
       'SELECT ' +
       '  n.nspname AS enum_schema, ' +
       '  t.typname AS enum_name, ' +
-      '  string_agg(e.enumlabel, '','' ORDER BY e.enumsortorder) AS enum_labels ' +
+      '  string_agg(e.enumlabel, ''|'' ORDER BY e.enumsortorder) AS enum_labels ' +
       'FROM pg_type AS t ' +
       'JOIN pg_enum AS e ' +
       '  ON t.oid = e.enumtypid ' +
       'JOIN pg_namespace AS n ' +
       '  ON n.oid = t.typnamespace ' +
-      'WHERE t.typtype = ''%s'' ' +
+      'WHERE t.typtype = ''e'' ' +
       'GROUP BY n.nspname, t.typname ' +
-      'ORDER BY n.nspname, t.typname',
+      'ORDER BY UPPER(t.typname)',
       '' // ServerVersion < 9
       );
     else Result := inherited;
diff --git a/source/table_editor.pas b/source/table_editor.pas
@@ -1866,7 +1866,6 @@ procedure TfrmTableEditor.listColumnsCreateEditor(Sender: TBaseVirtualTree;
         EnumEditor.AllowCustomText := True;
         EnumEditor.ItemMustExist := False;
         EnumEditor.ValueList := Explode('|', Col.DataType.Names);
-        EnumEditor.ValueList.Sort;
         EnumEditor.ValueList.Insert(0, '');
         EditLink := EnumEditor;
       end;
