PostgreSQL #

PostgreSQL is the most advanced open-source relational database available today. Behind its free license, PostgreSQL offers features that even beat many commercial databases: very rich data types (UUID, JSONB, Arrays, geometric types), powerful window functions, built-in full-text search, LISTEN/NOTIFY for real-time notifications, and high SQL standard compliance. PostgreSQL is often called “the most correct database” — it’s very strict about data types and transactions. This article covers JDBC connections, leveraging PostgreSQL’s unique data types from Java, UPSERT with ON CONFLICT, LISTEN/NOTIFY for event-driven architectures, CopyManager for bulk data imports, and Spring Boot JPA with PostgreSQL’s signature features.

PostgreSQL’s Strengths and Unique Characteristics #

PostgreSQL isn’t just “a better MySQL” — it has a fundamentally different philosophy and feature set.

Aspect	MySQL	SQL Server	Oracle	PostgreSQL
Auto-increment	`AUTO_INCREMENT`	`IDENTITY`	`SEQUENCE`	`SERIAL` / `BIGSERIAL` / `GENERATED AS IDENTITY`
Native UUID	✗ Not available	✗ `UNIQUEIDENTIFIER`	✗ Not available	✓ Built-in `UUID` type
JSON/JSONB	`JSON` (plain text)	`JSON` (plain text)	`JSON` (plain text)	✓ `JSONB` (binary, indexable!)
Arrays	✗ Not available	✗ Not available	✗ Not available	✓ `INTEGER[]`, `TEXT[]`, etc.
Enum types	`ENUM`	✗ Not available	✗ Not available	✓ `CREATE TYPE ... AS ENUM`
Full-text search	Limited	Available	Available	✓ Very powerful, built-in
UPSERT	`INSERT ... ON DUPLICATE KEY`	`MERGE`	`MERGE`	✓ `INSERT ... ON CONFLICT`
Async notifications	✗ Not available	`Service Broker`	`Advanced Queuing`	✓ `LISTEN/NOTIFY`
Empty string	`''` ≠ `NULL`	`''` ≠ `NULL`	`''` = `NULL`	`''` ≠ `NULL`
Case sensitivity	✗ Not by default	✗ Not by default	✗ Not by default	✓ Yes by default
License	GPL	Commercial	Commercial	PostgreSQL License (free)

flowchart TB
    A["Java Application"] --> B["Spring Data JPA\n(@Entity, Repository)"]
    A --> C["JDBC / JdbcTemplate"]
    B --> D["Hibernate\n(PostgreSQLDialect)"]
    C --> E["HikariCP\n(Connection Pool)"]
    D --> E
    E --> F["PostgreSQL JDBC Driver\n(pgjdbc)"]
    F --> G[("PostgreSQL Server\n(Local / Cloud / RDS / Supabase)")]

Setup — Driver and Database #

Dependencies #

<!-- Maven — the official PostgreSQL driver (pgjdbc) -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

<!-- HikariCP -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

// Gradle
implementation 'org.postgresql:postgresql:42.7.3'
implementation 'com.zaxxer:HikariCP:5.1.0'

Setting Up the Database and Tables #

-- Create the database
CREATE DATABASE store_db
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

\c store_db

-- Create custom ENUM types
CREATE TYPE product_status AS ENUM ('active', 'inactive', 'out_of_stock');
CREATE TYPE product_category AS ENUM ('electronics', 'accessories', 'storage', 'components', 'general');

-- Enable the extensions for UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- alternative for UUID generation

