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

SEARCH

CYCLE

MERGE

UNIQUE NULL NOT DISTINCT

ANY_VALUE()

RETURNING OLD/NEW

MERGE

JSON_TABLE

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

INTEGER

INT4

ftInteger

AsInteger

BIGINT

INT8

ftLargeint

AsLargeInt

SMALLINT

INT2

ftSmallint

AsSmallInt

SERIAL

ftAutoInc

AsInteger

BIGSERIAL

ftAutoInc

AsLargeInt

VARCHAR(N)

ftString

AsString

TEXT

ftMemo

AsString

NUMERIC(P,S)

ftBCD

AsCurrency

DOUBLE PRECISION

ftFloat

AsFloat

REAL

FLOAT4

ftSingle

AsSingle

DATE

ftDate

AsDateTime

TIME

ftTime

AsDateTime

TIMESTAMP

ftDateTime

AsDateTime

TIMESTAMPTZ

ftDateTime

AsDateTime

BOOLEAN

ftBoolean

AsBoolean

TRUE

FALSE

UUID

ftGuid

AsString

gen_random_uuid()

JSON

ftMemo

AsString

JSONB

ftMemo

AsString

BYTEA

ftBlob

AsBytes

ARRAY

ftMemo

AsString

INET

CIDR

ftString

AsString

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 (:name, :cpf, :email, :status) ' +
      'RETURNING id, created_at';
    LQuery.ParamByName('name').AsString := ACustomer.Name;
    LQuery.ParamByName('cpf').AsString := ACustomer.Cpf;
    LQuery.ParamByName('email').AsString := ACustomer.Email;
    LQuery.ParamByName('status').AsSmallInt := Ord(ACustomer.Status);

    { RETURNING: usar Open para receber o resultado }
    LQuery.Open;
    ACustomer.Id := LQuery.FieldByName('id').AsInteger;
    ACustomer.CreatedAt := LQuery.FieldByName('created_at').AsDateTime;
  finally
    LQuery.Free;
  end;
end;

UPSERT — INSERT ... ON CONFLICT

-- Inserir ou atualizar se já existir (pela constraint unique)
INSERT INTO customers (cpf, name, email, status)
VALUES (:cpf, :name, :email, :status)
ON CONFLICT (cpf) DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email,
  status = EXCLUDED.status;

-- Ignorar se já existir (sem atualizar)
INSERT INTO customer_tags (customer_id, tag)
VALUES (:customer_id, :tag)
ON CONFLICT DO NOTHING;

In Delphi:

procedure TPostgreSQLCustomerRepository.Upsert(ACustomer: TCustomer);
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := FConnection;
    LQuery.SQL.Text :=
      'INSERT INTO customers (cpf, name, email, status) ' +
      'VALUES (:cpf, :name, :email, :status) ' +
      'ON CONFLICT (cpf) DO UPDATE SET ' +
      '  name = EXCLUDED.name, ' +
      '  email = EXCLUDED.email, ' +
      '  status = EXCLUDED.status ' +
      'RETURNING id';
    LQuery.ParamByName('cpf').AsString := ACustomer.Cpf;
    LQuery.ParamByName('name').AsString := ACustomer.Name;
    LQuery.ParamByName('email').AsString := ACustomer.Email;
    LQuery.ParamByName('status').AsSmallInt := Ord(ACustomer.Status);
    LQuery.Open;
    ACustomer.Id := LQuery.FieldByName('id').AsInteger;
  finally
    LQuery.Free;
  end;
end;

JSONB — Semi-Structured Data

Storage and Query

-- Tabela com coluna JSONB
CREATE TABLE customer_settings (
  customer_id  INTEGER REFERENCES customers(id),
  settings     JSONB NOT NULL DEFAULT '{}',
  PRIMARY KEY (customer_id)
);

-- Inserir JSON
INSERT INTO customer_settings (customer_id, settings)
VALUES (1, '{"theme": "dark", "language": "pt-BR", "notifications": true}');

-- Consultar campo específico
SELECT settings->>'theme' AS theme FROM customer_settings WHERE customer_id = 1;

-- Filtrar por valor JSON
SELECT * FROM customer_settings WHERE settings @> '{"theme": "dark"}';

-- Índice GIN para busca rápida em JSONB
CREATE INDEX idx_settings_gin ON customer_settings USING GIN (settings);

In Delphi:

{ Inserir JSONB }
LQuery.SQL.Text :=
  'INSERT INTO customer_settings (customer_id, settings) ' +
  'VALUES (:customer_id, :settings::jsonb)';
