Search the Community

Hello! I am trying to create table at postgres SQL server with the following cod: procedure CreateTableTest(); var vTable : TFDTable; vPGConn : TFDConnection; vConDefs : TStringList; i, vSize : integer; begin vConDefs:=TStringList.Create(); vConDefs.AddPair('Server', '10.190.18.252'); vConDefs.AddPair('Port', '5432'); vConDefs.AddPair('User_Name', 'XXXXXX'); vConDefs.AddPair('Password', 'YYYYYYYYYYYYYYYY'); vConDefs.AddPair('Database', 'postgres'); vConDefs.AddPair('Pooled', 'True'); vConDefs.AddPair('POOL_MaximumItems', '100'); FDManager.AddConnectionDef('PG_TEST', 'PG', vConDefs); vConDefs.Clear(); vSize:=0; vTable:= TFDTable.Create(nil); vTable.TableName:='test_table'; vTable.SchemaName:='test_schem'; vTable.FieldDefs.Add('login', ftLargeint, vSize, true); vTable.FieldDefs.Add('ticket', ftLargeint, vSize, true); vTable.FieldDefs.Add('exec_time', ftDateTime, vSize, true); vTable.FieldDefs.Add('req_price', ftFloat, vSize, true); vTable.IndexDefs.Clear(); vTable.IndexDefs.Add(format('%s_pkey', [vTable.TableName]), 'login;ticket', [ixPrimary]); vPGConn:=TFDConnection.Create(nil); vTable.Connection := vPGConn; vTable.Connection.ConnectionDefName:='PG_TEST'; if not (vTable.Connection.Ping()) then vTable.Connection.Open(); if not(vTable.Exists) then try vTable.CreateTable(False); Log.WriteLog('<TFDCustomParams.CreateTable()> Table %s created at', [vTable.FullTableName]); except on e:EPgNativeException do begin Log.WriteLog('<TFDCustomParams.CreateTable()> Table %s create failed: [%s:%s]', [vTable.FullTableName, E.ClassName, E.Message], lmtERROR); for i:=0 to e.ErrorCount-1 do begin Log.WriteLog('%sDetails: [%s]', [#9, e.Errors[i].DetailedMessage], lmtERROR); Log.WriteLog('%sHint: [%s]', [#9, e.Errors[i].Hint], lmtERROR); Log.WriteLog('%sContext: [%s]', [#9, e.Errors[i].Context], lmtERROR); Log.WriteLog('%sInternalQuery: [%s]', [#9, e.Errors[i].InternalQuery], lmtERROR); Log.WriteLog('%sConstraintName: [%s]', [#9, e.Errors[i].ConstraintName], lmtERROR); end; end; on e:Exception do begin Log.WriteLog('<TFDCustomParams.CreateTable()> Table %s create failed: [%s:%s]', [vTable.FullTableName, E.ClassName, E.Message], lmtERROR); end; end; FreeAndNil(vPGConn); FreeAndNil(vConDefs); FreeAndNil(vTable); end; Code works perfectly, it creates table, but after creating it tries to define primary key, and at this moment it failes with message: "First chance exception at $00007FFAFEA0EF5C. Exception class EPgNativeException with message '[FireDAC][Phys][PG][libpq] ERROR: syntax error at or near "."'" I've look postgres logs and discovered the next error there: 2023-07-10 11:43:46.603 CEST [2263089] postgres@postgres ERROR: syntax error at or near "." at character 62 2023-07-10 11:43:46.603 CEST [2263089] postgres@postgres STATEMENT: ALTER TABLE test_schem.test_table ADD CONSTRAINT "test_schem"."pk_test_table" PRIMARY KEY ("login", ticket) Obviusly staetment generated by IFDPhysCommandGenerator is wrong after ADD CONSTRAINT there have to be just constraint name, without schema name. I am using Delphi 11.1 and postgres 14.8. Also I am connecting to default schem "public", if I create table in this schem it will works without errors and creates table with primary key as well. Well, the main question of this post is: please help me to define what I am doing wrong?