PostgreSQL → Basic Statements

Sumber: Luca Ferrari, Enrico Pirozzi - Learn PostgreSQL Use, manage, and build secure and scalable databases with PostgreSQL 16 (2023) - Chapter 4

Dalam bab ini, kita akan membahas perintah SQL dasar untuk PostgreSQL, yaitu perintah Data Definition Language (DDL) dan Data Manipulation Language (DML). Secara sederhana, perintah DDL digunakan untuk mengelola basis data dan tabel, sedangkan perintah DML digunakan untuk menyisipkan, menghapus, memperbarui, dan memilih data di dalam basis data. Dalam bab ini, kita juga akan menyelami lingkungan psql. Seperti yang telah dipelajari di Bab 2, Mengenal Klaster Anda, psql dapat digambarkan sebagai lingkungan shell PostgreSQL; ini adalah gerbang yang harus kita lalui untuk mulai menulis perintah secara native di PostgreSQL. Kita perlu ingat bahwa psql selalu ada di setiap instalasi PostgreSQL yang kita gunakan, dan sangat layak untuk dipelajari karena ini adalah lingkungan yang sangat kuat untuk mengelola data dan basis data kita.

Pernyataan dasar dan psql adalah fondasi yang akan kita gunakan untuk membangun pengetahuan kita tentang PostgreSQL. Oleh karena itu, membaca dan memahami bab ini sangat penting untuk memahami beberapa topik yang lebih kompleks yang akan dibahas nanti.

Berikut adalah daftar yang akan kita pelajari dalam bab ini:

• Menyiapkan lingkungan pengembangan kita

• Membuat dan mengelola basis data

• Mengelola tabel

• Memahami pernyataan manipulasi tabel dasar

Persyaratan Teknis

Pada titik ini dalam buku ini, kita telah mempelajari cara menginstal PostgreSQL dan cara mengkonfigurasi pengguna, tetapi jika Anda belum membaca bab-bab sebelumnya, Anda dapat dengan mudah mulai mengikuti langkah-langkah berikut menggunakan image Docker seperti yang dijelaskan di bawah ini.

Menggunakan Image Docker

Jika Anda ingin mengikuti langkah-langkah berikut tanpa menginstal dan mengkonfigurasi PostgreSQL, Anda dapat melakukannya dengan mudah menggunakan image Docker di repositori GitHub (detail tentang cara mengaturnya dibahas di Bab 1, Pengenalan PostgreSQL). Jadi, mari kita mulai container standalone seperti yang dijelaskan di Bab 1, Pengenalan PostgreSQL, dan kemudian jalankan perintah berikut:

$ sudo docker exec -it standalone_learn_postgresql_1 /bin/bash

Setelah menjalankan perintah ini, kita akan berada di dalam container standalone_learn_postgresql_1 dalam shell root:

root@learn_postgresql:/#

Menghubungkan ke Basis Data

Bahkan jika kita tidak menggunakan container Docker tetapi menggunakan instalasi PostgreSQL native seperti yang dijelaskan di Bab 1, Pengenalan PostgreSQL, kita akan mencapai hasil yang sama seperti di atas, menggunakan pernyataan yang sama yang dijalankan sebagai pengguna postgres:

root@learn_postgresql:/# su - postgres
postgres@learn_postgresql:~$ psql
postgres=#

Sekarang mari kita aktifkan mode ekspansi menggunakan perintah \x:

postgres=# \x
Expanded display is on.

Kemudian mari kita daftar semua basis data yang ada di klaster:

postgres=# \l
List of databases
-[ RECORD 1 ]-----+--------------
Name              | forumdb
Owner             | forum
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
ICU Locale        |
Locale Provider   | libc
Access privileges |

Untuk alasan ruang, kami hanya melaporkan basis data forumdb yang diimpor dari skrip Docker, tetapi ada juga basis data template0, template1, dan postgres seperti yang kita lihat di Bab 2, Mengenal Klaster Anda. Terakhir, mari kita sambungkan ke basis data forumdb:

postgres=# \c forumdb
You are now connected to database "forumdb" as user "postgres".

Sekarang setelah kita selesai menyiapkan lingkungan pengembangan kita, kita bisa melanjutkan untuk membuat basis data di dalamnya.

Membuat dan Mengelola Basis Data

Di bagian ini, kita akan mulai dengan membuat basis data pertama kita, kemudian kita akan belajar cara menghapus basis data, dan terakhir, cara membuat basis data baru dari basis data yang sudah ada. Kita juga akan menganalisis sudut pandang Administrator Basis Data (DBA). Kita akan melihat apa yang terjadi di balik layar saat kita membuat basis data baru dan mempelajari beberapa fungsi dasar yang berguna bagi DBA untuk mendapatkan gambaran tentang ukuran sebenarnya dari basis data.

Mari kita lihat cara membuat basis data dari awal dan apa yang terjadi di balik layar saat basis data dibuat.

Membuat Basis Data

Untuk membuat basis data bernama namabasisdata dari awal, Anda perlu menjalankan pernyataan sederhana ini:

CREATE DATABASE namabasisdata;

Catatan: SQL adalah bahasa yang tidak peka huruf besar-kecil, jadi kita bisa menulis semua perintah dengan huruf besar atau kecil.

Sekarang, mari kita lihat apa yang terjadi di balik layar saat kita membuat basis data baru. PostgreSQL melakukan langkah-langkah berikut:

1. Membuat salinan fisik dari basis data template, template1.

2. Menetapkan nama basis data ke basis data yang baru saja disalin.

Basis data template1 adalah basis data yang dibuat oleh proses initdb selama inisialisasi klaster PostgreSQL.

Mengelola Basis Data

Kita baru saja melihat cara membuat basis data. Di bagian ini, kita akan melihat cara mengelola basis data, cara mendaftar semua basis data yang ada di klaster, cara membuat basis data mulai dari basis data yang sudah ada, cara menghapus basis data, dan apa yang terjadi secara internal, di balik layar, saat kita membuat dan menghapus basis data.

Memperkenalkan Skema

Seperti yang dilaporkan di Bab 1, Pengenalan PostgreSQL: “basis data dapat diorganisir ke dalam namespace, yang disebut skema. Skema adalah nama mnemonik yang dapat diberikan pengguna untuk mengatur objek basis data, seperti tabel, ke dalam koleksi yang lebih terstruktur. Skema tidak dapat bersarang, sehingga mereka mewakili namespace datar.”

