shalapai

Hello, it works if I assign 'public' to SchemaName and if DB user is connected with default schema (search_path = '')

I need table to be created in apropriate schem, with primary key, if I not define "vTable.SchemaName:='test_schem';", then table will be created in schem which defined by default ("public") or in "search_path" parameter for conected user, yes I can try to override default schema name, but what if task needs to create tables in 10, 50 schems, everytime override default schema?... I think that is bad solution. For now I see the best solution provided @Lajos Juhász , it's to generate SQL in code manualy and don't rely on auto generated statements. But with regarding to above, I am starting to think that this is bug, may be I am wrong and someone can point me to mistake or something that I do not consider in code or just confirm that this is really bug in FireDAC

In last post I've used another field name: loginid - and that does not helped. The problem not with field name at all! The main problem is with CONSTRAINT NAME "test_schem"."pk_test_table", it must not be named with schema name, but as I defined in vTable.IndexDefs.Add(format('%s_pkey', [vTable.TableName]), 'loginid', [ixPrimary]); at the first parameter

No, the same behavior: Postgres directly points at constraint name: "test_schem"."pk_test_table". if I manualy edit statment to 'ALTER TABLE test_schem.test_table ADD CONSTRAINT "pk_test_table" PRIMARY KEY (loginid)' and run it, it works perfectly

Suppose that login is a keyword in postgres, ticket - not

I meant that I've added IndexDefs.Clear; But it does not helped/

Yes, but that is not case of issue! I've fixed this. Yes, I'am sure, you have to list them separated by semi-colon. In any case if there will be only one field, it does not helps. Also as you can see log of postgres, generator provided right statement in the part of defining primary key field list.

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?