-- A table with various PostgreSQL data types
CREATE TABLE products (
    id          BIGSERIAL       PRIMARY KEY,              -- auto-increment
    code        UUID            DEFAULT gen_random_uuid() UNIQUE, -- automatic UUID
    name        VARCHAR(255)    NOT NULL,
    price       NUMERIC(15,2)   NOT NULL,
    stock       INTEGER         NOT NULL DEFAULT 0,
    category    product_category,                          -- custom ENUM
    status      product_status  NOT NULL DEFAULT 'active', -- custom ENUM
    tags        TEXT[],                                   -- text array
    specs       JSONB,                                    -- binary JSON (indexable)
    active      BOOLEAN         NOT NULL DEFAULT TRUE,    -- native BOOLEAN!
    created_at  TIMESTAMPTZ     DEFAULT NOW(),            -- timestamp with timezone
    updated_at  TIMESTAMPTZ     DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_status   ON products(status);
CREATE INDEX idx_products_tags     ON products USING GIN(tags);        -- index for arrays
CREATE INDEX idx_products_specs    ON products USING GIN(specs);  -- index for JSONB

-- Trigger for automatic updated_at
CREATE OR REPLACE FUNCTION fn_update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_updated
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION fn_update_updated_at();

-- Initial data
INSERT INTO products (name, price, stock, category, tags, specs) VALUES
    ('ProBook Laptop', 12000000, 5, 'electronics',
     ARRAY['laptop', 'work', 'premium'],
     '{"ram": "16GB", "storage": "512GB SSD", "screen": "14 inch"}'),
    ('Wireless Mouse', 150000, 20, 'accessories',
     ARRAY['mouse', 'wireless'],
     '{"dpi": 1600, "buttons": 6, "connection": "USB-A"}'),
    ('1TB SSD', 750000, 30, 'storage',
     ARRAY['ssd', 'nvme'],
     '{"capacity": "1TB", "interface": "NVMe", "read_speed": "3500MB/s"}');
COMMIT;

JDBC Connections #

Basic Connection #

import java.sql.*;

// URL: jdbc:postgresql://host:port/database
String url      = "jdbc:postgresql://localhost:5432/store_db";
String username = "postgres";
String password = "mysecret";

try (Connection conn = DriverManager.getConnection(url, username, password)) {
    System.out.println("PostgreSQL: " + conn.getMetaData().getDatabaseProductVersion());
} catch (SQLException e) {
    System.err.println("Connection failed: " + e.getMessage());
}

// URL with additional parameters
String urlParams = "jdbc:postgresql://localhost:5432/store_db"
    + "?currentSchema=public"      // default schema
    + "&ssl=false"                  // disable SSL (development)
    + "&connectTimeout=10"          // connection timeout (seconds)
    + "&socketTimeout=30"           // socket timeout (seconds)
    + "&ApplicationName=StoreDB-App" // application name (shows in pg_stat_activity)
    + "&stringtype=unspecified";    // send Strings without a type — more flexible

SELECT and PostgreSQL Data Types #

try (Connection conn = DriverManager.getConnection(url, username, password)) {

    String sql = """
        SELECT id, code, name, price, stock, category, status,
               tags, specs, active, created_at
        FROM products
        WHERE active = true AND category = ?::product_category
        ORDER BY price DESC
        LIMIT ?
        """;

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, "electronics");  // Cast to ENUM with ::
        ps.setInt(2, 10);

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                long id     = rs.getLong("id");
                String code = rs.getString("code");  // UUID as a String
                String name = rs.getString("name");
                java.math.BigDecimal price = rs.getBigDecimal("price");
                int stock    = rs.getInt("stock");
                String cat  = rs.getString("category"); // ENUM as a String
                boolean active = rs.getBoolean("active"); // native BOOLEAN!

                // PostgreSQL arrays
                java.sql.Array tagArray = rs.getArray("tags");
                String[] tags = (String[]) tagArray.getArray(); // cast to a Java array

                // JSONB as a String — parse with Jackson/Gson
                String specsJson = rs.getString("specs");

                // TIMESTAMPTZ as OffsetDateTime
                java.time.OffsetDateTime created =
                    rs.getObject("created_at", java.time.OffsetDateTime.class);

                System.out.printf("[%d] %s - Rp%,.2f (%s) tags: %s%n",
                    id, name, price, cat, java.util.Arrays.toString(tags));
            }
        }
    }
}

INSERT with Rich Data Types #

import org.postgresql.util.PGobject;

