PostgreSQL Database

PostgreSQL Database — Skill

Use this skill when working with PostgreSQL database in Delphi projects via FireDAC.

When to Use

• When configuring FireDAC connection with PostgreSQL
• When creating tables, sequences, functions, triggers and views
• When implementing Repositories with FireDAC + PostgreSQL
• When working with advanced types (JSONB, Arrays, UUID, ENUM)
• When implementing UPSERT, CTEs, Full-Text Search or Window Functions
• When planning schema migrations (versioned scripts)

PostgreSQL Versions

| Version | Relevant News | |--------|----------------------| | 12 | Generated Columns, CTE inlining, Partitioning improvements | | 13 | Incremental sorting, Parallel vacuum, Deduplication in B-tree | | 14 | Multirange types, SEARCH/CYCLE in recursive CTEs | | 15 | MERGE statement, JSON logging, UNIQUE NULL NOT DISTINCT | | 16 | Logical replication from standby, ANY_VALUE(), ICU default collations | | 17 | RETURNING OLD/NEW no MERGE, JSON_TABLE, Identity columns improvements |

Recommendation: Use PostgreSQL 14+ for new projects. Enjoy MERGE, JSONB and partitioning.

FireDAC Connection with PostgreSQL

Minimum Configuration

unit MeuApp.Infra.Database.PostgreSQL.Connection;

interface

uses
  FireDAC.Comp.Client,
  FireDAC.Phys.PG,         //Driver PostgreSQL
  FireDAC.Phys.PGDef,      //Defaults do PostgreSQL
  FireDAC.Stan.Def,
  FireDAC.Stan.Pool,
  FireDAC.DApt;

type
  ///<summary>
  ///PostgreSQL connection factory via FireDAC.
  ///</summary>
  TPostgreSQLConnectionFactory = class
  public
    ///<summary>
    ///Creates and configures a PostgreSQL connection.
    ///</summary>
    ///<param name="AServer">Server address</param>
    ///<param name="ADatabase">Database name</param>
    ///<param name="AUserName">User (default: postgres)</param>
    ///<param name="APassword">Bank password</param>
    ///<param name="APort">Port (default: 5432)</param>
    ///<returns>FireDAC connection configured and opened</returns>
    class function CreateConnection(
      const AServer: string;
      const ADatabase: string;
      const AUserName: string = 'postgres';
      const APassword: string = '';
      APort: Integer = 5432
    ): TFDConnection;

    ///<summary>
    ///Creates connection via full connection string.
    ///</summary>
    class function CreateFromConnectionString(
      const AConnectionString: string
    ): TFDConnection;
  end;

implementation

uses
  System.SysUtils;

class function TPostgreSQLConnectionFactory.CreateConnection(
  const AServer, ADatabase, AUserName, APassword: string;
  APort: Integer): TFDConnection;
begin
  if ADatabase.Trim.IsEmpty then
    raise EArgumentException.Create('ADatabase não pode ser vazio');

  Result := TFDConnection.Create(nil);
  try
    Result.DriverName := 'PG';
    Result.Params.Values['Server'] := AServer;
    Result.Params.Values['Port'] := APort.ToString;
    Result.Params.Database := ADatabase;
    Result.Params.UserName := AUserName;
    Result.Params.Password := APassword;

    { Configurações recomendadas }
    Result.Params.Values['CharacterSet'] := 'UTF8';

    { Opções do driver FireDAC }
    Result.FormatOptions.StrsTrim2Len := True;
    Result.FetchOptions.Mode := fmAll;
    Result.ResourceOptions.AutoReconnect := True;
    Result.TxOptions.Isolation := xiReadCommitted;

    { Schema padrão — 'public' por default, alterar se necessário }
    //Result.Params.Values['MetaDefSchema'] := 'public';

    Result.Connected := True;
  except
    Result.Free;
    raise;
  end;
end;

class function TPostgreSQLConnectionFactory.CreateFromConnectionString(
  const AConnectionString: string): TFDConnection;
begin
  Result := TFDConnection.Create(nil);
  try
    Result.ConnectionString := AConnectionString;
    Result.Connected := True;
  except
    Result.Free;
    raise;
  end;
end;

FDPhysPGDriverLink — Configure Client Library

uses
  FireDAC.Phys.PGWrapper,
  FireDAC.Phys.PG;

var
  LDriverLink: TFDPhysPGDriverLink;