Mengacu kembali ke Bab 1, Pengenalan PostgreSQL, kita telah mempelajari bahwa ada dua jenis pengguna, pengguna normal dan superuser:

• Superuser dapat melakukan segala sesuatu di seluruh basis data dan skema.

• Pengguna normal dapat melakukan operasi tergantung pada set hak akses mereka.

PostgreSQL dan Skema Publik

Mulai dari PostgreSQL 15, PostgreSQL telah mengubah cara mengelola skema publik. Di bagian ini, kita akan melihat cara kerjanya. Sebelum PostgreSQL 15, setiap pengguna dapat melakukan operasi DDL apa pun pada skema publik. PostgreSQL 15 memperkenalkan konsep penghapusan hak akses global dari skema publik.

Mulai dari PostgreSQL 15:

• Pengguna normal tidak akan dapat menjalankan DDL pada skema publik.

• Pengguna normal tidak akan dapat melakukan DML pada skema publik kecuali mereka menerima izin dari superuser.

Mari kita gunakan contoh untuk menjelaskan lebih baik cara kerja fitur baru ini. Kita akan bekerja seolah-olah kita berada pada versi PostgreSQL <=14.x.

Berikut adalah langkah-langkah yang akan kita jalankan (beberapa instruksi akan dijelaskan nanti dalam buku ini):

1. Kita akan membuat pengguna normal bernama penggunaku.

2. Kita akan terhubung ke basis data sebagai pengguna penggunaku.

3. Sebagai penggunaku, kita akan mencoba membuat tabel baru bernama tabelku.

Di bawah ini, Anda akan menemukan eksekusi dari apa yang ditulis di atas:

forumdb=# create user penggunaku with password 'SuperSecret' login;
CREATE ROLE
forumdb=# set role to penggunaku;
SET
forumdb=> create table tabelku(id integer);
ERROR: permission denied for schema public
LINE 1: create table tabelku(id integer);

Seperti yang kita lihat, pengguna normal tidak dapat membuat tabel (DDL) pada skema publik.

Variabel search_path

PostgreSQL memiliki banyak variabel sistem. Salah satunya disebut search_path. Variabel search_path berisi urutan skema yang digunakan PostgreSQL untuk menemukan tabel; nilai default search_path adalah $user,public. Ini berarti bahwa pertama-tama PostgreSQL akan mencari semua tabel di skema yang memiliki nama yang sama dengan tabel pengguna, kemudian akan mencari skema publik.

Misalnya, jika kita memiliki pengguna bernama forum, dan kita ingin menampilkan semua catatan yang ada di tabel bernama cities, pertama-tama PostgreSQL akan mencari tabel cities di skema forum, dan jika tabel cities tidak dapat ditemukan di skema forum, PostgreSQL akan mencari tabel cities di skema publik.

Cara yang Benar untuk Mulai Bekerja

Mari kita mulai dari awal dan jalankan langkah-langkah berikut:

1. Sebagai superuser, mari kita buat basis data baru bernama myforumdb dan sambungkan ke sana.

2. Sebagai superuser, mari kita buat pengguna baru bernama myforum.

3. Sebagai superuser, mari kita buat skema baru bernama myforum dengan otorisasi untuk pengguna myforum.

4. Mari kita sambungkan ke basis data sebagai pengguna myforum:

postgres=# create database myforumdb;
CREATE DATABASE
postgres=# \c myforumdb
You are now connected to database "myforumdb" as user "postgres".
myforumdb=# create user myforum with password 'SuperSecret' login;
CREATE ROLE
myforumdb=# create schema myforum authorization myforum;
CREATE SCHEMA

Sekarang mari kita coba sambungkan ke basis data myforumdb sebagai pengguna myforum:

postgres@learn_postgresql:/$ psql -U myforum myforumdb
myforumdb=>

Mari kita coba membuat tabel baru bernama tabelku seperti yang telah kita lakukan sebelumnya:

myforumdb=> create table tabelku(id integer);
CREATE TABLE

Sekarang berhasil! Ini berhasil karena tabel tabelku telah dibuat di dalam skema myforum seperti yang telah kita jelaskan di atas.

Catatan: Basis data forumdb yang disediakan dengan container sudah diatur untuk digunakan menggunakan pengguna forum, yang merujuk ke skema forum.

Mendaftar Semua Tabel

Sekarang mari kita sambungkan ke basis data forumdb sebagai pengguna forum:

postgres@learn_postgresql:/$ psql -U forum forumdb
forumdb=>

Untuk mendaftar semua tabel yang ada di basis data forumdb, kita harus menggunakan perintah psql \dt. Perintah \dtmembuat daftar semua tabel yang ada di basis data forumdb:

forumdb=> \dt
List of relations
Schema | Name         | Type  | Owner
--------+--------------+-------+-------
forum  | categories   | table | forum
forum  | j_posts_tags | table | forum
forum  | posts        | table | forum
forum  | tags         | table | forum
forum  | users        | table | forum
(5 rows)

Membuat Basis Data Baru dari Template yang Dimodifikasi

Sekarang setelah kita mempelajari cara mendaftar semua tabel di basis data, mari kita pastikan bahwa setiap perubahan yang dibuat pada basis data template1 akan terlihat oleh semua basis data yang akan dibuat nanti. Kita akan melakukan langkah-langkah berikut:

1. Sambungkan ke basis data template1.

2. Buat tabel bernama dummytable di dalam basis data template1.

3. Buat basis data baru bernama dummydb.

Mari kita mulai membuat basis data menggunakan langkah-langkah berikut:

1. Sambungkan ke basis data template1:

postgres@learn_postgresql:/$ psql template1
template1=#

1. Sebagai superuser, buat tabel bernama dummytable. Untuk saat ini, kita tidak perlu khawatir tentang sintaks pasti untuk membuat tabel; ini akan dijelaskan lebih rinci nanti:

template1=# create table dummytable (dummyfield integer not null primary key);
CREATE TABLE

1. Gunakan perintah \dt untuk menampilkan daftar tabel yang ada di basis data template1:

template1=# \dt
List of relations
Schema | Name        | Type  | Owner
--------+-------------+-------+----------
public | dummytable  | table | postgres
(1 row)