try (Connection conn = DriverManager.getConnection(url, username, password)) {

    String sql = """
        INSERT INTO products (name, price, stock, category, tags, specs)
        VALUES (?, ?, ?, ?::product_category, ?, ?::jsonb)
        RETURNING id, code
        """;

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, "27\" 4K Monitor");
        ps.setBigDecimal(2, new java.math.BigDecimal("4500000"));
        ps.setInt(3, 3);
        ps.setString(4, "electronics");  // ENUM: cast with ::

        // PostgreSQL arrays — use conn.createArrayOf()
        java.sql.Array tagArray = conn.createArrayOf("text",
            new String[]{"monitor", "4k", "professional"});
        ps.setArray(5, tagArray);

        // JSONB — use a PGobject or cast a string with ::jsonb
        String specsJson = """
            {"resolution": "3840x2160", "refresh": "144Hz", "panel": "IPS"}
            """;
        ps.setString(6, specsJson.trim()); // ::jsonb cast in SQL

        // RETURNING id, code — PostgreSQL-specific, fetch both at once
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                System.out.println("New ID:  " + rs.getLong("id"));
                System.out.println("UUID code: " + rs.getString("code"));
            }
        }

        // Clean up the array object
        tagArray.free();
    }
}

PostgreSQL’s Unique Data Types from Java #

UUID #

import java.util.UUID;

// UUIDs in PostgreSQL can be set as java.util.UUID or String
String insertSql = "INSERT INTO products (code, name, price, stock) VALUES (?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(insertSql)) {

    UUID newCode = UUID.randomUUID();
    ps.setObject(1, newCode);  // setObject() for UUID
    ps.setString(2, "Gaming Headset");
    ps.setBigDecimal(3, new java.math.BigDecimal("650000"));
    ps.setInt(4, 8);
    ps.executeUpdate();
}

// Read a UUID
String selectSql = "SELECT code FROM products WHERE id = ?";
try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(selectSql)) {

    ps.setLong(1, 1L);
    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            UUID code = rs.getObject("code", UUID.class); // Java 8+ clean way
            // or: UUID.fromString(rs.getString("code"))
            System.out.println("UUID: " + code);
        }
    }
}

JSONB — Storing and Searching JSON #

import com.fasterxml.jackson.databind.ObjectMapper;
import org.postgresql.util.PGobject;

ObjectMapper objectMapper = new ObjectMapper();

// Storing JSONB — two ways
// Way 1: cast in SQL with ::jsonb (simpler)
String sql1 = "UPDATE products SET specs = ?::jsonb WHERE id = ?";

// Way 2: PGobject (more explicit)
String sql2 = "UPDATE products SET specs = ? WHERE id = ?";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(sql2)) {

    // Build a Java object, convert to a JSON string
    var specs = java.util.Map.of(
        "ram", "32GB",
        "storage", "1TB NVMe",
        "screen", "15.6 inch",
        "gpu", "RTX 4060"
    );
    String jsonStr = objectMapper.writeValueAsString(specs);

    // Wrap it in a PGobject of type jsonb
    PGobject jsonb = new PGobject();
    jsonb.setType("jsonb");
    jsonb.setValue(jsonStr);

    ps.setObject(1, jsonb);
    ps.setLong(2, 1L);
    ps.executeUpdate();
}

// Searching by JSONB — PostgreSQL operators
try (Connection conn = DriverManager.getConnection(url, username, password)) {

    // @> : JSONB containment — find products whose specs contain a certain key
    String containSql = """
        SELECT id, name, specs
        FROM products
        WHERE specs @> ?::jsonb
        """;

    try (PreparedStatement ps = conn.prepareStatement(containSql)) {
        // Find products with 16GB of RAM
        ps.setString(1, "{\"ram\": \"16GB\"}");

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getString("name") + ": "
                    + rs.getString("specs"));
            }
        }
    }

    // ->> : extract a value from JSONB as text
    String extractSql = """
        SELECT name, specs->>'ram' AS ram, specs->>'storage' AS storage
        FROM products
        WHERE specs IS NOT NULL
          AND (specs->>'ram') IS NOT NULL
        """;

    try (PreparedStatement ps = conn.prepareStatement(extractSql);
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%-20s RAM: %-6s Storage: %s%n",
                rs.getString("name"),
                rs.getString("ram"),
                rs.getString("storage"));
        }
    }
}

