TPgTable bug when using IDENTITY column
Posted: Thu 16 Jul 2020 10:39
Hello,
I encountered a bug in the TPgTable component. We are using IDENTITY columns in our PostgreSQL database. Since PostgreSQL 10, the recommended way to use autonumbering in Primary Key columns is to use IDENTITY columns, instead of SERIAL columns. Up and to PostgreSQL version 9, SERIAL columns were the way to do this. PostgreSQL 10 introduced IDENTITY columns, and this is now the preferred way to use autonumbering.
To reproduce the issue, create a table :
Then, use the following Delphi function to APPEND a record :
The code will fail. The error message will mention that field AAA needs a value. Probably the TPgTable component does not understand the new way of creating autonumbering columns yet.
We also use TPgQuery components using INSERT statements (like INSERT INTO TESTTABLE (BBB) VALUES ('TEST')). That works fine. But we have a lot of TPgTable components in our code as well, so it is important for us that this works.
I hope you can help with this. An actual fix in the component itself would be great of course, but a workaround could help us out until an actual fix can be implemented in the component.
I tested this using Delphi 10.1 with PgDac 5.2.6, but also using Delphi 10.4 with PgDac 6.2.4.
I encountered a bug in the TPgTable component. We are using IDENTITY columns in our PostgreSQL database. Since PostgreSQL 10, the recommended way to use autonumbering in Primary Key columns is to use IDENTITY columns, instead of SERIAL columns. Up and to PostgreSQL version 9, SERIAL columns were the way to do this. PostgreSQL 10 introduced IDENTITY columns, and this is now the preferred way to use autonumbering.
To reproduce the issue, create a table :
Code: Select all
CREATE TABLE TESTTABLE (AAA integer primary key GENERATED ALWAYS AS IDENTITY, BBB TEXT);
Code: Select all
procedure TestFunction();
var
objTable: TPgTable;
begin
objTable := TPgTable.Create(Nil);
objTable.Connection := DataCon;
objTable.FetchAll := False;
objTable.DMLRefresh := True;
objTable.TableName := 'TESTTABLE';
objTable.Open();
objTable.Append();
objTable.FieldByName('BBB').AsString := 'TEST';
objTable.Post();
objTable.Free();
end;
We also use TPgQuery components using INSERT statements (like INSERT INTO TESTTABLE (BBB) VALUES ('TEST')). That works fine. But we have a lot of TPgTable components in our code as well, so it is important for us that this works.
I hope you can help with this. An actual fix in the component itself would be great of course, but a workaround could help us out until an actual fix can be implemented in the component.
I tested this using Delphi 10.1 with PgDac 5.2.6, but also using Delphi 10.4 with PgDac 6.2.4.