1. Jadi, kita telah berhasil menambahkan tabel baru ke basis data template1. Sekarang mari kita coba membuat basis data baru bernama dummydb dan membuat daftar semua tabel di basis data dummydb:

template1=# create database dummydb;
CREATE DATABASE
template1=# \c dummydb
You are now connected to database "dummydb" as user "postgres".

Basis data dummydb berisi tabel berikut:

dummydb=# \dt
List of relations
Schema | Name        | Type  | Owner
--------+-------------+-------+----------
public | dummytable  | table | postgres
(1 row)

Seperti yang diharapkan, di basis data dummydb, kita dapat melihat tabel yang sebelumnya dibuat di basis data template1.

Penting: Ingat bahwa setiap perubahan yang dibuat pada basis data template1 akan ada di semua basis data yang dibuat setelah perubahan ini.

Sekarang kita akan menghapus basis data dummydb dan tabel dummy di basis data template1.

Menghapus Tabel dan Basis Data

Di bagian berikut, Anda akan belajar cara menghapus tabel dan basis data. Perintah yang akan kita pelajari adalah sebagai berikut:

• DROP TABLE: Digunakan untuk menghapus tabel di basis data.

• DROP DATABASE: Digunakan untuk menghapus basis data di klaster.

Menghapus Tabel

Di PostgreSQL, perintah yang diperlukan untuk menghapus tabel adalah cukup DROP TABLE namatabel. Untuk melakukan ini, kita harus terhubung ke basis data tempat tabel tersebut berada, dan kemudian menjalankan perintah DROP TABLE namatabel.

Misalnya, jika kita ingin menghapus tabel dummytable dari basis data template1, kita harus mengambil langkah-langkah berikut. Kita sambungkan ke basis data template1 menggunakan perintah berikut:

dummydb=# \c template1
You are now connected to database "template1" as user "postgres".

Dan kita bisa menghapus tabel menggunakan perintah berikut:

template1=# drop table dummytable;
DROP TABLE

Menghapus Basis Data

Di PostgreSQL, perintah yang diperlukan untuk menghapus basis data adalah cukup DROP DATABASE namabasisdata; misalnya, jika kita ingin menghapus basis data dummydb, kita harus menjalankan perintah berikut:

template1=# drop database dummydb;
DROP DATABASE

Dengan ini, semuanya sekarang telah dikembalikan ke keadaan seperti di awal bab ini.

Membuat Salinan Basis Data

Langkah-langkah berikut menunjukkan cara membuat basis data baru dari basis data template:

1. Buat salinan dari basis data forumdb di klaster PostgreSQL yang sama dengan menjalankan perintah berikut:

template1=# create database forumdb2 template forumdb;
CREATE DATABASE

1. Dengan menggunakan perintah ini, Anda cukup memberi tahu PostgreSQL untuk membuat basis data baru bernama forumdb2 menggunakan basis data forumdb sebagai template.

Sambungkan ke basis data forumdb2 sebagai pengguna forum:

postgres@learn_postgresql:/$ psql -U forum forumdb2
forumdb2=>

1. Daftar semua tabel di basis data forumdb2:

forumdb2=> \dt
List of relations
Schema | Name         | Type  | Owner
--------+--------------+-------+-------
forum  | categories   | table | forum
forum  | j_posts_tags | table | forum
forum  | posts        | table | forum
forum  | tags         | table | forum
forum  | users        | table | forum
(5 rows)

Anda dapat melihat bahwa tabel yang sama yang ada di basis data forumdb sekarang ada di basis data ini.

Mengkonfirmasi Ukuran Basis Data

Kita sekarang akan membahas pertanyaan tentang bagaimana seseorang dapat menentukan ukuran sebenarnya dari basis data. Ada dua metode yang dapat Anda gunakan untuk melakukan ini: psql dan SQL. Mari kita bandingkan keduanya di bagian berikut.

Metode psql

Kita dapat memeriksa ukuran basis data menggunakan metode psql, dengan langkah-langkah berikut:

1. Pertama, mari kita sambungkan ke forumdb dan kembali ke mode ekspansi:

postgres@learn_postgresql:/$ psql -U forum forumdb
forumdb=> \x
Expanded display is on.

1. Kemudian, jalankan perintah berikut:

forumdb=# \l+ forumdb
List of databases
-[ RECORD 1 ]-----+-----------
Name              | forumdb
Owner             | forum
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
ICU Locale        |
Locale Provider   | libc
Access privileges |
Size              | 7685 kB
Tablespace        | pg_default
Description       |

Di kolom Size, Anda sekarang dapat melihat ukuran sebenarnya dari basis data pada saat itu.

Metode SQL

Saat menggunakan metode yang diuraikan di atas, Anda mungkin menemukan bahwa Anda tidak dapat terhubung ke basis data Anda melalui perintah psql. Ini terjadi ketika kita hanya memiliki akses web ke basis data; misalnya, jika kita hanya memiliki akses instalasi sisi server pgadmin4. Jika ini terjadi, metode SQL adalah pendekatan alternatif yang akan memungkinkan Anda menemukan informasi yang sama. Untuk menggunakan metode ini, lengkapi langkah-langkah berikut:

1. Jalankan perintah berikut:

forumdb=> select pg_database_size('forumdb');
-[ RECORD 1 ]----+--------
pg_database_size | 7869231

Fungsi pg_database_size(nama) mengembalikan ruang disk yang digunakan oleh basis data yang disebut forumdb. Ini berarti bahwa hasilnya adalah jumlah byte yang digunakan oleh basis data.

1. Jika Anda menginginkan hasil yang lebih mudah dibaca dalam istilah “manusia”, Anda bisa menggunakan fungsi pg_size_pretty dan menulis berikut:

forumdb=> select pg_size_pretty(pg_database_size('forumdb'));
-[ RECORD 1 ]--+--------
pg_size_pretty | 7685 kB

Seperti yang Anda lihat, kedua metode memberikan hasil yang sama.

Di Balik Layar Pembuatan Basis Data

Kita baru saja mempelajari perintah apa yang digunakan untuk membuat basis data baru, tetapi apa yang terjadi di balik layar saat basis data dibuat?

Di bagian ini, kita akan melihat hubungan yang ada antara apa yang kita lakukan di tingkat SQL dan apa yang terjadi secara fisik di sistem file; perhatikan bahwa nomor oid yang kita lihat di bawah ini terkait dengan image Docker yang dibuat. Nilai numerik dari image Docker Anda mungkin berbeda.

