{"id":10353,"date":"2025-10-07T15:43:42","date_gmt":"2025-10-07T08:43:42","guid":{"rendered":"https:\/\/www.jetorbit.com\/panduan\/?p=10353"},"modified":"2025-10-07T16:43:02","modified_gmt":"2025-10-07T09:43:02","slug":"cheatsheet-postgresql-yang-wajib-kamu-ketahui","status":"publish","type":"post","link":"https:\/\/www.jetorbit.com\/panduan\/cheatsheet-postgresql-yang-wajib-kamu-ketahui\/","title":{"rendered":"Cheatsheet PostgreSQL yang Wajib Kamu Ketahui"},"content":{"rendered":"\n

PostgreSQL adalah salah satu sistem manajemen basis data relasional (RDBMS) open-source yang paling populer dan powerful di dunia. Dikenal dengan kehandalannya, fitur-fitur canggih, dan dukungan untuk berbagai tipe data, PostgreSQL telah menjadi pilihan utama bagi banyak developer dan perusahaan besar. Dalam artikel ini, kami akan membagikan cheatsheet PostgreSQL yang komprehensif untuk membantu Anda dalam mengelola database dengan lebih efisien.<\/p>\n\n\n\n

Mengapa Cheatsheet PostgreSQL Penting?<\/p>\n\n\n\n

Cheatsheet PostgreSQL sangat berguna bagi developer, database administrator, dan DevOps engineer yang bekerja dengan database setiap hari. Dengan memiliki referensi cepat tentang perintah-perintah penting, Anda dapat menghemat waktu dan meningkatkan produktivitas. Baik Anda seorang pemula yang baru belajar PostgreSQL atau seorang profesional berpengalaman, cheatsheet ini akan menjadi panduan praktis yang sangat membantu.<\/p>\n\n\n\n

    \nKoneksi ke PostgreSQL\n<\/ol>\n\n\n\n

    Sebelum mulai bekerja dengan PostgreSQL, Anda perlu tahu cara terhubung ke database server:<\/p>\n\n\n\n

    psql -U username -d database_name -h hostname -p port<\/code><\/pre>\n\n\n\n
    Koneksi menggunakan psql:<\/p>\n\n\n\n
    Keluar dari psql:<\/p>\n\n\n\n
    \\q\n\\quit<\/code><\/pre>\n\n\n\n
    Koneksi dengan password:<\/p>\n\n\n\n
    psql -U username -W<\/code><\/pre>\n\n\n\n
    Melihat daftar database:<\/p>\n\n\n\n
    \\l\n\\list<\/code><\/pre>\n\n\n\n
    Beralih ke database lain:<\/p>\n\n\n\n
    \\c database_name<\/code><\/pre>\n\n\n\n
      \nOperasi Database Dasar\n<\/ol>\n\n\n\n
      Membuat Database Baru<\/p>\n\n\n\n
      Untuk membuat database baru di PostgreSQL:<\/p>\n\n\n\n
      CREATE DATABASE nama_database;<\/code><\/pre>\n\n\n\n
      Membuat database dengan encoding tertentu:<\/p>\n\n\n\n
      CREATE DATABASE nama_database ENCODING 'UTF8';<\/code><\/pre>\n\n\n\n
      Menghapus Database<\/p>\n\n\n\n
      DROP DATABASE nama_database;<\/code><\/pre>\n\n\n\n
      DROP DATABASE IF EXISTS nama_database;<\/code><\/pre>\n\n\n\n
      Melihat Informasi Database<\/p>\n\n\n\n
      Melihat database yang sedang digunakan:<\/p>\n\n\n\n
      SELECT current_database();<\/code><\/pre>\n\n\n\n
      Melihat ukuran database:<\/p>\n\n\n\n
      SELECT pg_size_pretty(pg_database_size('nama_database'));<\/code><\/pre>\n\n\n\n
        \nOperasi Tabel\n<\/ol>\n\n\n\n
        Membuat Tabel<\/p>\n\n\n\n
        CREATE TABLE users (\nid SERIAL PRIMARY KEY,\nusername VARCHAR(50) UNIQUE NOT NULL,\nemail VARCHAR(100) UNIQUE NOT NULL,\ncreated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);<\/code><\/pre>\n\n\n\n
        Melihat Struktur Tabel<\/p>\n\n\n\n
        Menampilkan deskripsi tabel:<\/p>\n\n\n\n
        \\d nama_tabel<\/code><\/pre>\n\n\n\n
        Melihat semua tabel:<\/p>\n\n\n\n
        \\dt<\/code><\/pre>\n\n\n\n
        Melihat detail lengkap tabel:<\/p>\n\n\n\n
        \\d+ nama_tabel<\/code><\/pre>\n\n\n\n
        Mengubah Tabel<\/p>\n\n\n\n
        Menambah kolom baru:<\/p>\n\n\n\n
        ALTER TABLE users ADD COLUMN phone VARCHAR(20);<\/code><\/pre>\n\n\n\n
        Menghapus kolom:<\/p>\n\n\n\n
        ALTER TABLE users DROP COLUMN phone;<\/code><\/pre>\n\n\n\n
        Mengubah tipe data kolom:<\/p>\n\n\n\n
        ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);<\/code><\/pre>\n\n\n\n
        Menambah constraint:<\/p>\n\n\n\n
        ALTER TABLE users ADD CONSTRAINT check_email CHECK (email LIKE '%@%');<\/code><\/pre>\n\n\n\n
        Menghapus Tabel<\/p>\n\n\n\n
        DROP TABLE nama_tabel;<\/code><\/pre>\n\n\n\n
        DROP TABLE IF EXISTS nama_tabel CASCADE;<\/code><\/pre>\n\n\n\n
          \nManipulasi Data (CRUD Operations)\n<\/ol>\n\n\n\n
          INSERT – Menambah Data<\/p>\n\n\n\n
          Menambah satu baris:<\/p>\n\n\n\n
          INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');<\/code><\/pre>\n\n\n\n
          Menambah multiple baris:<\/p>\n\n\n\n
          INSERT INTO users (username, email) VALUES\n('user1', 'user1@example.com'),\n('user2', 'user2@example.com'),\n('user3', 'user3@example.com');<\/code><\/pre>\n\n\n\n
          Insert dengan return:<\/p>\n\n\n\n
          INSERT INTO users (username, email) VALUES ('jane', 'jane@example.com') RETURNING id;<\/code><\/pre>\n\n\n\n
          SELECT – Membaca Data<\/p>\n\n\n\n
          Select semua data:<\/p>\n\n\n\n
          SELECT * FROM users;<\/code><\/pre>\n\n\n\n
          Select kolom tertentu:<\/p>\n\n\n\n
          SELECT username, email FROM users;<\/code><\/pre>\n\n\n\n
          Select dengan kondisi:<\/p>\n\n\n\n
          SELECT * FROM users WHERE id = 1;<\/code><\/pre>\n\n\n\n
          Select dengan multiple kondisi:<\/p>\n\n\n\n
          SELECT * FROM users WHERE username = 'john_doe' AND email LIKE '%@example.com';<\/code><\/pre>\n\n\n\n
          Select dengan sorting:<\/p>\n\n\n\n
          SELECT * FROM users ORDER BY created_at DESC;<\/code><\/pre>\n\n\n\n
          Select dengan limit:<\/p>\n\n\n\n
          SELECT * FROM users LIMIT 10 OFFSET 0;<\/code><\/pre>\n\n\n\n
          UPDATE – Mengubah Data<\/p>\n\n\n\n
          Update satu kolom:<\/p>\n\n\n\n
          UPDATE users SET email = 'newemail@example.com' WHERE id = 1;<\/code><\/pre>\n\n\n\n
          Update multiple kolom:<\/p>\n\n\n\n
          UPDATE users SET username = 'new_username', email = 'new@example.com' WHERE id = 1;<\/code><\/pre>\n\n\n\n
          Update dengan return:<\/p>\n\n\n\n
          UPDATE users SET email = 'updated@example.com' WHERE id = 1 RETURNING *;<\/code><\/pre>\n\n\n\n
          DELETE – Menghapus Data<\/p>\n\n\n\n
          Hapus dengan kondisi:<\/p>\n\n\n\n
          DELETE FROM users WHERE id = 1;<\/code><\/pre>\n\n\n\n
          Hapus semua data:<\/p>\n\n\n\n
          DELETE FROM users;<\/code><\/pre>\n\n\n\n
          Hapus dengan return:<\/p>\n\n\n\n
          DELETE FROM users WHERE id = 1 RETURNING *;<\/code><\/pre>\n\n\n\n
            \nQuery Lanjutan\n<\/ol>\n\n\n\n
            JOIN Operations<\/p>\n\n\n\n
            INNER JOIN:<\/p>\n\n\n\n
            SELECT users.username, orders.order_date\nFROM users\nINNER JOIN orders ON users.id = orders.user_id;<\/code><\/pre>\n\n\n\n
            LEFT JOIN:<\/p>\n\n\n\n
            SELECT users.username, orders.order_date\nFROM users\nLEFT JOIN orders ON users.id = orders.user_id;<\/code><\/pre>\n\n\n\n
            RIGHT JOIN:<\/p>\n\n\n\n
            SELECT users.username, orders.order_date\nFROM users\nRIGHT JOIN orders ON users.id = orders.user_id;<\/code><\/pre>\n\n\n\n
            FULL OUTER JOIN:<\/p>\n\n\n\n
            SELECT users.username, orders.order_date\nFROM users\nFULL OUTER JOIN orders ON users.id = orders.user_id;<\/code><\/pre>\n\n\n\n
            Subqueries<\/p>\n\n\n\n
            Subquery di WHERE:<\/p>\n\n\n\n
            SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);<\/code><\/pre>\n\n\n\n
            Subquery di SELECT:<\/p>\n\n\n\n
            SELECT username, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count FROM users;<\/code><\/pre>\n\n\n\n
            Aggregate Functions<\/p>\n\n\n\n
            COUNT:<\/p>\n\n\n\n
            SELECT COUNT(*) FROM users;<\/code><\/pre>\n\n\n\n
            SUM:<\/p>\n\n\n\n
            SELECT SUM(total) FROM orders;<\/code><\/pre>\n\n\n\n
            AVG:<\/p>\n\n\n\n
            SELECT AVG(total) FROM orders;<\/code><\/pre>\n\n\n\n
            MAX dan MIN:<\/p>\n\n\n\n
            SELECT MAX(total), MIN(total) FROM orders;<\/code><\/pre>\n\n\n\n
            GROUP BY:<\/p>\n\n\n\n
            SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id;<\/code><\/pre>\n\n\n\n
            HAVING:<\/p>\n\n\n\n
            SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 5;<\/code><\/pre>\n\n\n\n
            Indexes<\/p>\n\n\n\n
            Membuat index:<\/p>\n\n\n\n
            CREATE INDEX idx_username ON users(username);<\/code><\/pre>\n\n\n\n
            Membuat unique index:<\/p>\n\n\n\n
            CREATE UNIQUE INDEX idx_email ON users(email);<\/code><\/pre>\n\n\n\n
            Membuat index untuk multiple kolom:<\/p>\n\n\n\n
            CREATE INDEX idx_name_email ON users(username, email);<\/code><\/pre>\n\n\n\n
            Melihat index:<\/p>\n\n\n\n
            \\di<\/code><\/pre>\n\n\n\n
            Menghapus index:<\/p>\n\n\n\n
            DROP INDEX idx_username;<\/code><\/pre>\n\n\n\n
              \nUser Management dan Permissions\n<\/ol>\n\n\n\n
              Membuat User<\/p>\n\n\n\n
              CREATE USER username WITH PASSWORD 'password';<\/code><\/pre>\n\n\n\n
              CREATE USER username WITH ENCRYPTED PASSWORD 'password';<\/code><\/pre>\n\n\n\n
              Mengubah Password User<\/p>\n\n\n\n
              ALTER USER username WITH PASSWORD 'new_password';<\/code><\/pre>\n\n\n\n
              Memberikan Privileges<\/p>\n\n\n\n
              Grant all privileges pada database:<\/p>\n\n\n\n
              GRANT ALL PRIVILEGES ON DATABASE database_name TO username;<\/code><\/pre>\n\n\n\n
              Grant privileges pada tabel:<\/p>\n\n\n\n
              GRANT SELECT, INSERT, UPDATE ON TABLE table_name TO username;<\/code><\/pre>\n\n\n\n
              Grant privileges pada schema:<\/p>\n\n\n\n
              GRANT ALL ON SCHEMA public TO username;<\/code><\/pre>\n\n\n\n
              Mencabut Privileges<\/p>\n\n\n\n
              REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;<\/code><\/pre>\n\n\n\n
              Melihat User dan Role<\/p>\n\n\n\n
              Melihat semua user:<\/p>\n\n\n\n
              \\du<\/code><\/pre>\n\n\n\n
              Menghapus User<\/p>\n\n\n\n
              DROP USER username;<\/code><\/pre>\n\n\n