Belajar Menggunakan Database PostgreSQL di Ubuntu 16.04

Pendahuluan

Sistem manajemen database relasional adalah komponen utama bagi banyak situs web dan aplikasi. Mereka memberikan sebuah cara terstruktur untuk menyimpan data dan mengakes informasi dengan cara yang terstruktur.

PostgreSQL, atau Postgres, adalah sebuah sistem manajemen database relasional (RDBMS) yang memberikan sebuah implementasi bahasa query SQL. Sistem database ini merupakan pilihan populer bagi banyak proyek baik proyek kecil maupun besar karena memiliki banyak fitur canggih seperti transaksi yang reliabel dan konkurensi tanpa read locks.

Installasi

Repositori bawaan Ubuntu sudah memiliki paket Postgres sehingga kita bisa memasangnya dengan mudah menggunakan apt.

Kita dapat menginstall paket Postgres bersama dengan paket -contrib yang menambah beberapa fitur:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Sekarang setelah terpasang, kita dapat melanjutkan untuk mempelajari bagaimana ia bekerja dan apa perbedaannya dengan sistem manajemen database lain yang mungkin pernah pembaca gunakan.

Menggunakan Roles dan Databases PostgreSQL

Postgres menggunakan sebuah konsep bernama "roles" untuk menangani otentikasi dan otorisasi. Konsep ini mirip seperti akun reguler Unix namun Postgres menggunakan istilah "role" untuk membedakan hak-hak tiap user.

Saat installasi Postgres sudah mengatur otentikasi ident, yang artinya ia akan menghubungkan roles dengan akun sistem Unix/Linux. Jika sebuah role ada didalam Postgres, maka username Unix/Linux yang memiliki nama sama dapat melakukan login menggunakan role tersebut.

Masuk ke Akun Postgres

Prosedur installasi membuat sebuah akun bernama postgres yang memiliki role bawaan Postgres. Agar dapat menggunakan Postgres, kita harus masuk ke sistem menggunakan akun tersebut.

Masuk ke akun postgres di server dengan menggunakan:

sudo -i -u postgres

Kini kita dapat mengakses prompt Postgres dengan perintah

psql

Kita lalu dapat masuk dan berinteraksi dengan sistem manajemen databasenya.

Keluar dari prompt PostgreSQL dengan perintah:

\q

Mengakses Prompt Postgres Tanpa Berganti Akun

Kita juga dapat menjalankan perintah yang diinginkan langsung melalui sudo.

Misal, dicontoh sebelumnya, kita hanya ingin untuk masuk ke prompt Postgres. Kita dapat masuk ke role postgres dengan user saat ini menggunakan sudo:

sudo -u postgres psql

Perintah ini akan membawa kita masuk ke Prostgres tanpa masuk ke salah satu role-nya terlebih dahulu.

Kita dapat keluar dengan perintah:

\q

Membuat Role Baru

Saat ini kita hanya memiliki role postgres yang sudah ada dengan database. Kita dapat membuat role baru menggunakan perintah createrole. Flag --interactive akan meminta nilai-nilai yang diperlukan.

Jika login sebagai postgres, kita dapat membuat akun baru dengan perintah:

createuser --interactive

Apabila ingin menggunakan perintah sudo tanpa perlu masuk ke Postgres, cukup ketikkan:

sudo -u postgres createuser --interactive

Script di atas akan memberikan beberapa pilihan dan berdasarkan respon yang diberikan, ia akan mengeksekusi perintah Postgres yang diperlukan untuk membuat user baru sesuai keperluan.

Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Kita dapat memberikan opsi lebih dengan flag-flag tambahan. Periksa opsi yang tersedia lewat perintah:

man createuser

Membuat Database Baru

Sistem otentikasi Postgres secara otomatis akan membuat database yang memiliki nama sama dengan role sehingga role tersebut memiliki akses ke dalamnya.

Sebelumnya kita membuat sebuah user bernama sammy yang juga secara otomatis akan memiliki akses ke database bernama sammy juga. Kita dapat membuat database yang diperlukan menggunakan perintah createdb. Jika masuk menggunakan akun postgres, ketikkan perintah:

createdb sammy

Jika lebih memilih untuk menggunakan sudo untuk tiap perintah tanpa pindah akun, kita dapat mengetikkan:

sudo -u postgres createdb sammy

Buka Prompt Postgres dengan Role yang Baru

Untuk masuk menggunakan otentikasi berbasis ident, kita akan memerlukan user Linux dengan nama role beserta database Postgres yang sama.

Apabila user dengan nama yang sama tidak tersedia, maka kita perlu membuatnya dengan peirntah adduser. Kita perlu menjalankan perintah ini menggunakan hak akses sudo (bukan dari postgres tapi dari user Linux normal):

sudo adduser sammy

Setelah akun yang diperlukan tersedia, kita dapat pindah akun dan masuk ke database menggunakan perintah:

sudo -i -u sammy
psql

Atau bisa juga dengan satu baris berikut:

sudo -u sammy psql

Kamu akan masuk secara otomatis denan asumsi seluruh data yang diperlukan sudah sesuai.

Jika ingin terhubung ke database yang berbeda dengan user sekarang, kita dapat menuliskan nama databasenya seperti ini:

psql -d postgres

Setelah masuk, kita dapat memeriksa informasi koneksi yang berlangsung dengan perintah:

\conninfo

You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Membuat dan Menghapus Tabel