forumdb=> select * from pg_database where datname='forumdb';
-[ RECORD 1 ]--+-----------
oid            | 16386
datname        | forumdb
datdba         | 16385
encoding       | 6
datlocprovider | c
datistemplate  | f
datallowconn   | t
datconnlimit   | -1
datfrozenxid   | 717
datminmxid     | 1
dattablespace  | 1663
datcollate     | en_US.utf8
datctype       | en_US.utf8
daticulocale   |
datcollversion | 2.31
datacl         |

Untuk memahami ini, kita perlu memperkenalkan tabel sistem pg_database:

1. Kembali ke mode ekspansi dan jalankan berikut:

Kueri ini memberikan semua informasi tentang basis data forumdb. Kolom pertama adalah Object Identifier (OID), yang merupakan nomor yang secara unik mengidentifikasi basis data yang disebut forumdb.

1. Keluar dari lingkungan psql dan pergi ke direktori $PGDATA (seperti yang ditunjukkan di bab-bab sebelumnya). Dalam lingkungan Linux Debian, kita harus menjalankan berikut:

cd /var/lib/postgresql/16/main/

Untuk image Docker, jalurnya adalah sebagai berikut:

cd /postgres/16/data

Jika kita tidak tahu apa nilai $PGDATA, kita bisa menjalankan berikut sebagai superuser:

forumdb=# show data_directory;
data_directory
-------------------
/postgres/16/data
(1 row)

1. Gunakan perintah ls untuk melihat apa yang ada di dalam direktori main atau data (image Docker):

postgres@learn_postgresql:~/data$ ls -l
total 128
drwx------ 8 postgres postgres 4096 Jan 3 09:49 base
drwx------ 2 postgres postgres 4096 Jan 3 09:49 global
[...]

1. Seperti yang Anda lihat, direktori pertama disebut base. Ini berisi semua basis data yang ada di klaster. Masuk ke direktori base untuk melihat isinya:

postgres@learn_postgresql:~/data$ cd base
postgres@learn_postgresql:~/data/base$

1. Daftar semua file yang ada di direktori:

postgres@learn_postgresql:~/data/base$ ls -l
total 40
drwx------ 2 postgres postgres 4096 Jan 3 09:45 1
drwx------ 2 postgres postgres 12288 Jan 3 09:14 16386
[....]

Seperti yang Anda lihat, ada direktori bernama 16386; namanya sama persis dengan OID di katalog pg_database.

Catatan: Ketika PostgreSQL membuat basis data baru, ia menyalin direktori yang terkait dengan basis data template1dan kemudian memberinya nama baru. Di PostgreSQL, basis data adalah direktori.

Di bagian ini, kita telah mempelajari cara mengelola basis data. Di bagian berikut, kita akan mempelajari cara mengelola tabel.

Mengelola Tabel

Di bagian ini, kita akan mempelajari cara mengelola tabel di basis data.

PostgreSQL memiliki tiga jenis tabel:

• Tabel sementara: Tabel yang sangat cepat, hanya terlihat oleh pengguna yang membuatnya.

• Tabel tidak tercatat (unlogged): Tabel yang sangat cepat untuk digunakan sebagai tabel pendukung yang umum untuk semua pengguna.

• Tabel tercatat (logged): Tabel reguler.

Kita sekarang akan menggunakan langkah-langkah berikut untuk membuat tabel pengguna dari awal:

1. Mari kita sambungkan ke forumdb sebagai pengguna forum:

postgres@learn_postgresql:~$ psql -U forum forumdb
forumdb=>

1. Jalankan perintah berikut:

forumdb=> CREATE TABLE myusers (
pk int GENERATED ALWAYS AS IDENTITY
, username text NOT NULL
, gecos text
, email text NOT NULL
, PRIMARY KEY( pk )
, UNIQUE ( username )
);
CREATE TABLE

1. Perintah CREATE TABLE membuat tabel baru. Perintah GENERATED AS IDENTITY secara otomatis menetapkan nilai unik ke kolom.

Amati apa yang dibuat di basis data menggunakan perintah \d:

forumdb=> \d myusers
Table "forum.myusers"
Column   | Type    | Collation | Nullable | Default
----------+---------+-----------+----------+-----------------------
pk       | integer |           | not null | generated always as identity
username | text    |           | not null |
gecos    | text    |           |          |
email    | text    |           | not null |
Indexes:
"myusers_pkey" PRIMARY KEY, btree (pk)
"myusers_username_key" UNIQUE CONSTRAINT, btree (username)

Catatan: Perhatikan bahwa PostgreSQL telah membuat indeks unik. Nanti dalam buku ini, kita akan menganalisis indeks secara lebih rinci dan membahas apa itu indeks, jenis indeks yang ada, dan cara menggunakannya. Untuk saat ini, kita cukup mengatakan bahwa indeks unik adalah indeks yang tidak mengizinkan penyisipan nilai duplikat untuk kolom tempat indeks dibuat. Di PostgreSQL, kunci primer diimplementasikan menggunakan indeks unik.

1. Gunakan perintah berikut untuk menghapus tabel:

forumdb=> drop table myusers;
DROP TABLE

Perintah di atas cukup menghapus tabel users. Perintah CREATE TABLE yang telah kita lihat sebelumnya, memiliki beberapa opsi berguna:

• IF NOT EXISTS

• TEMP

• UNLOGGED

Kita akan membahas masing-masing di subbagian berikut.

Opsi EXISTS

Opsi EXISTS dapat digunakan bersama dengan perintah pembuatan atau penghapusan entitas untuk memeriksa apakah objek sudah ada. Contoh penggunaannya dapat digabungkan dengan perintah CREATE TABLE atau CREATE DATABASE. Kita juga dapat menggunakan opsi ini saat kita membuat atau menghapus urutan, indeks, peran, dan skema.

Kasus penggunaannya sangat sederhana – perintah pembuatan atau penghapusan dijalankan jika klausa EXISTS benar; misalnya, jika kita ingin membuat tabel bernama users, jika tabel sudah ada, kita harus menjalankan pernyataan SQL ini:

forumdb=> create table if not exists users (
pk int GENERATED ALWAYS AS IDENTITY
,username text NOT NULL
,gecos text
,email text NOT NULL
,PRIMARY KEY( pk )
,UNIQUE ( username )
);
NOTICE: relation "users" already exists, skipping
CREATE TABLE