PostgreSQL Arrays #

try (Connection conn = DriverManager.getConnection(url, username, password)) {

    // Find products with a certain tag — the @> operator (array containment)
    String tagSql = """
        SELECT id, name, tags
        FROM products
        WHERE tags @> ?::text[]    -- products whose tags contain all these tags
          AND active = true
        """;

    try (PreparedStatement ps = conn.prepareStatement(tagSql)) {
        java.sql.Array searchTags = conn.createArrayOf("text", new String[]{"laptop"});
        ps.setArray(1, searchTags);

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                java.sql.Array tags = rs.getArray("tags");
                String[] tagArr = (String[]) tags.getArray();
                System.out.println(rs.getString("name") + ": "
                    + java.util.Arrays.toString(tagArr));
                tags.free();
            }
        }
        searchTags.free();
    }

    // Find products that have ANY of these tags — the && operator
    String anyTagSql = """
        SELECT id, name FROM products
        WHERE tags && ?::text[]    -- overlap between the product tags and the search tags
        """;

    try (PreparedStatement ps = conn.prepareStatement(anyTagSql)) {
        java.sql.Array searchTags = conn.createArrayOf("text",
            new String[]{"laptop", "monitor", "gaming"});
        ps.setArray(1, searchTags);

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) System.out.println(rs.getString("name"));
        }
        searchTags.free();
    }

    // Add an element to an array — the array_append function
    String addTagSql = """
        UPDATE products
        SET tags = array_append(tags, ?)
        WHERE id = ? AND NOT (tags @> ARRAY[?]::text[])
        """;

    try (PreparedStatement ps = conn.prepareStatement(addTagSql)) {
        ps.setString(1, "discount");
        ps.setLong(2, 1L);
        ps.setString(3, "discount"); // don't add if it already exists
        ps.executeUpdate();
    }
}

UPSERT — INSERT or UPDATE #

PostgreSQL supports INSERT ... ON CONFLICT (UPSERT), which is far more elegant than manual checks or convoluted subqueries.

ON CONFLICT DO UPDATE #

// Scenario: importing product data — update if the code exists, insert if not
String upsertSql = """
    INSERT INTO products (code, name, price, stock, category)
    VALUES (?::uuid, ?, ?, ?, ?::product_category)
    ON CONFLICT (code) DO UPDATE SET
        name      = EXCLUDED.name,
        price     = EXCLUDED.price,
        stock     = EXCLUDED.stock,
        category  = EXCLUDED.category,
        updated_at = NOW()
    RETURNING id, (xmax = 0) AS is_insert
    """;
// EXCLUDED refers to the values that ARE about to be inserted
// xmax = 0 means a new row (insert), xmax != 0 means an old row (update)

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(upsertSql)) {

    ps.setString(1, "123e4567-e89b-12d3-a456-426614174000");
    ps.setString(2, "Gaming Laptop");
    ps.setBigDecimal(3, new java.math.BigDecimal("15000000"));
    ps.setInt(4, 3);
    ps.setString(5, "electronics");

    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            boolean isInsert = rs.getBoolean("is_insert");
            System.out.println("Operation: " + (isInsert ? "New INSERT" : "UPDATE existing"));
            System.out.println("ID: " + rs.getLong("id"));
        }
    }
}

// ON CONFLICT DO NOTHING — ignore duplicates (no update, no error)
String ignoreSql = """
    INSERT INTO products (code, name, price, stock)
    VALUES (?::uuid, ?, ?, ?)
    ON CONFLICT (code) DO NOTHING
    """;

UPSERT for Stock Increments #