LQuery.ParamByName('customer_id').AsInteger := ACustomerId;
LQuery.ParamByName('settings').AsString := AJsonString;
LQuery.ExecSQL;

{ Ler campo JSONB }
LQuery.SQL.Text :=
  'SELECT settings->>''theme'' AS theme ' +
  'FROM customer_settings WHERE customer_id = :id';
LQuery.ParamByName('id').AsInteger := ACustomerId;
LQuery.Open;
LTheme := LQuery.FieldByName('theme').AsString;

Full-Text Search (FTS)

-- Coluna tsvector para busca textual
ALTER TABLE products ADD COLUMN search_vector TSVECTOR;

-- Trigger para atualizar automaticamente
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('portuguese', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('portuguese', COALESCE(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_product_search BEFORE INSERT OR UPDATE
  ON products FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- Índice GIN para FTS
CREATE INDEX idx_product_search ON products USING GIN (search_vector);

-- Buscar
SELECT * FROM products
WHERE search_vector @@ plainto_tsquery('portuguese', 'camisa azul')
ORDER BY ts_rank(search_vector, plainto_tsquery('portuguese', 'camisa azul')) DESC;

In Delphi:

function TProductRepository.Search(const ASearchTerm: string): TObjectList<TProduct>;
var
  LQuery: TFDQuery;
begin
  Result := TObjectList<TProduct>.Create(True);
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := FConnection;
    LQuery.SQL.Text :=
      'SELECT id, name, price, description ' +
      'FROM products ' +
      'WHERE search_vector @@ plainto_tsquery(''portuguese'', :term) ' +
      'ORDER BY ts_rank(search_vector, plainto_tsquery(''portuguese'', :term)) DESC ' +
      'LIMIT :limit';
    LQuery.ParamByName('term').AsString := ASearchTerm;
    LQuery.ParamByName('limit').AsInteger := 50;
    LQuery.Open;

    while not LQuery.Eof do
    begin
      Result.Add(MapToProduct(LQuery));
      LQuery.Next;
    end;
  finally
    LQuery.Free;
  end;
end;

CTEs (Common Table Expressions)

-- CTE para queries complexas e legíveis
WITH active_customers AS (
  SELECT id, name, email
  FROM customers
  WHERE status = 0
),
customer_orders AS (
  SELECT customer_id, COUNT(*) AS total_orders, SUM(total_amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT ac.name, ac.email, co.total_orders, co.total_spent
FROM active_customers ac
LEFT JOIN customer_orders co ON co.customer_id = ac.id
ORDER BY co.total_spent DESC NULLS LAST;

Recursive CTE

-- Hierarquia de categorias
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

WindowFunctions

-- Ranking de clientes por valor gasto
SELECT
  c.name,
  SUM(o.total_amount) AS total_spent,
  RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS ranking,
  ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC) AS row_num,
  SUM(o.total_amount) / SUM(SUM(o.total_amount)) OVER () * 100 AS percent_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Média móvel de vendas por dia
SELECT
  order_date::DATE AS day,
  SUM(total_amount) AS daily_total,
  AVG(SUM(total_amount)) OVER (ORDER BY order_date::DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM orders
GROUP BY order_date::DATE
ORDER BY day;

Functions (PL/pgSQL)

-- Function que retorna valor (equivale a function no Delphi)
CREATE OR REPLACE FUNCTION fn_customer_full_name(p_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
  v_name VARCHAR;
BEGIN
  SELECT name INTO v_name FROM customers WHERE id = p_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Customer % not found', p_id;
  END IF;
  RETURN v_name;
END;
$$ LANGUAGE plpgsql;

-- Function que retorna tabela (equivale a Selectable Procedure no Firebird)
CREATE OR REPLACE FUNCTION fn_customers_by_status(p_status SMALLINT)
RETURNS TABLE (
  o_id     INTEGER,
  o_name   VARCHAR,
  o_email  VARCHAR,
  o_status SMALLINT
) AS $$
BEGIN
  RETURN QUERY
    SELECT id, name, email, status
    FROM customers
    WHERE status = p_status
    ORDER BY name;
END;
$$ LANGUAGE plpgsql;

-- Procedure (PG 11+ — sem retorno, apenas ação)
CREATE OR REPLACE PROCEDURE sp_deactivate_customer(p_id INTEGER)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE customers SET status = 1, updated_at = NOW() WHERE id = p_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Customer % not found', p_id;
  END IF;
END;
$$;

Call in Delphi:

{ Function escalar }
LQuery.SQL.Text := 'SELECT fn_customer_full_name(:id)';
LQuery.ParamByName('id').AsInteger := ACustomerId;
LQuery.Open;
LFullName := LQuery.Fields[0].AsString;

{ Function que retorna table (como SELECT) }
LQuery.SQL.Text := 'SELECT * FROM fn_customers_by_status(:status)';
LQuery.ParamByName('status').AsSmallInt := Ord(csActive);
LQuery.Open;

{ Procedure (PG 11+) }
LQuery.SQL.Text := 'CALL sp_deactivate_customer(:id)';
LQuery.ParamByName('id').AsInteger := ACustomerId;
LQuery.ExecSQL;

ENUM Types

-- Tipo enum nativo do PostgreSQL
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  status      order_status NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Inserir
INSERT INTO orders (customer_id, status) VALUES (1, 'processing');

In Delphi (map to Pascal enum):

type
  TOrderStatus = (osPending, osProcessing, osShipped, osDelivered, osCancelled);

const
  ORDER_STATUS_NAMES: array[TOrderStatus] of string = (
    'pending', 'processing', 'shipped', 'delivered', 'cancelled'
  );

function StringToOrderStatus(const AValue: string): TOrderStatus;
var
  LStatus: TOrderStatus;
begin
  for LStatus := Low(TOrderStatus) to High(TOrderStatus) do
    if SameText(ORDER_STATUS_NAMES[LStatus], AValue) then
      Exit(LStatus);
  raise EArgumentException.CreateFmt('Status inválido: "%s"', [AValue]);
end;

{ Ler do banco }
LOrder.Status := StringToOrderStatus(LQuery.FieldByName('status').AsString);

{ Gravar no banco }
LQuery.ParamByName('status').AsString := ORDER_STATUS_NAMES[AOrder.Status];

UUID as Primary Key

-- Usar gen_random_uuid() nativo (PG 13+)
CREATE TABLE sessions (
  id         UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  token      VARCHAR(255),
  expires_at TIMESTAMPTZ
);

-- Para PG < 13: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Então: DEFAULT uuid_generate_v4()

In Delphi:

LQuery.SQL.Text :=
  'INSERT INTO sessions (user_id, token, expires_at) ' +
  'VALUES (:user_id, :token, :expires_at) RETURNING id';
LQuery.ParamByName('user_id').AsInteger := AUserId;
LQuery.ParamByName('token').AsString := AToken;
LQuery.ParamByName('expires_at').AsDateTime := AExpiresAt;
LQuery.Open;
LSessionId := LQuery.FieldByName('id').AsString; { UUID como string }

Transactions and Isolation Levels

Isolation Levels in PostgreSQL

xiReadCommitted

xiRepeatableRead

xiSerializable

Explicit Transaction

procedure ExecuteInTransaction(AConnection: TFDConnection; AProc: TProc);
begin
  AConnection.StartTransaction;
  try
    AProc;
    AConnection.Commit;
  except
    AConnection.Rollback;
    raise;
  end;
end;

SAVEPOINT (Partial Transaction)

{ PostgreSQL suporta SAVEPOINT para rollback parcial }
FConnection.StartTransaction;
try
  FCustomerRepo.Insert(LCustomer);

  FConnection.ExecSQL('SAVEPOINT before_order');
  try
    FOrderRepo.Insert(LOrder);
  except
    FConnection.ExecSQL('ROLLBACK TO SAVEPOINT before_order');
    { Customer foi salvo, order não }
  end;

  FConnection.Commit;
except
  FConnection.Rollback;
  raise;
end;

LISTEN / NOTIFY (Bank Events)

-- No PostgreSQL: notificações assíncronas
-- Em uma trigger:
CREATE OR REPLACE FUNCTION notify_new_order() RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('new_order', json_build_object('id', NEW.id, 'customer_id', NEW.customer_id)::TEXT);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_notify AFTER INSERT ON orders
  FOR EACH ROW EXECUTE FUNCTION notify_new_order();

{ No Delphi: escutar eventos via FireDAC Event Alerter }
uses
  FireDAC.Phys.PG;

var
  LAlerter: TFDEventAlerter;
begin
  LAlerter := TFDEventAlerter.Create(nil);
  try
    LAlerter.Connection := FConnection;
    LAlerter.DriverName := 'PG';
    LAlerter.Names.Text := 'new_order';
    LAlerter.Options.Timeout := 0;
    LAlerter.OnAlert := HandleNewOrderEvent;
    LAlerter.Active := True;
  finally
    { Manter vivo enquanto a aplicação rodar }
  end;
end;

procedure TMyService.HandleNewOrderEvent(ASender: TFDCustomEventAlerter;
  const AEventName: string; const AArgument: Variant);
begin
  { AArgument contém o payload JSON enviado pelo pg_notify }
  if AEventName = 'new_order' then
    ProcessNewOrderNotification(VarToStr(AArgument));
end;

Schemas

-- Schemas para organizar objetos do banco
CREATE SCHEMA IF NOT EXISTS app;
CREATE SCHEMA IF NOT EXISTS audit;

-- Tabelas em schemas diferentes
CREATE TABLE app.customers (...);
CREATE TABLE audit.log (...);

-- Search path: define schemas visíveis por padrão
SET search_path TO app, public;

In Delphi:

{ Definir schema padrão na conexão }
FConnection.Params.Values['MetaDefSchema'] := 'app';

{ Ou via SQL }
FConnection.ExecSQL('SET search_path TO app, public');

Partitioning (PG 10+)

-- Particionamento por intervalo de data
CREATE TABLE orders (
  id          SERIAL,
  customer_id INTEGER NOT NULL,
  order_date  DATE NOT NULL,
  total       NUMERIC(15,2),
  status      SMALLINT DEFAULT 0
) PARTITION BY RANGE (order_date);

-- Partições por ano
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_2026 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Índice na tabela particionada (criado em todas as partições)
CREATE INDEX idx_orders_customer ON orders (customer_id);

Schema Creation — Migration Script

/* migration_001_initial_schema.sql */

/* ===== Extensions ===== */
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

/* ===== ENUM Types ===== */
CREATE TYPE customer_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

/* ===== Tables ===== */
CREATE TABLE IF NOT EXISTS customers (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  cpf        VARCHAR(14) UNIQUE,
  email      VARCHAR(150),
  status     customer_status NOT NULL DEFAULT 'active',
  notes      TEXT,
  metadata   JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS products (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  price       NUMERIC(15, 2) NOT NULL DEFAULT 0,
  stock_qty   INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
  description TEXT,
  tags        TEXT[] DEFAULT '{}',
  status      customer_status NOT NULL DEFAULT 'active',
  search_vector TSVECTOR,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS orders (
  id            INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id   INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  order_date    TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  total_amount  NUMERIC(15, 2) NOT NULL DEFAULT 0,
  status        order_status NOT NULL DEFAULT 'pending',
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS order_items (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  NUMERIC(15, 2) NOT NULL,
  total_price NUMERIC(15, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

/* ===== Indices ===== */
CREATE INDEX idx_customer_name     ON customers (name);
CREATE INDEX idx_customer_cpf      ON customers (cpf);
CREATE INDEX idx_order_date        ON orders (order_date);
CREATE INDEX idx_order_customer    ON orders (customer_id);
CREATE INDEX idx_item_order        ON order_items (order_id);
CREATE INDEX idx_product_search    ON products USING GIN (search_vector);

/* ===== Functions ===== */
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customer_updated BEFORE UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Schema migration in Delphi

///<summary>
///Checks whether a table exists in PostgreSQL.
///</summary>
function TableExists(AConnection: TFDConnection; const ATableName: string;
  const ASchema: string = 'public'): Boolean;
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := AConnection;
    LQuery.SQL.Text :=
      'SELECT COUNT(*) FROM information_schema.tables ' +
      'WHERE table_schema = :schema AND table_name = :name';
    LQuery.ParamByName('schema').AsString := ASchema;
    LQuery.ParamByName('name').AsString := ATableName.ToLower;
    LQuery.Open;
    Result := LQuery.Fields[0].AsInteger > 0;
  finally
    LQuery.Free;
  end;
end;

///<summary>
///Checks whether a column exists in a table.
///</summary>
function ColumnExists(AConnection: TFDConnection;
  const ATableName, AColumnName: string;
  const ASchema: string = 'public'): Boolean;
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := AConnection;
    LQuery.SQL.Text :=
      'SELECT COUNT(*) FROM information_schema.columns ' +
      'WHERE table_schema = :schema AND table_name = :table AND column_name = :col';
    LQuery.ParamByName('schema').AsString := ASchema;
    LQuery.ParamByName('table').AsString := ATableName.ToLower;
    LQuery.ParamByName('col').AsString := AColumnName.ToLower;
    LQuery.Open;
    Result := LQuery.Fields[0].AsInteger > 0;
  finally
    LQuery.Free;
  end;
end;

PostgreSQL Error Handling

except
  on E: EFDDBEngineException do
  begin
    case E.Kind of
      ekUKViolated:
        raise EDuplicateException.Create('Registro duplicado: ' + E.Message);
      ekFKViolated:
        raise EDependencyException.Create('Violação de FK: ' + E.Message);
      ekRecordLocked:
        raise EConflictException.Create('Registro bloqueado por outra transação');
      ekServerGone:
        raise EConnectionLostException.Create('Conexão com PostgreSQL perdida');
    else
      raise;
    end;
  end;
end;

{ Verificar código de erro PostgreSQL específico }
except
  on E: EFDDBEngineException do
  begin
    { Códigos SQLSTATE do PostgreSQL: }
    { 23505 = unique_violation }
    { 23503 = foreign_key_violation }
    { 23502 = not_null_violation }
    { 40001 = serialization_failure }
    { 40P01 = deadlock_detected }
    if E.Errors[0].ErrorCode = 23505 then
      raise EDuplicateException.Create('Valor duplicado')
    else
      raise;
  end;
end;

Useful Extensions

-- pgcrypto: criptografia
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT crypt('senha123', gen_salt('bf'));  -- bcrypt hash

-- pg_trgm: busca por similaridade (LIKE otimizado)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customer_name_trgm ON customers USING GIN (name gin_trgm_ops);
SELECT * FROM customers WHERE name % 'Joao';  -- busca fuzzy

-- uuid-ossp: geração de UUID (PG < 13, se gen_random_uuid não disponível)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

PostgreSQL Anti-Patterns to Avoid

//❌ Concatenar SQL — SQL Injection
LQuery.SQL.Text := 'SELECT * FROM customers WHERE name = ''' + AName + '''';

//✅ Parameterized parameters
LQuery.SQL.Text := 'SELECT * FROM customers WHERE name = :name';
LQuery.ParamByName('name').AsString := AName;

//❌ ExecSQL with RETURNING — loses the result
LQuery.SQL.Text := 'INSERT INTO ... RETURNING id';
LQuery.ExecSQL;

//✅ Open com RETURNING
LQuery.SQL.Text := 'INSERT INTO ... RETURNING id';
LQuery.Open;

//❌ SELECT * on large tables
LQuery.SQL.Text := 'SELECT * FROM orders';

//✅ Select only necessary columns + LIMIT
LQuery.SQL.Text := 'SELECT id, customer_id, total FROM orders LIMIT :limit';

//❌ N+1 queries (loop with query inside)
for I := 0 to LCustomers.Count - 1 do
begin
  LQuery.SQL.Text := 'SELECT COUNT(*) FROM orders WHERE customer_id = :id';
  // ...
end;

//✅ JOIN ou subquery
LQuery.SQL.Text :=
  'SELECT c.*, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count ' +
  'FROM customers c';

//❌ Use SERIAL when IDENTITY is better
CREATE TABLE t (id SERIAL PRIMARY KEY);

//✅ Usar IDENTITY (SQL Standard)
CREATE TABLE t (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

//❌ Ignore indexes on filtered columns
SELECT * FROM orders WHERE customer_id = 1;  -- sem índice = full scan

//✅ Create indexes for columns used in WHERE, JOIN, ORDER BY
CREATE INDEX idx_order_customer ON orders (customer_id);

Key Differences: PostgreSQL vs Firebird

SERIAL

IDENTITY

ON CONFLICT

JSONB

tsvector

TEXT[]

INT[]

CREATE TYPE ... AS ENUM

CREATE TABLE IF NOT EXISTS

RDB$RELATIONS

CREATE PROCEDURE

CREATE PROCEDURE

SUSPEND

PG

FB

libpq.dll

fbclient.dll

PostgreSQL Checklist

• Driver

  PG

  configured?
• Connection string with

  Server

  ,

  Port

  ,

  Database

  ,

  UserName

  ,

  Password

  ?

• CharacterSet := 'UTF8'

  defined?
• Parameterized queries (without string concatenation)?

• RETURNING

  with

  Open

  (not

  ExecSQL

  )?

• IDENTITY

  instead of

  SERIAL

  for new projects?
• Explicit transactions for compound operations?
• Errors handled via

  EFDDBEngineException.Kind

  ?
• libpq.dll (32/64-bit) in PATH or configured in VendorLib?
• Indexes created for columns used in WHERE and JOIN?
• Foreign Keys with appropriate

  ON DELETE

  /

  ON UPDATE

  ?
• JSONB for semi-structured data (instead of TEXT)?

• information_schema

  to check metadata (not

  RDB$

  )?