Perintah yang dijelaskan di atas hanya akan membuat tabel users jika belum ada; jika tidak, perintah akan dilewati. Perintah DROP bekerja dengan cara yang sama; perintah DROP TABLE digunakan untuk menghapus tabel; misalnya, jika kita ingin menghapus tabel myusers jika ada, kita harus menjalankan berikut:

forumdb=> drop table if exists myusers;
NOTICE: table "myusers" does not exist, skipping
DROP TABLE

Anda dapat melihat bahwa perintah dilewati karena tabel tidak ada. Opsi ini bisa berguna karena jika tabel tidak ada, PostgreSQL tidak memblokir instruksi lain yang berikutnya.

Mengelola Tabel Sementara

Nanti dalam buku ini, kita akan menjelajahi sesi, transaksi, dan konkurensi secara lebih mendalam. Untuk saat ini, Anda hanya perlu tahu bahwa sesi adalah sekumpulan transaksi, setiap sesi diisolasi, dan bahwa transaksi diisolasi dari segala sesuatu yang lain. Dengan kata lain, apa pun yang terjadi di dalam transaksi tidak dapat dilihat dari luar transaksi sampai transaksi selesai. Karena ini, kita mungkin perlu membuat struktur data yang hanya terlihat dalam transaksi yang sedang berjalan. Untuk melakukan ini, kita harus menggunakan opsi temp.

Kita sekarang akan menjelajahi dua kemungkinan. Kemungkinan pertama adalah kita bisa memiliki tabel yang hanya terlihat dalam sesi tempat tabel itu dibuat. Yang kedua adalah kita mungkin memiliki tabel yang terlihat dalam transaksi yang sama tempat tabel itu dibuat.

Berikut adalah contoh dari kemungkinan pertama di mana ada tabel yang terlihat dalam sesi:

forumdb=> create temp table if not exists temp_users (
pk int GENERATED ALWAYS AS IDENTITY
,username text NOT NULL
,gecos text
,email text NOT NULL
,PRIMARY KEY( pk )
,UNIQUE ( username )
);
CREATE TABLE

Perintah di atas akan membuat tabel temp_users, yang hanya akan terlihat dalam sesi tempat tabel dibuat.

Jika sebaliknya kita ingin memiliki tabel yang hanya terlihat dalam transaksi kita, maka kita harus menambahkan opsi on commit drop. Untuk melakukan ini, kita harus melakukan berikut:

1. Mulai transaksi baru.

2. Buat tabel temp_users.

3. Komit atau kembalikan transaksi yang dimulai di Langkah 1.

Mari kita mulai dengan Langkah 1:

1. Mulai transaksi dengan kode berikut:

forumdb=> begin work;
BEGIN
forumdb=*>

1. Simbol * berarti bahwa kita berada di dalam blok transaksi. Buat tabel yang hanya terlihat di dalam transaksi:

forumdb=*> create temp table if not exists temp_users_transaction (
pk int GENERATED ALWAYS AS IDENTITY
,username text NOT NULL
,gecos text
,email text NOT NULL
,PRIMARY KEY( pk )
,UNIQUE ( username )
) on commit drop;
CREATE TABLE

Sekarang periksa bahwa tabel ada di dalam transaksi dan tidak ada di luar transaksi:

forumdb=*> \d temp_users_transaction
Table "pg_temp_3.temp_users_transaction"
Column   | Type    | Collation | Nullable | Default
----------+---------+-----------+----------+-----------------------
pk       | integer |           | not null | generated always as identity
username | text    |           | not null |
gecos    | text    |           |          |
email    | text    |           | not null |
Indexes:
"temp_users_transaction_pkey" PRIMARY KEY, btree (pk)
"temp_users_transaction_username_key" UNIQUE CONSTRAINT, btree (username)

1. Anda dapat melihat struktur tabel temp_users_transaction, jadi sekarang komit transaksi:

forumdb=*> commit work;
COMMIT

Jika Anda menjalankan kembali perintah DESCRIBE \d temp_users_transaction, PostgreSQL merespons dengan cara ini:

forumdb=> \d temp_users_transaction
Did not find any relation named "temp_users_transaction".

Ini terjadi karena opsi on commit drop menghapus tabel setelah transaksi selesai.

Mengelola Tabel Tidak Tercatat

Kita sekarang akan membahas topik tabel tidak tercatat. Untuk saat ini, kita hanya akan mencatat bahwa tabel tidak tercatat jauh lebih cepat daripada tabel klasik (juga dikenal sebagai tabel tercatat) tetapi tidak aman terhadap kerusakan. Ini berarti bahwa konsistensi data tidak dijamin jika terjadi kerusakan.

Potongan kode berikut menunjukkan cara membuat tabel tidak tercatat:

forumdb=> create unlogged table if not exists unlogged_users (
pk int GENERATED ALWAYS AS IDENTITY
,username text NOT NULL
,gecos text
,email text NOT NULL
,PRIMARY KEY( pk )
,UNIQUE ( username )
);
CREATE TABLE

Catatan: Tabel tidak tercatat adalah alternatif cepat untuk tabel permanen dan sementara. Peningkatan kinerja ini datang dengan biaya kehilangan data jika server mengalami kerusakan. Jika server mengalami kerusakan setelah reboot, tabel akan kosong. Ini adalah sesuatu yang mungkin bisa Anda tanggung dalam keadaan tertentu.

Membuat Tabel

Kita sekarang akan menjelajahi apa yang terjadi di balik layar saat tabel baru dibuat. Juga, untuk tabel, PostgreSQL menetapkan pengenal objek yang disebut OID. Kita telah melihat oid2name di Bab 2, Mengenal Klaster Anda. Sekarang kita akan melihat sesuatu yang serupa. OID hanyalah nomor yang secara internal mengidentifikasi objek di dalam klaster PostgreSQL. Mari kita lihat hubungan antara tabel yang dibuat di tingkat SQL dan apa yang terjadi di balik layar di sistem file:

1. Untuk melakukan ini, kita akan menggunakan OID dan tabel sistem yang disebut pg_class, yang mengumpulkan informasi tentang semua tabel yang ada di basis data. Jadi, mari kita jalankan kueri ini:

forumdb=> select oid,relname from pg_class where relname='users';
oid   | relname
-------+---------
16389 | users
(1 row)

1. Di sini, kolom oid adalah kolom pengenal objek, dan relname mewakili nama relasi dari objek. Seperti yang terlihat di sini, basis data forumdb disimpan di direktori 16389.

Sekarang, mari kita lihat di mana tabel users disimpan. Untuk melakukan ini, pergi ke direktori 16386 menggunakan kode berikut:

postgres@learn_postgresql:~$ cd /var/lib/postgresql/16/main/base/16386

Atau jika Anda menggunakan image Docker, jalankan:

postgres@learn_postgresql:~$ cd /postgres/16/data/base/16386

1. Setelah di sini, jalankan perintah berikut:

postgres@learn_postgresql:~/data/base/16386$ ls -l | grep 16389
-rw------- 1 postgres postgres 0 Jan 3 09:13 16389

Seperti yang Anda lihat, di direktori 16386, ada file bernama 16389. Di PostgreSQL, setiap tabel disimpan dalam satu atau lebih file. Jika ukuran tabel kurang dari 1 GB, maka tabel akan disimpan dalam satu file. Jika tabel memiliki ukuran lebih besar dari 1 GB, maka tabel akan disimpan dalam dua file dan file kedua akan disebut 16389.1. Jika tabel users memiliki ukuran lebih besar dari 2 GB, maka tabel akan disimpan dalam tiga file, yang disebut 16389, 16389.1, dan 16389.2; hal yang sama berlaku untuk indeks users_username_key.

Catatan: Di PostgreSQL, setiap tabel atau indeks disimpan dalam satu atau lebih file. Ketika tabel atau indeks melebihi 1 GB, itu dibagi menjadi segmen berukuran gigabyte.

Di bagian ini, kita telah mempelajari cara mengelola tabel, dan kita telah melihat apa yang terjadi secara internal. Di bagian berikut, kita akan mempelajari cara memanipulasi data di dalam tabel.

Memahami Pernyataan Manipulasi Tabel Dasar

Sekarang setelah Anda mempelajari cara membuat tabel, Anda perlu memahami cara menyisipkan, melihat, memodifikasi, dan menghapus data di tabel. Ini akan membantu Anda memperbarui entri yang salah atau memperbarui entri yang ada, sesuai kebutuhan. Ada berbagai perintah yang dapat digunakan untuk ini, yang akan kita lihat sekarang.

Menyisipkan dan Memilih Data

Di bagian ini, kita akan belajar cara menyisipkan data ke dalam tabel. Untuk menyisipkan data ke dalam tabel, Anda perlu menggunakan perintah INSERT. Perintah INSERT menyisipkan baris baru ke dalam tabel.

Dimungkinkan untuk menyisipkan satu atau lebih baris yang ditentukan oleh ekspresi nilai, atau nol atau lebih baris yang dihasilkan dari kueri. Kita sekarang akan melalui beberapa kasus penggunaan sebagai berikut:

1. Untuk menyisipkan pengguna baru di tabel users, jalankan perintah berikut:

forumdb=> insert into users (username,gecos,email) values ('myusername','mygecos','myemail');
INSERT 0 1

1. Hasil ini menunjukkan bahwa PostgreSQL telah menyisipkan satu catatan ke dalam tabel users. Angka pertama adalah OID dari baris yang telah disisipkan; versi PostgreSQL yang lebih baru secara default memiliki tabel yang dibuat tanpa OID pada baris, jadi Anda hanya mendapatkan 0 yang dikembalikan.

Sekarang, jika kita ingin melihat catatan yang baru saja kita masukkan ke tabel users, kita harus melakukan perintah select:

forumdb=> select * from users;
pk | username    | gecos    | email
----+-------------+----------+---------
1  | myusername  | mygecos  | myemail
(1 row)

Perintah select dijalankan untuk mengambil baris dari tabel. Dengan pernyataan SQL ini, PostgreSQL mengembalikan semua data yang ada di semua kolom tabel. Nilai * menentukan semua kolom yang ada. Ini juga dapat diungkapkan sebagai berikut:

forumdb=> select pk,username,gecos,email from users;
pk | username    | gecos    | email
----+-------------+----------+---------
1  | myusername  | mygecos  | myemail
(1 row)

1. Sekarang mari kita sisipkan pengguna lain ke tabel users; misalnya, sisipkan pengguna 'scotty' dengan semua kolomnya sendiri:

forumdb=> insert into users (username,gecos,email) values ('scotty','scotty_gecos','scotty_email');
INSERT 0 1

1. Jika kita ingin melakukan pencarian yang sama seperti sebelumnya, mengurutkan data berdasarkan kolom username, kita harus menjalankan berikut:

forumdb=> select pk,username,gecos,email from users order by username;
pk | username    | gecos        | email
----+-------------+--------------+--------------
1  | myusername  | mygecos      | myemail
2  | scotty      | scotty_gecos | scotty_email
(2 rows)

Catatan: Bahasa SQL, tanpa opsi ORDER BY, tidak mengembalikan data secara terurut.

Di PostgreSQL, ini juga bisa ditulis sebagai berikut:

forumdb=> select pk,username,gecos,email from users order by 2;
pk | username    | gecos        | email
----+-------------+--------------+--------------
1  | myusername  | mygecos      | myemail
2  | scotty      | scotty_gecos | scotty_email
(2 rows)

PostgreSQL juga menerima posisi kolom pada kueri sebagai opsi pengurutan.

1. Sekarang mari kita lihat cara menyisipkan beberapa catatan menggunakan pernyataan satu baris. Misalnya, pernyataan berikut akan menyisipkan dua catatan di tabel categories:

forumdb=> insert into categories (title,description) values ('C Language', 'Languages'), ('Python Language','Languages');
INSERT 0 2

Ini adalah variasi kecil dari perintah INSERT. Tabel categories kita sekarang akan berisi nilai-nilai berikut:

forumdb=> select * from categories;
pk | title                  | description
----+------------------------+---------------------------------
1  | Database               | Database related discussions
2  | Unix                   | Unix and Linux discussions
3  | Programming Languages  | All about programming languages
4  | C Language             | Languages
5  | Python Language        | Languages
(5 rows)

1. Sekarang, jika kita ingin memilih hanya tupel di mana deskripsi sama dengan Database related discussions, gunakan kondisi where:

forumdb=> select * from categories where description ='Database related discussions';
pk | title    | description
----+----------+------------------------------
1  | Database | Database related discussions
(1 row)

1. Kondisi where memfilter pada satu atau lebih kolom tabel. Misalnya, jika kita ingin mencari semua topik dengan title sebagai orange dan description sebagai fruits, kita harus menulis berikut:

forumdb=> select * from categories where description = 'Languages' and title='C Language';
pk | title       | description
----+-------------+-------------
4  | C Language  | Languages
(1 row)

1. Sekarang jika, misalnya, kita ingin memilih semua tupel yang memiliki kolom description sama dengan Languages dan diurutkan berdasarkan title dalam urutan terbalik, jalankan berikut:

forumdb=> select * from categories where description ='Languages' order by title desc;
pk | title           | description
----+-----------------+-------------
5  | Python Language | Languages
4  | C Language      | Languages
(2 rows)

Atau kita juga bisa menulis ini:

forumdb=> select * from categories where description ='Languages' order by 2 desc;
pk | title           | description
----+-----------------+-------------
5  | Python Language | Languages
4  | C Language      | Languages
(2 rows)

Opsi ASC dan DESC mengurutkan kueri dalam urutan naik atau turun; jika tidak ada yang ditentukan, ASC adalah default.

Nilai NULL

Di bagian ini, kita akan berbicara tentang nilai NULL. Dalam bahasa SQL, nilai NULL didefinisikan sebagai berikut:

Null (atau NULL) adalah penanda khusus yang digunakan dalam Structured Query Language untuk menunjukkan bahwa nilai data tidak ada di basis data. Diperkenalkan oleh pencipta model basis data relasional, E. F. Codd, SQL NULL berfungsi untuk memenuhi persyaratan bahwa semua Sistem Manajemen Basis Data Relasional (RDBMS) yang sejati mendukung representasi informasi yang hilang.

Sekarang mari kita lihat bagaimana NULL digunakan di PostgreSQL:

1. Mari kita mulai dengan menyisipkan tupel dengan cara ini:

forumdb=> insert into categories (title) values ('A new discussion');
INSERT 0 1

1. Mari kita lihat sekarang tupel mana yang ada di tabel categories:

forumdb=> select * from categories;
pk | title                  | description
----+------------------------+---------------------------------
1  | Database               | Database related discussions
2  | Unix                   | Unix and Linux discussions
3  | Programming Languages  | All about programming languages
4  | C Language             | Languages
5  | Python Language        | Languages
6  | A new discussion       |
(6 rows)

1. Jadi sekarang, jika kita ingin memilih semua tupel di mana deskripsi tidak ada, kita gunakan berikut:

forumdb=> select * from categories where description ='';
pk | title | description
----+-------+-------------
(0 rows)

1. Seperti yang Anda lihat, PostgreSQL tidak mengembalikan tupel apa pun. Ini karena penyisipan terakhir telah memasukkan nilai NULL di kolom description.

Untuk melihat nilai NULL yang ada di tabel, mari kita jalankan perintah berikut:

forumdb=> \pset null NULL
Null display is "NULL".

1. Ini memberi tahu psql untuk menampilkan nilai NULL yang ada di tabel sebagai NULL, seperti yang ditunjukkan di sini:

forumdb=> select * from categories;
pk | title                  | description
----+-----------------------+----------------------------
1  | Database               | Database related discussions
2  | Unix                   | Unix and Linux discussions
3  | Programming Languages  | All about programming languages
4  | C Language             | Languages
5  | Python Language        | Languages
6  | A new discussion       | NULL
(6 rows)

1. Seperti yang Anda lihat, nilai description yang terkait dengan title A new discussion bukan string kosong; itu adalah nilai NULL.

Sekarang, jika kita ingin melihat semua catatan yang memiliki nilai NULL di kolom description, kita harus menggunakan operator IS NULL:

forumdb=> select title,description from categories where description is null;
title            | description
------------------+-------------
A new discussion | NULL
(1 row)

1. Kueri di atas mencari semua tupel yang tidak memiliki nilai di kolom description. Sekarang, kita akan mencari semua tupel yang memiliki nilai di kolom description menggunakan kueri berikut:

forumdb=> select title,description from categories where description is not null;
title                  | description
-----------------------+---------------------------------
Database               | Database related discussions
Unix                   | Unix and Linux discussions
Programming Languages  | All about programming languages
C Language             | Languages
Python Language        | Languages
(5 rows)

Catatan: Untuk melakukan pencarian pada kolom NULL, kita harus menggunakan operator IS NULL / IS NOT NULL. String kosong berbeda dari nilai NULL.

Mengurutkan dengan Nilai NULL

Sekarang mari kita lihat apa yang terjadi saat mengurutkan tabel di mana ada nilai NULL yang ada:

1. Mari kita ulangi kueri pengurutan yang kita lakukan sebelumnya:

forumdb=> select * from categories order by description;
pk | title                  | description
----+-----------------------+----------------------------
3  | Programming Languages  | All about programming languages
1  | Database               | Database related discussions
4  | C Language             | Languages
5  | Python Language        | Languages
2  | Unix                   | Unix and Linux discussions
6  | A new discussion       | NULL
(6 rows)

Seperti yang Anda lihat, semua nilai description diurutkan dan nilai NULL ditempatkan di akhir set hasil. Hal yang sama dapat dicapai dengan menjalankan berikut:

forumdb=> select * from categories order by description NULLS last;
pk | title                  | description
----+-----------------------+----------------------------
3  | Programming Languages  | All about programming languages
1  | Database               | Database related discussions
4  | C Language             | Languages
5  | Python Language        | Languages
2  | Unix                   | Unix and Linux discussions
6  | A new discussion       | NULL
(6 rows)

1. Jika kita ingin menempatkan nilai NULL di awal, kita harus melakukan berikut:

forumdb=> select * from categories order by description NULLS first;
pk | title                  | description
----+-----------------------+---------------------------------
6  | A new discussion       | NULL
3  | Programming Languages  | All about programming languages
1  | Database               | Database related discussions
4  | C Language             | Languages
5  | Python Language        | Languages
2  | Unix                   | Unix and Linux discussions
(6 rows)

Jika tidak ditentukan, berikut adalah tindakan default untuk kueri tipe ORDER BY:

• NULLS LAST adalah default untuk ASC (yang juga default) dan NULLS FIRST adalah default untuk DESC.

Membuat Tabel Mulai dari Tabel Lain

Kita sekarang akan memeriksa cara membuat tabel baru menggunakan data dari tabel lain. Untuk melakukan ini, Anda perlu membuat tabel sementara dengan data yang ada di tabel categories sebagai berikut:

forumdb=> create temp table temp_categories as select * from categories;
SELECT 6

Perintah ini membuat tabel bernama temp_categories dengan struktur data dan data yang sama seperti tabel yang disebut categories:

forumdb=> select * from temp_categories;
pk | title                  | description
----+-----------------------+----------------------------
1  | Database               | Database related discussions
2  | Unix                   | Unix and Linux discussions
3  | Programming Languages  | All about programming languages
4  | C Language             | Languages
5  | Python Language        | Languages
6  | A new discussion       | NULL
(6 rows)

Memperbarui Data

Sekarang mari kita coba memperbarui beberapa data:

1. Jika Anda ingin mengubah nilai Unix menjadi nilai Linux, Anda perlu menjalankan pernyataan berikut:

forumdb=> update temp_categories set title='Linux' where pk = 2;
UPDATE 1

Pernyataan ini akan mengubah nilai Unix menjadi Linux di kolom title untuk semua baris tabel temp_categories yang memiliki pk=2, seperti yang terlihat di sini:

forumdb=> select * from temp_categories where pk=2;
pk | title | description
----+-------+----------------------------
2  | Linux | Unix and Linux discussions
(1 row)

1. Jika Anda ingin mengubah nilai title dari semua baris yang memiliki nilai description adalah Languages, Anda perlu menjalankan pernyataan berikut:

forumdb=> update temp_categories set title = 'no title' where description = 'Languages';
UPDATE 2

UPDATE 2 berarti bahwa hanya dua baris yang telah dimodifikasi, seperti yang ditunjukkan di sini:

forumdb=> select * from temp_categories order by description;
pk | title                  | description
----+-----------------------+----------------------------
3  | Programming Languages  | All about programming languages
1  | Database               | Database related discussions
4  | no title              | Languages
5  | no title              | Languages
2  | Linux                 | Unix and Linux discussions
6  | A new discussion       | NULL
(6 rows)

Peringatan: Anda harus berhati-hati saat menggunakan perintah UPDATE. Jika Anda bekerja dalam mode auto-commit, tidak ada kesempatan untuk kembali setelah pembaruan selesai. Auto-commit adalah default di psql.

Menghapus Data

Di bagian ini, kita akan melihat cara menghapus data dari tabel. Perintah yang diperlukan untuk menghapus data adalah delete. Mari kita mulai:

1. Jika kita ingin menghapus semua catatan di tabel temp_categories yang memiliki pk=5, kita harus melakukan perintah berikut:

forumdb=> delete from temp_categories where pk=5;
DELETE 1

Pernyataan di atas menghapus semua catatan yang memiliki pk=5. DELETE 1 berarti bahwa satu catatan telah dihapus. Seperti yang Anda lihat di sini, baris dengan nilai pk=5 tidak lagi ada di temp_categories:

forumdb=> select * from temp_categories where pk=5;
pk | title | description
----+-------+-------------
(0 rows)

1. Sekarang, jika kita ingin menghapus semua baris yang memiliki nilai description sama dengan NULL, kita harus menjalankan pernyataan ini:

forumdb=> delete from temp_categories where description is null;
DELETE 1

1. Pernyataan di atas menggunakan perintah DELETE yang dikombinasikan dengan operator IS NULL.

2. Jika Anda ingin menghapus semua catatan dari tabel, Anda harus menjalankan berikut:

forumdb=> delete from temp_categories;
DELETE 4

Peringatan: Berhati-hatilah saat menggunakan perintah ini – semua catatan yang ada di tabel akan dihapus!

Sekarang tabel temp_categories kosong, seperti yang ditunjukkan di sini:

forumdb=> select * from temp_categories;
pk | title | description
----+-------+-------------
(0 rows)

1. Jika kita ingin memuat ulang semua data dari tabel categories ke tabel temp_categories, kita harus menjalankan pernyataan ini:

forumdb=> insert into temp_categories select * from categories;
INSERT 0 6

Pernyataan di atas mengambil semua nilai dari tabel categories dan memasukkannya ke tabel temp_categories, seperti yang Anda lihat di sini:

forumdb=> select * from temp_categories order by description;
pk | title                  | description
----+-----------------------+----------------------------
3  | Programming Languages  | All about programming languages
1  | Database               | Database related discussions
4  | C Language             | Languages
5  | Python Language        | Languages
2  | Unix                   | Unix and Linux discussions
6  | A new discussion       | NULL
(6 rows)

Cara lain untuk menghapus data adalah dengan menggunakan perintah TRUNCATE. Ketika kita ingin menghapus semua data dari tabel tanpa memberikan kondisi where, kita dapat menggunakan perintah TRUNCATE:

forumdb=> truncate table temp_categories;
TRUNCATE TABLE

Perintah TRUNCATE menghapus semua data di tabel. Seperti yang Anda lihat di sini, tabel temp_categories sekarang kosong:

forumdb=> select * from temp_categories;
pk | title | description
----+-------+-------------
(0 rows)

Berikut adalah beberapa informasi kunci tentang perintah TRUNCATE:

• TRUNCATE menghapus semua catatan di tabel mirip dengan perintah DELETE.

• Dalam perintah TRUNCATE, tidak mungkin menggunakan kondisi where.

• Perintah TRUNCATE menghapus catatan jauh lebih cepat daripada perintah DELETE.

Ringkasan

Bab ini memperkenalkan Anda pada pernyataan SQL/PostgreSQL dasar dan beberapa perintah SQL dasar. Anda telah mempelajari cara membuat dan menghapus basis data, cara membuat dan menghapus tabel, jenis tabel yang ada, pernyataan dasar mana yang digunakan untuk menyisipkan, memodifikasi, dan menghapus data, dan kueri dasar pertama dari banyak kueri yang dapat Anda gunakan untuk mengkueri basis data.

Di bab berikutnya, Anda akan belajar cara menulis kueri yang lebih kompleks yang berhubungan dengan beberapa tabel dengan cara yang berbeda.