// Scenario: adding stock — create a new row if the product doesn't exist,
// or add to the stock if it already exists
String addStockSql = """
    INSERT INTO products (code, name, price, stock, category)
    VALUES (?::uuid, ?, ?, ?, ?::product_category)
    ON CONFLICT (code) DO UPDATE SET
        stock = products.stock + EXCLUDED.stock  -- add, not replace
    RETURNING id, stock AS latest_stock
    """;

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(addStockSql)) {

    ps.setString(1, "123e4567-e89b-12d3-a456-426614174000");
    ps.setString(2, "Gaming Laptop");
    ps.setBigDecimal(3, new java.math.BigDecimal("15000000"));
    ps.setInt(4, 5); // add 5 units
    ps.setString(5, "electronics");

    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            System.out.println("Latest stock: " + rs.getInt("latest_stock"));
        }
    }
}

LISTEN/NOTIFY — Real-Time Notifications #

PostgreSQL has a unique feature: LISTEN/NOTIFY, which lets one connection send notifications to other connections listening on a particular channel. This can be used for event-driven architectures without an external message broker.

Server (LISTEN Receiver) #

import org.postgresql.PGConnection;
import org.postgresql.PGNotification;

// A dedicated connection for LISTEN — don't take it from a connection pool
String url = "jdbc:postgresql://localhost:5432/store_db";
Connection listenConn = DriverManager.getConnection(url, "postgres", "mysecret");

try {
    // Start listening on the "product_changed" channel
    try (Statement stmt = listenConn.createStatement()) {
        stmt.execute("LISTEN product_changed");
        stmt.execute("LISTEN out_of_stock");
    }
    System.out.println("Listening for notifications...");

    // Notification polling loop (non-blocking)
    while (!Thread.currentThread().isInterrupted()) {
        // Send an empty query to flush notifications from the server
        try (Statement stmt = listenConn.createStatement()) {
            stmt.execute("SELECT 1");
        }

        // Fetch all incoming notifications
        PGConnection pgConn = listenConn.unwrap(PGConnection.class);
        PGNotification[] notifications = pgConn.getNotifications(1000); // 1-second timeout

        if (notifications != null) {
            for (PGNotification notification : notifications) {
                System.out.printf("[Notification] Channel: %s | PID: %d | Payload: %s%n",
                    notification.getName(),
                    notification.getPID(),
                    notification.getParameter());

                // Process notifications by channel
                switch (notification.getName()) {
                    case "product_changed" -> processProductChange(notification.getParameter());
                    case "out_of_stock"    -> processOutOfStock(notification.getParameter());
                }
            }
        }
    }
} finally {
    listenConn.close();
}

Client (NOTIFY Sender) #

// Send a notification from Java
try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement("SELECT pg_notify(?, ?)")) {

    // Format the payload as JSON
    String payload = "{\"id\": 1, \"action\": \"update\", \"field\": \"price\"}";
    ps.setString(1, "product_changed");
    ps.setString(2, payload);
    ps.execute();
    System.out.println("Notification sent");
}

// Or directly from SQL (for example inside a trigger)
// In SQL: PERFORM pg_notify('out_of_stock', row_to_json(NEW)::text);

Automatic NOTIFY Triggers #

