PostgreSQL #

# Ubuntu/Debian
sudo apt install php8.3-pgsql

# Verify
php -m | grep pgsql
# pgsql
# pdo_pgsql

<?php
declare(strict_types=1);

// PostgreSQL DSN
$dsn = 'pgsql:host=localhost;port=5432;dbname=myapp;sslmode=require';

$pdo = new PDO($dsn, 'pguser', 'pgpassword', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false, // use native PgSQL prepared statements
]);

// sslmode: disable | allow | prefer | require | verify-ca | verify-full
// For production: require or verify-full

// Connecting to a cloud PostgreSQL (Supabase, Neon, Railway, etc.)
$dsnCloud = 'pgsql:host=db.myproject.supabase.co;port=5432;dbname=postgres;sslmode=require';
$pdoCloud = new PDO($dsnCloud, 'postgres', 'my-secret-password');

// Set the session timezone
$pdo->exec("SET timezone = 'Asia/Jakarta'");
$pdo->exec("SET search_path TO myschema, public"); // PostgreSQL schemas

<?php
// pg_connect — procedural connection
$connection = pg_connect(
    "host=localhost port=5432 dbname=myapp user=pguser password=pgpassword sslmode=require"
);

if ($connection === false) {
    throw new \RuntimeException("PostgreSQL connection failed: " . pg_last_error());
}

// Check the connection status
if (pg_connection_status($connection) !== PGSQL_CONNECTION_OK) {
    pg_connection_reset($connection); // try to reconnect
}

// Close the connection
pg_close($connection);

// pg_pconnect — persistent connection
$persistentConnection = pg_pconnect("host=localhost dbname=myapp user=pguser password=secret");

