postgresql-patterns

PostgreSQL Database — Rules

Use these rules when developing with PostgreSQL database via FireDAC.

Connection Required

{ Configuração base }
FConnection.DriverName := 'PG';
FConnection.Params.Values['Server'] := 'localhost';
FConnection.Params.Values['Port'] := '5432';
FConnection.Params.Database := 'meubanco';
FConnection.Params.UserName := 'postgres';
FConnection.Params.Password := 'senha';
FConnection.Params.Values['CharacterSet'] := 'UTF8';

Auto-Increment

Approach	Version	Example
SERIAL	All	Legacy — creates implicit sequence
BIGSERIAL	All	Legacy — 64-bit
GENERATED ALWAYS AS IDENTITY	PG 10+	✅ Modern — SQL Standard
GENERATED BY DEFAULT AS IDENTITY	PG 10+	Allows manual override
nextval('seq')	All	Manual sequence

RETURNING — Same Rule as Firebird

//❌ ExecSQL loses the returned value!
LQuery.SQL.Text := 'INSERT INTO ... RETURNING id';
LQuery.ExecSQL;

//✅ Open to receive RETURNING
LQuery.SQL.Text := 'INSERT INTO ... RETURNING id';
LQuery.Open;
LId := LQuery.FieldByName('id').AsInteger;

UPSERT — INSERT ... ON CONFLICT

LQuery.SQL.Text :=
  'INSERT INTO customers (cpf, name, email) ' +
  'VALUES (:cpf, :name, :email) ' +
  'ON CONFLICT (cpf) DO UPDATE SET ' +
  '  name = EXCLUDED.name, email = EXCLUDED.email ' +
  'RETURNING id';
LQuery.Open;

JSONB — Semi-Structured Data

{ Gravar JSONB }
LQuery.ParamByName('settings').AsString := '{"theme":"dark"}';
{ Cast necessário no SQL: :param::jsonb }

{ Ler campo JSONB }
LQuery.SQL.Text := 'SELECT settings->>''theme'' FROM ... WHERE id = :id';

Functions and Procedures

Type	Delphi Call
Scalar function	SELECT fn_nome(:param) + Open
Function RETURNS TABLE	SELECT * FROM fn_nome(:param) + Open
Procedure (PG 11+)	CALL sp_nome(:param) + ExecSQL

Transactions

{ Transação explícita }
FConnection.StartTransaction;
try
  FRepoA.Insert(LObjA);
  FRepoB.Insert(LObjB);
  FConnection.Commit;
except
  FConnection.Rollback;
  raise;
end;