-- Trigger: send automatic notifications when stock falls below a threshold
CREATE OR REPLACE FUNCTION fn_check_stock()
RETURNS TRIGGER AS $$
BEGIN
    -- Notify on stock updates
    PERFORM pg_notify('product_changed',
        json_build_object(
            'id', NEW.id,
            'name', NEW.name,
            'old_stock', OLD.stock,
            'new_stock', NEW.stock
        )::text
    );

    -- Special notification if the stock runs out
    IF NEW.stock = 0 AND OLD.stock > 0 THEN
        PERFORM pg_notify('out_of_stock',
            json_build_object('id', NEW.id, 'name', NEW.name)::text
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_stock
    AFTER UPDATE OF stock ON products
    FOR EACH ROW
    EXECUTE FUNCTION fn_check_stock();

CopyManager — Very Fast Bulk Imports #

PostgreSQL’s CopyManager allows mass data imports from CSV or streams — far faster than regular batch INSERTs.

Importing from CSV #

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

// CSV data (can come from a file, string, or stream)
String csvData = """
    Laptop A,8000000,5,electronics
    Laptop B,9000000,3,electronics
    Ergonomic Mouse,250000,15,accessories
    512GB SSD,500000,20,storage
    """;

try (Connection conn = DriverManager.getConnection(url, username, password)) {
    CopyManager copyManager = new CopyManager((BaseConnection) conn);

    // COPY FROM STDIN — import from Java into PostgreSQL
    String copySql = """
        COPY products (name, price, stock, category)
        FROM STDIN
        WITH (FORMAT csv, DELIMITER ',', NULL '\\N')
        """;

    long rowCount = copyManager.copyIn(
        copySql,
        new java.io.StringReader(csvData)
    );
    System.out.println("Imported successfully: " + rowCount + " rows");
}

// Import from a large CSV file (stream-based, memory efficient)
try (Connection conn = DriverManager.getConnection(url, username, password);
     java.io.InputStream is = new java.io.FileInputStream("data/products.csv")) {

    CopyManager copyManager = new CopyManager((BaseConnection) conn);
    String copySql = "COPY products (name, price, stock, category) FROM STDIN WITH (FORMAT csv, HEADER true)";
    long rows = copyManager.copyIn(copySql, is);
    System.out.println("Imported: " + rows + " rows");
}

Exporting to CSV #

// COPY TO STDOUT — export from PostgreSQL to Java
try (Connection conn = DriverManager.getConnection(url, username, password)) {
    CopyManager copyManager = new CopyManager((BaseConnection) conn);

    String copySql = """
        COPY (
            SELECT id, name, price, stock, category
            FROM products
            WHERE active = true
            ORDER BY id
        ) TO STDOUT WITH (FORMAT csv, HEADER true, DELIMITER ',')
        """;

    java.io.StringWriter writer = new java.io.StringWriter();
    long rows = copyManager.copyOut(copySql, writer);
    System.out.println("Exported: " + rows + " rows");
    System.out.println(writer.toString());

    // Export directly to a file
    try (java.io.OutputStream os = new java.io.FileOutputStream("export/products.csv")) {
        copyManager.copyOut(copySql, os);
    }
}

HikariCP for PostgreSQL #

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class PostgresPool {

    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();

        config.setJdbcUrl("jdbc:postgresql://localhost:5432/store_db");
        config.setUsername("postgres");
        config.setPassword("mysecret");
        config.setDriverClassName("org.postgresql.Driver");

        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30_000);
        config.setIdleTimeout(600_000);
        config.setMaxLifetime(1_800_000);

        // PostgreSQL: no connectionTestQuery needed
        // HikariCP uses isValid(), which is more efficient
        // config.setConnectionTestQuery("SELECT 1"); // optional

        // PostgreSQL-specific properties
        config.addDataSourceProperty("ApplicationName", "StoreDB-App");
        config.addDataSourceProperty("stringtype", "unspecified"); // send Strings without a type
        config.addDataSourceProperty("reWriteBatchedInserts", "true"); // batch INSERT optimization
        config.addDataSourceProperty("prepareThreshold", "5"); // server-side prepared statements

        config.setPoolName("Postgres-Pool");

        dataSource = new HikariDataSource(config);
    }

    public static java.sql.Connection getConnection() throws java.sql.SQLException {
        return dataSource.getConnection();
    }
}

Spring Boot + Spring Data JPA #

Dependencies #

<!-- Maven -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

application.yml Configuration #

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/store_db
    username: postgres
    password: mysecret
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
      pool-name: Postgres-Pool
      data-source-properties:
        ApplicationName: StoreDB-Spring
        stringtype: unspecified
        reWriteBatchedInserts: true

  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false
    open-in-view: false
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true
        jdbc:
          batch_size: 50
          fetch_size: 100
        default_schema: public

Entity with PostgreSQL Data Types #