<?php
// PDO — named parameters
$stmt = $pdo->prepare("
    SELECT id, name, email, created_at
    FROM users
    WHERE role = :role AND active = :active
    ORDER BY name
    LIMIT :limit OFFSET :offset
");

$stmt->bindValue(':role',   'admin',  PDO::PARAM_STR);
$stmt->bindValue(':active', true,     PDO::PARAM_BOOL);
$stmt->bindValue(':limit',  20,       PDO::PARAM_INT);
$stmt->bindValue(':offset', 0,        PDO::PARAM_INT);
$stmt->execute();

$users = $stmt->fetchAll();

// INSERT and get the ID — PostgreSQL: RETURNING
$stmt = $pdo->prepare("
    INSERT INTO users (name, email, password_hash, role, created_at)
    VALUES (:name, :email, :hash, :role, NOW())
    RETURNING id, created_at
");

$stmt->execute([
    ':name'  => 'Budi Santoso',
    ':email' => '[email protected]',
    ':hash'  => password_hash('password123', PASSWORD_BCRYPT),
    ':role'  => 'user',
]);

$new = $stmt->fetch();
echo "ID: {$new['id']}, Created: {$new['created_at']}\n";

// pg_* — positional parameters ($1, $2, ...)
$result = pg_query_params($connection,
    "SELECT id, name FROM users WHERE email = $1 AND active = $2",
    ['[email protected]', true]
);

while ($row = pg_fetch_assoc($result)) {
    echo "{$row['id']}: {$row['name']}\n";
}
pg_free_result($result);

<?php
// Store JSON data
$stmt = $pdo->prepare("
    INSERT INTO products (name, metadata)
    VALUES (:name, :metadata::jsonb)
");

$metadata = [
    'colors'      => ['red', 'blue', 'green'],
    'dimensions'  => ['l' => 30, 'w' => 20, 'h' => 5],
    'certificates'=> ['SNI', 'ISO-9001'],
    'weight_grams'=> 500,
];

$stmt->execute([
    ':name'     => 'Premium Backpack',
    ':metadata' => json_encode($metadata, JSON_UNESCAPED_UNICODE),
]);

// Query JSONB — the -> operator (JSON type) and ->> (text type)
$stmt = $pdo->prepare("
    SELECT id, name,
           metadata->>'weight_grams'               AS weight,
           metadata->'dimensions'->>'l'            AS length,
           metadata->'colors'                      AS colors_json,
           jsonb_array_length(metadata->'colors')  AS color_count
    FROM products
    WHERE metadata->>'weight_grams' = :weight
    AND metadata->'colors' ? :color
");

$stmt->execute([':weight' => '500', ':color' => 'red']);
$products = $stmt->fetchAll();

// JSONB indexes for fast queries
// CREATE INDEX idx_products_metadata ON products USING gin(metadata);
// CREATE INDEX idx_products_weight ON products ((metadata->>'weight_grams'));

// JSONB contains @> — find documents containing a specific value
$stmt = $pdo->prepare("
    SELECT id, name FROM products
    WHERE metadata @> :filter::jsonb
");

$stmt->execute([':filter' => json_encode(['certificates' => ['SNI']])]);
// Returns products that have SNI in their certificates array

<?php
// PostgreSQL supports arrays as a column type
// CREATE TABLE articles (
//     id     SERIAL PRIMARY KEY,
//     title  TEXT,
//     tags   TEXT[],     -- array of text
//     score  INTEGER[]   -- array of integer
// );

// Insert an array — PHP arrays are encoded to PostgreSQL format
$tags  = ['php', 'postgresql', 'database'];
$pgArr = '{' . implode(',', array_map(fn($t) => '"' . addslashes($t) . '"', $tags)) . '}';

$stmt = $pdo->prepare("INSERT INTO articles (title, tags) VALUES (:title, :tags)");
$stmt->execute([':title' => 'Learning PostgreSQL', ':tags' => $pgArr]);

// Cleaner way: cast directly in the query
$stmt = $pdo->prepare("
    INSERT INTO articles (title, tags)
    VALUES (:title, string_to_array(:tags, ','))
");
$stmt->execute([':title' => 'Learning PostgreSQL', ':tags' => 'php,postgresql,database']);

// Query arrays — ANY, ALL, @>, &&
$stmt = $pdo->prepare("
    SELECT id, title, tags
    FROM articles
    WHERE :tag = ANY(tags)            -- articles that have a specific tag
    ORDER BY created_at DESC
");
$stmt->execute([':tag' => 'php']);

// Articles that have ALL the searched tags
$stmt = $pdo->prepare("
    SELECT id, title FROM articles
    WHERE tags @> ARRAY[:tag1, :tag2]::text[]
");
$stmt->execute([':tag1' => 'php', ':tag2' => 'database']);

// Parse an array from PostgreSQL to PHP
$stmt = $pdo->query("SELECT tags FROM articles WHERE id = 1");
$row  = $stmt->fetch();
// $row['tags'] = '{php,postgresql,database}' — needs parsing
$tags = str_getcsv(trim($row['tags'], '{}'));
// ['php', 'postgresql', 'database']

<?php
// PostgreSQL supports UUID as a native type
// CREATE TABLE users (
//     id    UUID DEFAULT gen_random_uuid() PRIMARY KEY,
//     name  TEXT
// );

$stmt = $pdo->prepare("
    INSERT INTO users (name, email)
    VALUES (:name, :email)
    RETURNING id
");
$stmt->execute([':name' => 'Budi', ':email' => '[email protected]']);
$userId = $stmt->fetchColumn(); // returns the UUID string: 'a1b2c3d4-...'

// Or generate from PHP and send to PostgreSQL
$uuid = sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
    mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff),
    mt_rand(0, 0x0fff) | 0x4000,
    mt_rand(0, 0x3fff) | 0x8000,
    mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
);

$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (:id::uuid, :name)");
$stmt->execute([':id' => $uuid, ':name' => 'Siti']);

<?php
// Create an ENUM type in PostgreSQL
// CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
// CREATE TABLE orders (status order_status DEFAULT 'pending');

// Insert an ENUM value
$stmt = $pdo->prepare("UPDATE orders SET status = :status WHERE id = :id");
$stmt->execute([':status' => 'processing', ':id' => 42]);

// Check the valid ENUM values
$stmt = $pdo->query("SELECT unnest(enum_range(NULL::order_status)) AS value");
$validValues = $stmt->fetchAll(PDO::FETCH_COLUMN);
// ['pending', 'processing', 'shipped', 'delivered', 'cancelled']

<?php
function processOrderWithSavepoints(PDO $pdo, array $data): array
{
    $pdo->beginTransaction();
    $result = ['order_id' => null, 'notification' => false, 'audit' => false];

    try {
        // Step 1: Insert the order (must succeed)
        $stmt = $pdo->prepare("
            INSERT INTO orders (user_id, total, status)
            VALUES (:user_id, :total, 'pending')
            RETURNING id
        ");
        $stmt->execute([':user_id' => $data['user_id'], ':total' => $data['total']]);
        $result['order_id'] = $stmt->fetchColumn();

        // Step 2: Send a notification (optional — failure doesn't cancel the order)
        $pdo->exec("SAVEPOINT sp_notification");
        try {
            $pdo->prepare("INSERT INTO notifications (user_id, message, order_id) VALUES (?, ?, ?)")
                ->execute([$data['user_id'], "Order #{$result['order_id']} created", $result['order_id']]);
            $result['notification'] = true;
        } catch (\Exception $e) {
            $pdo->exec("ROLLBACK TO SAVEPOINT sp_notification");
            error_log("Notification failed: " . $e->getMessage());
        }

        // Step 3: Audit log (optional — failure doesn't cancel the order)
        $pdo->exec("SAVEPOINT sp_audit");
        try {
            $pdo->prepare("INSERT INTO audit_log (action, data, created_at) VALUES ('order_created', ?, NOW())")
                ->execute([json_encode($data)]);
            $result['audit'] = true;
        } catch (\Exception $e) {
            $pdo->exec("ROLLBACK TO SAVEPOINT sp_audit");
            error_log("Audit log failed: " . $e->getMessage());
        }

        $pdo->commit();
        return $result;

    } catch (\Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

<?php
// Register a listener via pg_*
$connection = pg_connect("host=localhost dbname=myapp user=pguser password=secret");

// Start listening on channels
pg_query($connection, "LISTEN new_order");
pg_query($connection, "LISTEN stock_out");

echo "Waiting for notifications...\n";

// Notification polling loop
while (true) {
    $notif = pg_get_notify($connection, PGSQL_ASSOC);

    if ($notif !== false) {
        echo "Channel: {$notif['message']}\n";
        echo "Sender PID: {$notif['pid']}\n";
        echo "Payload: {$notif['payload']}\n";

        // Process based on the channel
        match($notif['message']) {
            'new_order' => processNewOrder($notif['payload']),
            'stock_out' => sendStockAlert($notif['payload']),
            default     => null,
        };
    }

    usleep(100_000); // wait 100ms before polling again
}

// Notification sender (from another connection or a database trigger)
pg_query($connection, "NOTIFY new_order, '{\"order_id\": 42, \"total\": 150000}'");

// Or from a PostgreSQL trigger:
/*
CREATE OR REPLACE FUNCTION notify_new_order()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('new_order', row_to_json(NEW)::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_new_order
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_new_order();
*/

<?php
// COPY from PHP to PostgreSQL using pg_copy_from()
$connection = pg_connect("host=localhost dbname=myapp user=pguser password=secret");

// Prepare the data in CSV format (tab-separated by default)
$data = [];
for ($i = 1; $i <= 100000; $i++) {
    $data[] = "$i\tProduct $i\t" . rand(10000, 1000000) . "\t1\n";
}

// COPY directly from a PHP array — very fast
$success = pg_copy_from($connection, 'products', $data, "\t");
if (!$success) {
    throw new \RuntimeException("COPY failed: " . pg_last_error($connection));
}
echo "100,000 rows successfully inserted via COPY\n";

// COPY from a CSV file to a table
$sql = "COPY products (id, name, price, active) FROM STDIN WITH (FORMAT csv, HEADER true)";
pg_query($connection, $sql);

$file = fopen('large_products.csv', 'r');
while (!feof($file)) {
    $line = fgets($file, 65536);
    pg_put_line($connection, $line);
}
fclose($file);
pg_end_copy($connection);

// Export to CSV via COPY TO
$result = pg_query($connection, "COPY (SELECT id, name, price FROM products WHERE active) TO STDOUT WITH CSV HEADER");
$file   = fopen('export.csv', 'w');
while ($line = pg_fetch_result($result, 0, 0)) {
    fwrite($file, $line);
}
fclose($file);

-- 1. Serial (auto-increment) and Sequences
-- MySQL:
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY);
-- PostgreSQL: SERIAL (shorthand) or GENERATED ALWAYS AS IDENTITY
CREATE TABLE t (id SERIAL PRIMARY KEY);
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

-- 2. ILIKE — case-insensitive LIKE (doesn't exist in MySQL)
SELECT * FROM users WHERE name ILIKE '%budi%'; -- case-insensitive

-- 3. Strings: single quotes only (double quotes are for identifiers)
SELECT 'text'; -- OK
SELECT "column" FROM table; -- identifier, not a string

-- 4. Boolean literals
SELECT TRUE, FALSE, NULL; -- not 1/0 like MySQL

-- 5. RETURNING — get newly inserted/updated/deleted values
INSERT INTO t (name) VALUES ('Budi') RETURNING id, created_at;
UPDATE t SET name = 'Siti' WHERE id = 1 RETURNING *;
DELETE FROM t WHERE id = 1 RETURNING id; -- confirm the deleted row

-- 6. CTEs (WITH) — more powerful in PostgreSQL
WITH ranked AS (
    SELECT id, name, salary,
           RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT * FROM ranked WHERE rank = 1;

-- 7. Built-in full-text search
SELECT * FROM articles
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', 'learning postgresql');