begin
  LDriverLink := TFDPhysPGDriverLink.Create(nil);
  try
    { Apontar libpq.dll customizado (32/64-bit) }
    LDriverLink.VendorLib := 'C:\PostgreSQL\bin\libpq.dll';

    { Windows: precisa também libintl-9.dll, libeay32.dll, ssleay32.dll no PATH }
  finally
    { DriverLink vive por toda a aplicação — criar no DataModule }
  end;
end;

Connection Pooling

{ Via FDManager }
FDManager.ConnectionDefs.AddConnectionDef;
with FDManager.ConnectionDefs.ConnectionDefByName('PG_POOL') do
begin
  DriverID := 'PG';
  Server := 'localhost';
  Port := 5432;
  Database := 'meubanco';
  UserName := 'postgres';
  Password := 'senha';
  Params.Values['CharacterSet'] := 'UTF8';
  Params.Values['Pooled'] := 'True';
  Params.Values['POOL_MaximumItems'] := '50';
  Params.Values['POOL_CleanupTimeout'] := '30000';
  Params.Values['POOL_ExpireTimeout'] := '90000';
end;

SSL/TLS

{ Conexão segura com SSL }
Result.Params.Values['PGAdvanced'] := 'sslmode=require';
{ Para certificado de cliente: }
//Result.Params.Values['PGAdvanced'] :=
//'sslmode=verify-full;sslcert=client-cert.pem;sslkey=client-key.pem;sslrootcert=ca.pem';

Data Types — PostgreSQL Mapping ↔ Delphi

| PostgreSQL | Delphi (FireDAC) | Note | |------------|------------------|------------| | INTEGER / INT4 | ftInteger / AsInteger | 32-bit | | BIGINT / INT8 | ftLargeint / AsLargeInt | 64-bit | | SMALLINT / INT2 | ftSmallint / AsSmallInt | 16-bit | | SERIAL | ftAutoInc / AsInteger | 32-bit auto-increment | | BIGSERIAL | ftAutoInc / AsLargeInt | 64-bit auto-increment | | VARCHAR(N) | ftString / AsString | Limited text | | TEXT | ftMemo / AsString | Unlimited Text | | NUMERIC(P,S) | ftBCD / AsCurrency | Monetary values ​​| | DOUBLE PRECISION | ftFloat / AsFloat | Ponto flutuante | | REAL / FLOAT4 | ftSingle / AsSingle | 32-bit float | | DATE | ftDate / AsDateTime | Date only | | TIME | ftTime / AsDateTime | Just in time | | TIMESTAMP | ftDateTime / AsDateTime | Date + Time (without timezone) | | TIMESTAMPTZ | ftDateTime / AsDateTime | Date + Time (with timezone) | | BOOLEAN | ftBoolean / AsBoolean | TRUE/FALSE native | | UUID | ftGuid / AsString | Use gen_random_uuid() (PG 13+) | | JSON | ftMemo / AsString | JSON text (validated) | | JSONB | ftMemo / AsString | Binary JSON (indexable) | | BYTEA | ftBlob / AsBytes | Binary data | | ARRAY | ftMemo / AsString | PostgreSQL Array as Text | | INET / CIDR | ftString / AsString | Network addresses |

Sequences and Auto-Increment

SERIAL / BIGSERIAL (Legacy)

-- Cria coluna auto-increment automaticamente + sequence
CREATE TABLE customers (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL
);
-- Equivale a criar uma SEQUENCE + DEFAULT nextval('customers_id_seq')

IDENTITY Columns (Modern — SQL Standard)

-- Preferir sobre SERIAL em novos projetos (PG 10+)
CREATE TABLE customers (
  id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  VARCHAR(100) NOT NULL
);

-- GENERATED BY DEFAULT: permite override manual do ID
CREATE TABLE products (
  id    INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  VARCHAR(100) NOT NULL
);

Manual Sequences

CREATE SEQUENCE seq_order_number START WITH 1000 INCREMENT BY 1;

-- Usar no INSERT
INSERT INTO orders (order_number) VALUES (nextval('seq_order_number'));

RETURNING in Delphi

///<summary>
///Insert customer and obtain the id and created_at generated by the bank.
///RETURNING works with Open (same as Firebird).
///</summary>
procedure TPostgreSQLCustomerRepository.Insert(ACustomer: TCustomer);
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := FConnection;
    LQuery.SQL.Text :=
      'INSERT INTO customers (name, cpf, email, status) ' +
      'VALUES (