import jakarta.persistence.*;
import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.type.SqlTypes;
import java.math.BigDecimal;
import java.time.OffsetDateTime;
import java.util.Map;
import java.util.UUID;

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // UUID — a separate column, not the PK
    @Column(unique = true, updatable = false,
            columnDefinition = "UUID DEFAULT gen_random_uuid()")
    private UUID code;

    @Column(nullable = false, length = 255)
    private String name;

    @Column(nullable = false, precision = 15, scale = 2)
    private BigDecimal price;

    @Column(nullable = false)
    private Integer stock = 0;

    // PostgreSQL ENUM — needs @Enumerated and columnDefinition
    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "product_category")
    private ProductCategory category;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false, columnDefinition = "product_status DEFAULT 'active'")
    private ProductStatus status = ProductStatus.ACTIVE;

    // PostgreSQL arrays
    @Column(columnDefinition = "TEXT[]")
    private String[] tags;

    // PostgreSQL JSONB — Hibernate 6+ supports Map to JSONB
    @JdbcTypeCode(SqlTypes.JSON)
    @Column(columnDefinition = "JSONB")
    private Map<String, Object> specs;

    // Native PostgreSQL BOOLEAN
    @Column(nullable = false)
    private Boolean active = true;

    @Column(name = "created_at", updatable = false,
            columnDefinition = "TIMESTAMPTZ DEFAULT NOW()")
    private OffsetDateTime createdAt;

    @Column(name = "updated_at",
            columnDefinition = "TIMESTAMPTZ DEFAULT NOW()")
    private OffsetDateTime updatedAt;

    @PrePersist
    protected void onCreate() {
        createdAt = OffsetDateTime.now();
        updatedAt = OffsetDateTime.now();
        if (code == null) code = UUID.randomUUID();
    }

    @PreUpdate
    protected void onUpdate() {
        updatedAt = OffsetDateTime.now();
    }

    // Enum types
    public enum ProductCategory { electronics, accessories, storage, components, general }
    public enum ProductStatus   { active, inactive, out_of_stock }

    // Constructors, getters, setters
    public Product() {}
    public Product(String name, BigDecimal price, int stock, ProductCategory category) {
        this.name = name; this.price = price; this.stock = stock; this.category = category;
    }
    // ... getters and setters
}

Repository with PostgreSQL Features #

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Method names — same as other databases
    List<Product> findByActiveTrue();
    List<Product> findByCategoryAndActiveTrue(Product.ProductCategory category);
    Optional<Product> findByCode(UUID code);

    // Native PostgreSQL queries — JSONB, arrays, ILIKE
    @Query(value = """
        SELECT id, name, price
        FROM products
        WHERE active = true
          AND name ILIKE CONCAT('%', :keyword, '%')  -- ILIKE: case-insensitive LIKE
        ORDER BY price DESC
        LIMIT :limit
        """, nativeQuery = true)
    List<Object[]> searchCaseInsensitive(@Param("keyword") String keyword, @Param("limit") int limit);

    // Search by JSONB
    @Query(value = """
        SELECT * FROM products
        WHERE specs @> CAST(:json AS jsonb)
          AND active = true
        """, nativeQuery = true)
    List<Product> findBySpecs(@Param("json") String jsonFragment);

    // Search by tags (array containment)
    @Query(value = """
        SELECT * FROM products
        WHERE tags @> CAST(:tags AS text[])
          AND active = true
        """, nativeQuery = true)
    List<Product> findByTags(@Param("tags") String tagsArray); // "{laptop,gaming}"

    // UPSERT via a native query
    @Modifying
    @Query(value = """
        INSERT INTO products (code, name, price, stock, category)
        VALUES (CAST(:code AS uuid), :name, :price, :stock, CAST(:category AS product_category))
        ON CONFLICT (code) DO UPDATE SET
            name = EXCLUDED.name, price = EXCLUDED.price,
            stock = EXCLUDED.stock, updated_at = NOW()
        """, nativeQuery = true)
    void upsert(@Param("code") String code, @Param("name") String name,
                @Param("price") BigDecimal price, @Param("stock") int stock,
                @Param("category") String category);

    // PostgreSQL full-text search
    @Query(value = """
        SELECT * FROM products
        WHERE to_tsvector('simple', name || ' ' || COALESCE(category::text, ''))
              @@ plainto_tsquery('simple', :query)
          AND active = true
        """, nativeQuery = true)
    List<Product> fullTextSearch(@Param("query") String query);

    // Window functions — ranking per category
    @Query(value = """
        SELECT id, name, price, category,
               RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank
        FROM products
        WHERE active = true
        """, nativeQuery = true)
    List<Object[]> rankingPerCategory();
}