Setelah dapat terhubung ke sistem database PostgreSQL, kita dapat melakukan beberapa tugas sederhana.

Pertama, kita akan membuat sebuah tabel untuk menyimpan beberapa data. Mari kita buat sebuah tabel yang mendeskripsikan daftar peralatan sebuah taman bermain.

Sintaks dasar untuk membuat tabel adalah sebagai berikut:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

Seperti yang dapat pembaca lihat, kita menuliskan nama tabel, lalu menuliskan kolom yang diinginkan, juga tipe kolom dan panjang maksimum data yang dapat diisikan. Kita juga dapat menambahkan konstrain untuk tiap kolom jika perlu.

Pelajari tentang bagaimana membuat dan mengatur tabel di Postgres disini.

Sekarang kita akan membuat tabel seperti berikut ini:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Kita telah membuat tabel playground untuk menyimpan data peralatan yang dimiliki. Tabel ini dimulai dengan sebuah ID yang bertipe serial. Tipe ada ini adalah sebuah auto-incrementing integer. Kita dapat memberikan kontrain primary key untuk kolom ini yang artinya isi kolom harus unik dan tidak null.

Untuk dua kolom equip_id dan install_date kita tidak memberikan panjangnya. Hal ini karena beberapa tipe kolom tidak memerlukan panjang maksimum.

Kita lalu membuat agar kolom type dan color tidak dapat dikosongkan. Kita juga membuat sebuah kolom location dan sebuah konstrain untuk membatasi nilainya dari 8 opsi. Kolom terakhir adalah kolom install_date untuk memberitahu kapan kita memasangnya.

Kita dapat melihat tabel yang baru dibuat dengan perintah:

\d

 List of relations
 Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Kita sudah memiliki tabel playground, tapi kita juga memiliki sebuah data lain bernama playground_equip_id_seq dengan tipe sequence. Data ini merupakan representasi tipe serial yang tadi kita berikan untuk kolom equip_id. Data ini akan mencatat nomor berikutnya untuk membuat data otomatis bagi kolom bertipe serial.

Jika ingin melihat data tabel tanpa sequence-nya, ketikkan:

\dt

Output          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

Add, Query, dan Delete Data Di Table

Setelah memiliki sebuah tabel, kita dapat memasukkan beberapa data ke dalamnya.

Mari kita tanpa sebuah ayunan (swing) dan seluncuran (slide). Kita memasukkan data dengan memanggil tabel yang ingin ditambahkan, lalu memberikan nama kolom dan data untuk tiap kolom tersebut.

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');

Ada beberapa hal yang perlu diperhatikan saat memasukkan data untuk meminimalkan kesalahan. Pertama, nama kolom tidak boleh diberi tanda '', namun isi kolom tersebut perlu diberikan.

Hal lain yang perlu dilakukan adalah memastikan untuk tidak memberikan data kolom equip_id secara manual. Hal ini karena isinya akan dibuat secara otomatis.

Kita dapat melihat data yang sudah dimasukkan dengan perintah:

SELECT * FROM playground;

Output equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2014-04-28
        2 | swing | yellow | northwest | 2010-08-16
(2 rows)

Disini, kita dapat melihat bahwa kolom equip_id secara otomatis sudah ada dan data yang lain sukses ditambahkan.

Jika seluncuran (slide) rusak maka kita perlu mengahpusnya. Untuk menghapus data dari tabel gunakan perintah:

DELETE FROM playground WHERE type = 'slide';

Jika kita ingin melihat isi tabel kita lagi, kita akan menemukan bahwa seluncuran (slide) sudah tidak ada:

SELECT * FROM playground;

Output equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2010-08-16
(1 row)

Bagaimana Cara Menambah dan Menghapus Kolom dari Tabel

Jika ingin memodifikasi sebuah tabel setelah dibuat untuk menambah kolom baru, kita dapat melakukannya dengan mudah.

Kita dapat menambah sebuah kolom untuk menampilkan kapan terakhirkali peralatan di maintenance:

ALTER TABLE playground ADD last_maint date;

Untuk melihat informasi tabel kembali, gunakan perintah (perhatikan kolom terakhir yang ditambahkan):

SELECT * FROM playground;

Output equip_id | type  | color  | location  | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2010-08-16   | 
(1 row)

Kita juga dapat menghapu sebuah kolom dengan mudah. Apabila ternyata petugas taman menggunakan database yang berbeda untuk menyimpan riwayat maintenance, kita dapat menghapusnya dengan cara:

ALTER TABLE playground DROP last_maint;

Bagaimana Cara Mengupdate Data di Tabel

Kita tahu bagaimana menambah data ke dalam sebuah tabel dan bagaimana menghapusnya, tapi kita belum membahas bagaimana untuk memodifikasi data yang sudah ada.

Misalnya, kita ingin mengambil data dengan tipe "swing" dan mengganti warnanya menjadi merah (red) (ini akan mengganti seluruh data yang memiliki tipe "swing"):

UPDATE playground SET color = 'red' WHERE type = 'swing';

Kita dapat memeriksa bahwa perintah berhasil dengan:

SELECT * FROM playground;

 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2010-08-16
(1 row)

Seperti yang terlihat, data warnanya sudah berubah.

Penutup

Sekarang kita sudah memasang dan menyiapkan PostgreSQL di Ubuntu 16.04. Tunggu artikel lain seputar PostgreSQL ya.

Diterjemahkan dari How To Install and Use PostgreSQL on Ubuntu 16.04