TPgTable bug when using IDENTITY column

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
apriem
Posts: 20
Joined: Mon 06 Oct 2014 07:26

TPgTable bug when using IDENTITY column

Post by apriem » 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 :

Code: Select all

CREATE TABLE TESTTABLE (AAA integer primary key GENERATED ALWAYS AS IDENTITY, BBB TEXT);
Then, use the following Delphi function to APPEND a record :

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;
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.

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: TPgTable bug when using IDENTITY column

Post by oleg0k » Fri 17 Jul 2020 12:43

Hello,

Try to add the line

Code: Select all

objTable.Options.RequiredFields := False;
before

Code: Select all

objTable.Open();
wbr, Oleg
Devart Team

apriem
Posts: 20
Joined: Mon 06 Oct 2014 07:26

Re: TPgTable bug when using IDENTITY column

Post by apriem » Fri 17 Jul 2020 14:20

Thanks. This seems to solve our problem. :)

I cannot find this property in the online documentation. What does it do? Does this disable in-component checks for required fields? If so, the database server itself will also check these contstraints, so that is something we would be able to live with.

Do you expect other problems to arise when using these IDENTITY columns? Since PostgreSQL advises to use these IDENTITY columns since PG10... Will PgDac fully support these columns?

Thanks for the workaround for now!

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: TPgTable bug when using IDENTITY column

Post by oleg0k » Tue 21 Jul 2020 08:34

Hello,

PgDAC fully supports the IDENTITY column. Please refer to our documentation for information on the RequiredFields property: https://www.devart.com/pgdac/docs/devar ... fields.htm

wbr, Oleg
Devart Team

Post Reply