处理postgresql表的唯一和身份自动增量云的最佳方法

Question

我得到这个错误："bankid“字段必须有一个值。

为了解决这个问题，我必须使用Uniquery字段编辑器添加字段，并将"required“属性设置为False，同时将autogeneratedvalue设置为arAutoInc。

这是唯一的方法吗?向Uniquery添加字段？

表：

CREATE TABLE public.banks (
  bank    varchar(50),
  branch  varchar(80),
  "no"    varchar(30),
  bankid  integer NOT NULL GENERATED ALWAYS AS IDENTITY,
)
WITH (
    OIDS = FALSE
  );

唯一组件：

  object UniQuery2: TUniQuery
    SQLInsert.Strings = (
      'INSERT INTO "Banks"'
      '  (bank, branch, no)'
      'VALUES'
      '  (:bank, :branch, :no)  RETURNING bankid;')
    SQLUpdate.Strings = (
      'update "Banks" set bank=:bank, branch=:branch, "no"=:no'
      'where bankid=:bankid')
    SQLLock.Strings = (
      '')
    SQLRefresh.Strings = (
      'SELECT bank, branch, no, bankid FROM "Banks"'
      'WHERE'
      '  bankid = :bankid')
    SQLRecCount.Strings = (
      'SELECT count(*) FROM ('
      'SELECT * FROM "Banks"'
      ''
      ') t')
    Connection = UniConnection1
    SQL.Strings = (
      'select * from "Banks" order by bank,branch,"no"')
    Options.ReturnParams = True
    Left = 64
    Top = 80
    object UniQuery2bank: TStringField
      FieldName = 'bank'
      Required = True
      Size = 50
    end
    object UniQuery2branch: TStringField
      FieldName = 'branch'
      Required = True
      Size = 80
    end
    object UniQuery2no: TStringField
      FieldName = 'no'
      Required = True
      Size = 30
    end
    object UniQuery2bankid: TIntegerField
      AutoGenerateValue = arAutoInc
      FieldName = 'bankid'
    end

  end

delphi代码：

UniQuery2.Options.DefaultValues := True;
uniquery2.open;
UniQuery2.Append;
UniQuery2.fieldByName('bank').AsString:=sEdit1.Text;
UniQuery2.fieldByName('branch').AsString:=sEdit2.Text;
UniQuery2.fieldByName('no').AsString:=sEdit2.Text;
UniQuery2.Post;

delphi 10.2.3 postgresql 10.8

Answer 1

Ago解决方案的参数化形式可以解决Uniquery未附加到数据源和Dbgrid时的问题

uq.sql.text:='upadte table1 set field1=:f';
uq.parambyname('f').asinteger:=somevalue;
uq.execute;

当uniquery合并了数据和计算字段以便与dbgrid一起使用时：

uq.append;
uq.fieldbyname('field1').asinteger:=somevalue;
//****
uq.filedbyname('id').required:=false;
//****
uq.post;