PostgreSQL #

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

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

<?php
declare(strict_types=1);

// DSN PostgreSQL
$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, // gunakan prepared statement native PgSQL
]);

// sslmode: disable | allow | prefer | require | verify-ca | verify-full
// Untuk production: require atau verify-full

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

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

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

if ($koneksi === false) {
    throw new \RuntimeException("Koneksi PostgreSQL gagal: " . pg_last_error());
}

// Cek status koneksi
if (pg_connection_status($koneksi) !== PGSQL_CONNECTION_OK) {
    pg_connection_reset($koneksi); // coba reconnect
}

// Tutup koneksi
pg_close($koneksi);

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

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

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

$users = $stmt->fetchAll();

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

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

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

// pg_* — positional parameter ($1, $2, ...)
$result = pg_query_params($koneksi,
    "SELECT id, nama FROM users WHERE email = $1 AND aktif = $2",
    ['[email protected]', true]
);

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

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

$metadata = [
    'warna'     => ['merah', 'biru', 'hijau'],
    'dimensi'   => ['p' => 30, 'l' => 20, 't' => 5],
    'sertifikat'=> ['SNI', 'ISO-9001'],
    'berat_gram'=> 500,
];

$stmt->execute([
    ':nama'     => 'Tas Ransel Premium',
    ':metadata' => json_encode($metadata, JSON_UNESCAPED_UNICODE),
]);