Tips and Advanced Features #

Transactions with Advisory Locks #

PostgreSQL has advisory locks — application-level locks not tied to a specific table or row, useful for preventing duplicate processes in distributed systems.

try (Connection conn = DriverManager.getConnection(url, username, password)) {
    conn.setAutoCommit(false);

    // Request an advisory lock with a numeric key
    long lockKey = 12345L;
    try (PreparedStatement ps = conn.prepareStatement("SELECT pg_advisory_xact_lock(?)")) {
        ps.setLong(1, lockKey);
        ps.execute();
        // The lock is automatically released when the transaction ends (commit/rollback)
    }

    // A process that must run exclusively
    System.out.println("Processing inside the advisory lock...");
    // ...

    conn.commit();
}

EXPLAIN ANALYZE from Java #

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(
         "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM products WHERE active = true")) {

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString(1)); // JSON execution plan
        }
    }
}

When to Use PostgreSQL #

Use POSTGRESQL when:
  ✓ You need an open-source database with no license costs but with enterprise features
  ✓ Complex data models: JSONB for semi-structured data, Arrays for lists
  ✓ You need clean, expressive UPSERTs (ON CONFLICT)
  ✓ You need real-time notifications without an external broker (LISTEN/NOTIFY)
  ✓ Built-in full-text search without Elasticsearch for simple needs
  ✓ A team that values SQL standard compliance and strict ACID
  ✓ Cloud deployment: AWS RDS, Google Cloud SQL, Azure, Supabase, Neon

Things to keep in mind:
  ✗ Case-sensitive by default — 'Laptop' ≠ 'laptop' in exact queries
  ✗ PostgreSQL ENUMs are hard to change (ALTER TYPE needs workarounds) — consider plain text
  ✗ Automatic VACUUM for dead tuples — watch the autovacuum settings on large tables
  ✗ No "LIMIT" on UPDATE/DELETE without a subquery
  ✗ Advisory lock keys must be managed yourself so features don't collide

Summary #

The org.postgresql:postgresql driver is the only official driver. Unlike Oracle’s many JAR versions, one pgjdbc version works with all PostgreSQL versions.
Native BOOLEAN, native UUID, indexable JSONB — three PostgreSQL data type advantages available without workarounds. Use rs.getBoolean(), rs.getObject("col", UUID.class), and PGobject for JSONB.
conn.createArrayOf("text", array) to create PostgreSQL arrays from Java. Read them back with rs.getArray() then cast to String[].
RETURNING after INSERT/UPDATE/DELETE — PostgreSQL can return the affected rows directly without a separate query. Use it with ps.executeQuery() (not executeUpdate()).
ON CONFLICT DO UPDATE is the clean UPSERT way — EXCLUDED.column refers to the values about to be inserted, so it can be used for updates or increments.
LISTEN/NOTIFY for real-time notifications between database connections — a lightweight alternative to a message broker within one PostgreSQL infrastructure.
CopyManager for bulk imports — far faster than regular batch INSERTs. Import millions of rows from CSV in seconds.
reWriteBatchedInserts=true in HikariCP — a PostgreSQL property that rewrites INSERT ... VALUES (?) (?) (?) into a single multi-row statement, significantly improving batch performance.
ILIKE for case-insensitive search (not LIKE). PostgreSQL distinguishes the two because it’s case-sensitive by default.

← Previous: Oracle Next: MongoDB →