// Query JSONB — operator -> (tipe JSON) dan ->> (tipe text)
$stmt = $pdo->prepare("
    SELECT id, nama,
           metadata->>'berat_gram'                  AS berat,
           metadata->'dimensi'->>'p'                AS panjang,
           metadata->'warna'                        AS warna_json,
           jsonb_array_length(metadata->'warna')    AS jumlah_warna
    FROM produk
    WHERE metadata->>'berat_gram' = :berat
    AND metadata->'warna' ? :warna
");

$stmt->execute([':berat' => '500', ':warna' => 'merah']);
$produk = $stmt->fetchAll();

// Index JSONB untuk query cepat
// CREATE INDEX idx_produk_metadata ON produk USING gin(metadata);
// CREATE INDEX idx_produk_berat ON produk ((metadata->>'berat_gram'));

// JSONB contains @> — cari dokumen yang mengandung nilai tertentu
$stmt = $pdo->prepare("
    SELECT id, nama FROM produk
    WHERE metadata @> :filter::jsonb
");

$stmt->execute([':filter' => json_encode(['sertifikat' => ['SNI']])]);
// Mengembalikan produk yang punya SNI dalam array sertifikat

<?php
// PostgreSQL mendukung array sebagai tipe kolom
// CREATE TABLE artikel (
//     id     SERIAL PRIMARY KEY,
//     judul  TEXT,
//     tags   TEXT[],     -- array of text
//     nilai  INTEGER[]   -- array of integer
// );

// Insert array — PHP array di-encode ke format PostgreSQL
$tags  = ['php', 'postgresql', 'database'];
$pgArr = '{' . implode(',', array_map(fn($t) => '"' . addslashes($t) . '"', $tags)) . '}';

$stmt = $pdo->prepare("INSERT INTO artikel (judul, tags) VALUES (:judul, :tags)");
$stmt->execute([':judul' => 'Belajar PostgreSQL', ':tags' => $pgArr]);

// Cara lebih bersih: cast langsung di query
$stmt = $pdo->prepare("
    INSERT INTO artikel (judul, tags)
    VALUES (:judul, string_to_array(:tags, ','))
");
$stmt->execute([':judul' => 'Belajar PostgreSQL', ':tags' => 'php,postgresql,database']);

// Query array — ANY, ALL, @>, &&
$stmt = $pdo->prepare("
    SELECT id, judul, tags
    FROM artikel
    WHERE :tag = ANY(tags)            -- artikel yang punya tag tertentu
    ORDER BY created_at DESC
");
$stmt->execute([':tag' => 'php']);

// Artikel yang punya SEMUA tag yang dicari
$stmt = $pdo->prepare("
    SELECT id, judul FROM artikel
    WHERE tags @> ARRAY[:tag1, :tag2]::text[]
");
$stmt->execute([':tag1' => 'php', ':tag2' => 'database']);

// Parse array dari PostgreSQL ke PHP
$stmt = $pdo->query("SELECT tags FROM artikel WHERE id = 1");
$row  = $stmt->fetch();
// $row['tags'] = '{php,postgresql,database}' — perlu di-parse
$tags = str_getcsv(trim($row['tags'], '{}'));
// ['php', 'postgresql', 'database']

<?php
// PostgreSQL mendukung UUID sebagai tipe native
// CREATE TABLE users (
//     id    UUID DEFAULT gen_random_uuid() PRIMARY KEY,
//     nama  TEXT
// );

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

// Atau generate dari PHP dan kirim ke 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, nama) VALUES (:id::uuid, :nama)");
$stmt->execute([':id' => $uuid, ':nama' => 'Siti']);

<?php
// Buat tipe ENUM di PostgreSQL
// CREATE TYPE status_order AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
// CREATE TABLE orders (status status_order DEFAULT 'pending');

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

// Cek nilai ENUM yang valid
$stmt = $pdo->query("SELECT unnest(enum_range(NULL::status_order)) AS nilai");
$nilaiValid = $stmt->fetchAll(PDO::FETCH_COLUMN);
// ['pending', 'processing', 'shipped', 'delivered', 'cancelled']

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

    try {
        // Step 1: Insert order (wajib berhasil)
        $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']]);
        $hasil['order_id'] = $stmt->fetchColumn();

        // Step 2: Kirim notifikasi (opsional — gagal tidak batalkan order)
        $pdo->exec("SAVEPOINT sp_notifikasi");
        try {
            $pdo->prepare("INSERT INTO notifikasi (user_id, pesan, order_id) VALUES (?, ?, ?)")
                ->execute([$data['user_id'], "Order #{$hasil['order_id']} dibuat", $hasil['order_id']]);
            $hasil['notifikasi'] = true;
        } catch (\Exception $e) {
            $pdo->exec("ROLLBACK TO SAVEPOINT sp_notifikasi");
            error_log("Notifikasi gagal: " . $e->getMessage());
        }

        // Step 3: Audit log (opsional — gagal tidak batalkan order)
        $pdo->exec("SAVEPOINT sp_audit");
        try {
            $pdo->prepare("INSERT INTO audit_log (aksi, data, created_at) VALUES ('order_buat', ?, NOW())")
                ->execute([json_encode($data)]);
            $hasil['audit'] = true;
        } catch (\Exception $e) {
            $pdo->exec("ROLLBACK TO SAVEPOINT sp_audit");
            error_log("Audit log gagal: " . $e->getMessage());
        }

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

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

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

// Mulai dengarkan channel
pg_query($koneksi, "LISTEN order_baru");
pg_query($koneksi, "LISTEN stok_habis");

echo "Menunggu notifikasi...\n";

// Loop polling notifikasi
while (true) {
    $notif = pg_get_notify($koneksi, PGSQL_ASSOC);

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

        // Proses berdasarkan channel
        match($notif['message']) {
            'order_baru'  => prosesOrderBaru($notif['payload']),
            'stok_habis'  => kirimAlertStok($notif['payload']),
            default       => null,
        };
    }

    usleep(100_000); // tunggu 100ms sebelum polling lagi
}

// Pengirim notifikasi (dari koneksi lain atau trigger database)
pg_query($koneksi, "NOTIFY order_baru, '{\"order_id\": 42, \"total\": 150000}'");

// Atau dari trigger PostgreSQL:
/*
CREATE OR REPLACE FUNCTION notify_order_baru()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('order_baru', row_to_json(NEW)::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

<?php
// COPY dari PHP ke PostgreSQL menggunakan pg_copy_from()
$koneksi = pg_connect("host=localhost dbname=myapp user=pguser password=secret");

// Siapkan data dalam format CSV (tab-separated secara default)
$data = [];
for ($i = 1; $i <= 100000; $i++) {
    $data[] = "$i\tProduk $i\t" . rand(10000, 1000000) . "\t1\n";
}

// COPY langsung dari array PHP — sangat cepat
$sukses = pg_copy_from($koneksi, 'produk', $data, "\t");
if (!$sukses) {
    throw new \RuntimeException("COPY gagal: " . pg_last_error($koneksi));
}
echo "100.000 baris berhasil diinsert via COPY\n";

// COPY dari file CSV ke tabel
$sql = "COPY produk (id, nama, harga, aktif) FROM STDIN WITH (FORMAT csv, HEADER true)";
pg_query($koneksi, $sql);

$file = fopen('produk_besar.csv', 'r');
while (!feof($file)) {
    $baris = fgets($file, 65536);
    pg_put_line($koneksi, $baris);
}
fclose($file);
pg_end_copy($koneksi);

// Export ke CSV via COPY TO
$result = pg_query($koneksi, "COPY (SELECT id, nama, harga FROM produk WHERE aktif) TO STDOUT WITH CSV HEADER");
$file   = fopen('export.csv', 'w');
while ($baris = pg_fetch_result($result, 0, 0)) {
    fwrite($file, $baris);
}
fclose($file);

-- 1. Serial (auto-increment) dan Sequence
-- MySQL:
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY);
-- PostgreSQL: SERIAL (shorthand) atau 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 (tidak ada di MySQL)
SELECT * FROM users WHERE nama ILIKE '%budi%'; -- case-insensitive

-- 3. String: hanya single quote (double quote untuk identifier)
SELECT 'teks'; -- OK
SELECT "kolom" FROM tabel; -- identifier, bukan string

-- 4. Boolean literal
SELECT TRUE, FALSE, NULL; -- bukan 1/0 seperti MySQL

-- 5. RETURNING — ambil nilai yang baru di-insert/update/delete
INSERT INTO t (nama) VALUES ('Budi') RETURNING id, created_at;
UPDATE t SET nama = 'Siti' WHERE id = 1 RETURNING *;
DELETE FROM t WHERE id = 1 RETURNING id; -- konfirmasi baris yang dihapus

-- 6. CTE (WITH) — lebih powerful di PostgreSQL
WITH ranked AS (
    SELECT id, nama, salary,
           RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS peringkat
    FROM employees
)
SELECT * FROM ranked WHERE peringkat = 1;

-- 7. Full-text search bawaan
SELECT * FROM artikel
WHERE to_tsvector('indonesian', konten) @@ plainto_tsquery('indonesian', 'belajar postgresql');