PostgreSQL → Advanced Statements

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

Pernyataan Lanjutan

Pada bab sebelumnya, kita telah memulai langkah pertama dengan PostgreSQL. Dalam bab ini, kita akan menganalisis bahasa SQL secara lebih mendalam dan menulis kueri yang lebih kompleks. Kita akan kembali membahas pernyataan SELECT, INSERT, dan UPDATE, tetapi kali ini dengan opsi yang lebih canggih. Selanjutnya, kita akan membahas join, Common Table Expressions (CTE), dan MERGE secara mendalam.

Berikut adalah topik yang akan kita pelajari dalam bab ini:

• Mengeksplorasi pernyataan SELECT

• Menggunakan UPSERT dan MERGE

• Mengeksplorasi CTE

Persyaratan Teknis

Sebelum memulai, pastikan untuk menjalankan container Docker bernama chapter_05 dengan perintah berikut:

$ bash run-pg-docker.sh chapter_05
postgres@learn_postgresql:~$ psql -U forum forumdb

Mengeksplorasi Pernyataan SELECT

Seperti yang telah kita pelajari pada bab sebelumnya, pernyataan SELECT dapat digunakan untuk memfilter dataset menggunakan kondisi kesamaan. Selain itu, kita juga dapat memfilter data menggunakan kondisi > atau <, seperti pada contoh berikut:

forumdb=> select * from categories where pk > 2;
pk | title                 | description
----+----------------------+---------------------------------
3  | Programming Languages | All about programming languages
(1 row)

Kueri di atas mengembalikan semua catatan dengan nilai pk > 2. Selain itu, kita juga dapat menggunakan kondisi LIKEuntuk memfilter data. Mari kita lihat lebih lanjut.

Menggunakan Klausa LIKE

Misalkan kita ingin mencari semua catatan yang memiliki nilai kolom title diawali dengan string Prog. Untuk melakukannya, kita dapat menggunakan kondisi LIKE:

forumdb=> \x
Expanded display is on.
forumdb=> select * from categories where title like 'Prog%';
-[ RECORD 1 ]--------------------------------
pk          | 3
title       | Programming Languages
description | All about programming languages

Kueri di atas mengembalikan semua catatan dengan nilai title yang diawali dengan Prog. Jika kita ingin mencari catatan dengan title yang diakhiri dengan Languages, kita dapat menulis:

forumdb=> select * from categories where title like '%Languages';
-[ RECORD 1 ]--------------------------------
pk          | 3
title       | Programming Languages
description | All about programming languages

Kita juga dapat menggabungkan kedua jenis pencarian tersebut. Misalnya, untuk mencari catatan yang mengandung substring discuss pada kolom description:

forumdb=> \x
Expanded display is off
forumdb=> select * from categories where description like '%discuss%';
pk | title    | description
----|----------|------------------------------
1  | Database | Database related discussions
2  | Unix     | Unix and Linux discussions
(2 rows)

Sekarang, coba jalankan kueri berikut:

forumdb=> select * from categories where title like 'prog%';
(0 rows)

Kueri ini tidak mengembalikan hasil karena pencarian dengan LIKE bersifat case-sensitive. Untuk mengatasi ini, kita dapat menggunakan fungsi upper(text), yang mengubah semua karakter dalam string menjadi huruf besar:

forumdb=> select upper('prog');
upper
-------
PROG
(1 row)

Di PostgreSQL, kita dapat memanggil fungsi tanpa menulis FROM, tidak seperti di Oracle yang memerlukan tabel dummy seperti DUAL. Untuk melakukan pencarian LIKE yang tidak peka huruf besar-kecil, kita dapat menulis:

forumdb=> select * from categories where upper(description) like '%DISCUSS%';
pk | title    | description
----|----------|------------------------------
1  | Database | Database related discussions
2  | Unix     | Unix and Linux discussions
(2 rows)

Menggunakan ILIKE

PostgreSQL menyediakan operator ILIKE untuk melakukan pencarian LIKE yang tidak peka huruf besar-kecil:

forumdb=> select * from categories where description ilike '%DISCUSS%';
pk | title    | description
----|----------|------------------------------
1  | Database | Database related discussions
2  | Unix     | Unix and Linux discussions
(2 rows)

Ini adalah cara PostgreSQL untuk menangani pencarian LIKE yang tidak peka huruf besar-kecil.

Menggunakan DISTINCT

Sekarang kita akan membahas kueri DISTINCT. Pertama, mari kita kenalkan fungsi coalesce, yang sangat berguna bagi DBA. Fungsi coalesce mengembalikan nilai pertama yang bukan NULL dari dua atau lebih parameter:

forumdb=> select coalesce(NULL,'test');
coalesce
----------
test
(1 row)

Sekarang, mari kita sisipkan kategori baru:

forumdb=> insert into categories (title) values ('New Category');
INSERT 0 1

Kemudian, atur tampilan NULL:

forumdb=# \pset null (NULL)
Null display is "(NULL)".

Lihat data dalam tabel categories:

forumdb=> select pk,title,description from categories;
pk | title                 | description
----|-----------------------|----------------------------
1  | Database              | Database related discussions
2  | Unix                  | Unix and Linux discussions
3  | Programming Languages | All about programming languages
4  | New Category          | (NULL)
(4 rows)

Untuk mengganti nilai NULL pada kolom description dengan No description, kita dapat menggunakan coalesce:

forumdb=> select pk,title,coalesce(description,'No description') from categories;
pk | title                 | coalesce
----|-----------------------|----------------------------
1  | Database              | Database related discussions
2  | Unix                  | Unix and Linux discussions
3  | Programming Languages | All about programming languages
4  | New Category          | No description
(4 rows)

Namun, nama kolom hasil dari coalesce menjadi coalesce, yang kurang ramah pengguna. Kita dapat menetapkan alias untuk kolom tersebut:

forumdb=> select pk,title,coalesce(description,'No description') as description from categories;
pk | title                 | description
----|-----------------------|---------------------------------
1  | Database              | Database related discussions
2  | Unix                  | Unix and Linux discussions
3  | Programming Languages | All about programming languages
4  | New Category          | No description
(4 rows)

Jika alias mengandung spasi atau huruf besar, gunakan tanda kutip:

forumdb=> select pk,title,coalesce(description,'No description') as "Description" from categories;
pk | title                 | Description
----|-----------------------|---------------------------------
1  | Database              | Database related discussions
2  | Unix                  | Unix and Linux discussions
3  | Programming Languages | All about programming languages
4  | New Category          | No description
(4 rows)

Sekarang, mari kita sisipkan catatan lain:

forumdb=> insert into categories (title,description) values ('Database','PostgreSQL');
INSERT 0 1

Lihat data yang diurutkan berdasarkan title:

forumdb=> select title from categories order by title;
title
-----------------------
Database
Database
New Category
Programming Languages
Unix
(5 rows)

Terdapat dua catatan dengan nilai Database. Untuk menampilkan hanya nilai unik, gunakan klausa DISTINCT:

forumdb=> select distinct title from categories order by title;
title
-----------------------
Database
New Category
Programming Languages
Unix
(4 rows)

Klausa DISTINCT mengembalikan hanya nilai yang berbeda. Namun, untuk tabel besar, DISTINCT dapat memperlambat kueri karena melibatkan pengurutan data.

Menggunakan LIMIT dan OFFSET

Klausa LIMIT digunakan untuk membatasi jumlah baris yang dikembalikan, sedangkan OFFSET digunakan untuk melewati sejumlah baris tertentu. Keduanya dapat digunakan secara independen atau bersama-sama untuk mengembalikan sebagian data dari hasil kueri.

Contoh penggunaan LIMIT:

forumdb=> select * from categories order by pk limit 1;
pk | title    | description
----|----------|------------------------------
1  | Database | Database related discussions
(1 row)

Kueri di atas mengembalikan hanya catatan pertama. Untuk dua catatan pertama:

forumdb=> select * from categories order by pk limit 2;
pk | title    | description
----|----------|------------------------------
1  | Database | Database related discussions
2  | Unix     | Unix and Linux discussions
(2 rows)

Untuk mengambil catatan kedua saja:

forumdb=> select * from categories order by pk offset 1 limit 1;
pk | title | description
----|-------|----------------------------
2  | Unix  | Unix and Linux discussions
(1 row)

LIMIT dan OFFSET sangat berguna untuk pengambilan data secara berhalaman. Selain itu, LIMIT dapat digunakan untuk membuat tabel baru dari struktur tabel yang ada:

forumdb=> create table new_categories as select * from categories limit 0;
SELECT 0

Kueri ini menyalin hanya struktur data dari tabel categories ke new_categories:

forumdb=> \d new_categories
Table "forum.new_categories"
Column      | Type   | Collation | Nullable | Default
-------------|--------|-----------|----------|---------
pk          | integer|           |          |
title       | text   |           |          |
description | text   |           |          |

Menggunakan Subkueri

Subkueri adalah kueri yang bersarang di dalam kueri lain menggunakan tanda kurung. Subkueri dapat mengembalikan nilai tunggal atau kumpulan catatan, seperti kueri biasa. Mari kita mulai dengan subkueri menggunakan operator IN/NOT IN.

Subkueri dengan Kondisi IN/NOT IN

Operator IN dapat digunakan di dalam klausa WHERE sebagai pengganti beberapa kondisi OR. Misalnya, untuk mencari kategori dengan pk=1 atau pk=2:

forumdb=> select * from categories where pk=1 or pk=2;
pk | title    | description
----|----------|------------------------------
1  | Database | Database related discussions
2  | Unix     | Unix and Linux discussions
(2 rows)

Alternatifnya, kita dapat menggunakan IN:

forumdb=> select * from categories where pk in (1,2);
pk | title    | description
----|----------|------------------------------
1  | Database | Database related discussions
2  | Unix     | Unix and Linux discussions
(2 rows)

Operator NOT IN berfungsi sebaliknya, mencari catatan yang tidak memiliki nilai tertentu:

forumdb=> select * from categories where pk not in (1,2);
pk | title                 | description
----|-----------------------|----------------------------
3  | Programming Languages | All about programming languages
4  | New Category          | (NULL)
5  | Database              | PostgreSQL
(3 rows)

Sekarang, mari kita sisipkan data ke tabel users dan posts:

forumdb=> insert into users (username,email) values ('luca_ferrari','[e-mail]'),('enrico_pirozzi','[e-mail]');
INSERT 0 2
forumdb=> insert into posts (title,content,author,category) values ('Indexing PostgreSQL','Btree in PostgreSQL is....',1,1);
INSERT 0 1
forumdb=> insert into posts (title,content,author,category) values ('Indexing Mysql','Btree in Mysql is....',1,1);
INSERT 0 1
forumdb=> insert into posts (title,content,author,category) values ('Data types in C++','Data type in C++ are ..' ,2,3);
INSERT 0 1

Lihat isi tabel posts:

forumdb=> \x
Expanded display is on.
forumdb=> select pk,title,content,author,category from posts;
-[ RECORD 1 ]------------------------
pk      | 1
title   | Indexing PostgreSQL
content | Btree in PostgreSQL is....
author  | 1
category| 1
-[ RECORD 2 ]------------------------
pk      | 2
title   | Indexing Mysql
content | Btree in Mysql is....
author  | 1
category| 1
-[ RECORD 3 ]------------------------
pk      | 3
title   | Data types in C++
content | Data type in C++ are ..
author  | 2
category| 3

Untuk mencari semua posting yang termasuk dalam kategori Database, kita dapat menggunakan subkueri:

forumdb=> select pk,title,content,author,category from posts where category in (select pk from categories where title ='Database');
-[ RECORD 1 ]------------------------
pk      | 1
title   | Indexing PostgreSQL
content | Btree in PostgreSQL is....
author  | 1
category| 1
-[ RECORD 2 ]------------------------
pk      | 2
title   | Indexing Mysql
content | Btree in Mysql is....
author  | 1
category| 1

Subkueri dalam kueri di atas adalah:

forumdb=> \x
Expanded display is off.
forumdb=> select pk from categories where title ='Database';
pk
----
1
5
(2 rows)

Subkueri ini mengambil nilai pk=1 dan pk=5 dari tabel categories, dan kueri utama mencari catatan di tabel posts yang memiliki category=1 atau category=5. Untuk mencari posting yang tidak termasuk dalam kategori Database:

forumdb=> \x
Expanded display is on.
forumdb=> select pk,title,content,author,category from posts where category not in (select pk from categories where title ='Database');
-[ RECORD 1 ]---------------------
pk      | 3
title   | Data types in C++
content | Data type in C++ are ..
author  | 2
category| 3

Subkueri dengan Kondisi EXISTS/NOT EXISTS

Pernyataan EXISTS digunakan untuk memeriksa apakah subkueri mengembalikan hasil (TRUE), sedangkan NOT EXISTS memeriksa apakah subkueri tidak mengembalikan hasil (FALSE). Misalnya, untuk menulis ulang kueri sebelumnya menggunakan EXISTS:

forumdb=> select pk,title,content,author,category from posts where exists (select 1 from categories where title ='Database' and posts.category=pk);
-[ RECORD 1 ]------------------------
pk      | 1
title   | Indexing PostgreSQL
content | Btree in PostgreSQL is....
author  | 1
category| 1
-[ RECORD 2 ]------------------------
pk      | 2
title   | Indexing Mysql
content | Btree in Mysql is....
author  | 1
category| 1

Untuk mencari posting yang tidak termasuk dalam kategori Database menggunakan NOT EXISTS:

forumdb=> select pk,title,content,author,category from posts where not exists (select 1 from categories where title ='Database' and posts.category=pk);
-[ RECORD 1 ]------------------
pk      | 3
title   | Data types in C++
content | Data type in C++ are ..
author  | 2
category| 3

Kueri dengan IN dan EXISTS disebut kueri semi-join, dan kita akan membahas lebih lanjut tentang join di bagian berikutnya.

Mempelajari Join

Join adalah kombinasi baris dari dua atau lebih tabel. Misalnya, kueri berikut mengembalikan semua kombinasi baris dari tabel categories dan posts:

forumdb=> select c.pk,c.title,p.pk,p.category,p.title from categories c,posts p;
pk | title                 | pk | category | title
----|-----------------------|----|----------|------------
1  | Database              | 1  | 1        | Indexing PostgreSQL
2  | Unix                  | 1  | 1        | Indexing PostgreSQL
3  | Programming Languages | 1  | 1        | Indexing PostgreSQL
4  | New Category          | 1  | 1        | Indexing PostgreSQL
5  | Database              | 1  | 1        | Indexing PostgreSQL
1  | Database              | 2  | 1        | Indexing Mysql
...
(15 rows)

Kueri ini menghasilkan produk Cartesian antara tabel categories dan posts, yang juga disebut CROSS JOIN. Kueri yang sama dapat ditulis secara eksplisit sebagai:

forumdb=> select c.pk,c.title,p.pk,p.category,p.title from categories c CROSS JOIN posts p;

Menggunakan INNER JOIN

Misalkan kita ingin memfilter baris yang memiliki nilai category yang sama (categories.pk = posts.category):

forumdb=> select c.pk,c.title,p.pk,p.category,p.title from categories c,posts p where c.pk=p.category;
pk | title                 | pk | category | title
----|-----------------------|----|----------|------------
1  | Database              | 1  | 1        | Indexing PostgreSQL
1  | Database              | 2  | 1        | Indexing Mysql
3  | Programming Languages | 3  | 3        | Data types in C++
(3 rows)

Kueri ini dapat ditulis menggunakan operasi INNER JOIN:

forumdb=> select c.pk,c.title,p.pk,p.category,p.title from categories c inner join posts p on c.pk=p.category;

INNER JOIN memilih catatan yang memiliki nilai yang cocok di kedua tabel. Untuk mencari posting dalam kategori Database menggunakan INNER JOIN:

forumdb=> \x
Expanded display is on.
forumdb=> select c.pk,c.title,p.pk,p.category,p.title from categories c inner join posts p on c.pk=p.category where c.title='Database';
-[ RECORD 1 ]-----------------
pk      | 1
title   | Database
pk      | 1
category| 1
title   | Indexing PostgreSQL
-[ RECORD 2 ]-----------------
pk      | 1
title   | Database
pk      | 2
category| 1
title   | Indexing Mysql

Kondisi INNER JOIN dapat digunakan untuk menulis ulang kueri yang menggunakan IN atau EXISTS, dan biasanya lebih cepat dalam hal kecepatan eksekusi.

Menggunakan LEFT JOIN

LEFT JOIN mengembalikan semua catatan dari tabel kiri (categories) dan catatan yang cocok dari tabel kanan (posts). Jika tidak ada kecocokan, hasilnya adalah NULL:

forumdb=> select c.*,p.category,p.title from categories c left join posts p on c.pk=p.category;
-[ RECORD 1 ]--------------------------------
pk          | 1
title       | Database
description | Database related discussions
category    | 1
title       | Indexing PostgreSQL
...
-[ RECORD 4 ]--------------------------------
pk          | 2
title       | Unix
description | Unix and Linux discussions
category    | (NULL)
title       | (NULL)
...

Untuk mencari kategori yang tidak memiliki posting:

forumdb=> \x
Expanded display is off.
forumdb=> select c.* from categories c left join posts p on p.category=c.pk where p.category is null;
pk | title        | description
----|--------------|----------------------------
2  | Unix         | Unix and Linux discussions
4  | New Category | (NULL)
5  | Database     | PostgreSQL
(3 rows)

Menggunakan RIGHT JOIN

RIGHT JOIN adalah kebalikan dari LEFT JOIN, mengembalikan semua catatan dari tabel kanan dan catatan yang cocok dari tabel kiri:

forumdb=> select c.*,p.category,p.title from posts p right join categories c on c.pk=p.category;

Hasilnya sama dengan LEFT JOIN jika tabel ditukar.

Menggunakan FULL OUTER JOIN

FULL OUTER JOIN menggabungkan hasil dari LEFT JOIN dan RIGHT JOIN:

forumdb=> create temp table new_posts as select * from posts;
SELECT 3
forumdb=> insert into new_posts (pk,title,content,author,category) values (6,'A new Book','A new book not present in categories....',1,NULL);
 Resolution: 0
INSERT 0 1
forumdb=> select c.pk,c.title,p.pk,p.title from categories c full outer join new_posts p on p.category=c.pk;
pk | title                 | pk | title
----|-----------------------|----|----------------
1  | Database              | 1  | Indexing PostgreSQL
1  | Database              | 2  | Indexing Mysql
3  | Programming Languages | 3  | Data types in C++
NULL| NULL                 | 6  | A new Book
2  | Unix                  | NULL| NULL
5  | Database              | NULL| NULL
4  | New Category          | NULL| NULL
(7 rows)

Menggunakan LATERAL JOIN

LATERAL JOIN memungkinkan subkueri dijalankan untuk setiap baris dari tabel utama. Misalnya, tambahkan kolom likes ke tabel posts:

forumdb=> alter table posts add likes integer default 0;
ALTER TABLE
forumdb=> update posts set likes = 3 where title like 'Indexing%';
UPDATE 2

Untuk mencari pengguna dengan posting yang memiliki likes > 2:

forumdb=> select u.* from users u where exists (select 1 from posts p where u.pk=p.author and likes > 2 );
pk | username      | gecos | email
----|---------------|-------|---------------------
1  | luca_ferrari  |       | [e-mail]
(1 row)

Dengan LATERAL JOIN, kita juga dapat mengambil nilai likes:

forumdb=> select u.username,q.* from users u join lateral (select author, title,likes from posts p where u.pk=p.author and likes > 2 ) as q on true;
username     | author | title               | likes
-------------|--------|---------------------|-------
luca_ferrari | 1      | Indexing PostgreSQL | 3
luca_ferrari | 1      | Indexing Mysql      | 3
(2 rows)

Fungsi Agregasi

Fungsi agregasi melakukan perhitungan pada sekumpulan baris dan mengembalikan satu baris. PostgreSQL menyediakan fungsi agregasi standar SQL:

• AVG(): Mengembalikan nilai rata-rata.

• COUNT(): Mengembalikan jumlah nilai.

• MAX(): Mengembalikan nilai maksimum.

• MIN(): Mengembalikan nilai minimum.

• SUM(): Mengembalikan jumlah nilai.

Fungsi agregasi digunakan bersama klausa GROUP BY, yang membagi hasil menjadi kelompok. Misalnya, untuk menghitung jumlah catatan per kategori:

forumdb=> select category,count(*) from posts group by category;
category | count
----------|-------
3        | 1
1        | 2
(2 rows)

Kita juga dapat menggunakan HAVING untuk memfilter kelompok:

forumdb=> select category,count(*) from posts group by category having count(*) > 1;
category | count
----------|-------
1        | 2
(1 row)

Alias dapat digunakan pada fungsi agregasi, tetapi tidak pada klausa HAVING:

forumdb=> select category,count(*) as category_count from posts group by category having count(*) > 1;
category | category_count
----------|----------------
1        | 2
(1 row)

UNION/UNION ALL

Operator UNION menggabungkan hasil dari dua atau lebih pernyataan SELECT dengan syarat:

• Jumlah kolom harus sama.

• Tipe data kolom harus serupa.

• Urutan kolom harus sama.

Contoh:

forumdb=> insert into tags (tag) values ('Database'),('Operating Systems');
INSERT 0 2
forumdb=> select tag as datalist from tags UNION select title as datalist from categories;
datalist
-----------------------
New Category
Operating Systems
Programming Languages
Database
Unix
(5 rows)

UNION ALL menyertakan duplikat:

forumdb=> select tag as datalist from tags UNION ALL select title as datalist from categories order by 1;
datalist
-----------------------
Database
Database
Database
New Category
Operating Systems
Programming Languages
Unix
(7 rows)

EXCEPT/INTERSECT

Operator EXCEPT mengembalikan baris dari kueri pertama yang tidak ada di kueri kedua:

forumdb=> select title as datalist from categories except select tag as datalist from tags order by 1;
datalist
-----------------------
New Category
Programming Languages
Unix
(3 rows)

Operator INTERSECT mengembalikan baris yang ada di kedua kueri:

forumdb=> select title as datalist from categories intersect select tag as datalist from tags order by 1;
datalist
----------
Database
(1 row)

Menggunakan UPSERT

UPSERT (Update or Insert) digunakan untuk menyisipkan catatan baru atau memperbarui catatan yang sudah ada. Di PostgreSQL, ini dilakukan dengan ON CONFLICT:

INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;

Contoh:

forumdb=> alter table j_posts_tags add constraint j_posts_tags_pkey primary key (tag_pk,post_pk);
ALTER TABLE
forumdb=> insert into j_posts_tags (post_pk ,tag_pk) values (3,2),(1,1),(2,1);
INSERT 0 3
forumdb=> insert into j_posts_tags (post_pk ,tag_pk) values (2,1) ON CONFLICT DO NOTHING;
INSERT 0 0

Menggunakan DO UPDATE:

forumdb=> insert into j_posts_tags (post_pk ,tag_pk) values (2,1) ON CONFLICT (tag_pk,post_pk) DO UPDATE set tag_pk=excluded.tag_pk+1;
INSERT 0 1

Klausa RETURNING untuk INSERT

Klausa RETURNING mengembalikan nilai kolom setelah INSERT:

forumdb=> insert into j_posts_tags (tag_pk,post_pk) values(1,3) returning *;
tag_pk | post_pk
--------|---------
1      | 3
(1 row)
INSERT 0 1

Memperbarui Catatan Terkait

Untuk memperbarui catatan yang terkait dengan beberapa tabel:

forumdb=> create temp table t_categories as select * from categories limit 0;
SELECT 0
forumdb=> insert into t_categories (pk,title,description) values (4,'Machine Learning','Machine Learning discussions'),(5,'Software engineering','Software engineering discussions');
INSERT 0 2
forumdb=> update categories c set title=t.title,description=t.description from t_categories t where c.pk=t.pk;
UPDATE 2

MERGE

Mulai dari PostgreSQL 15, MERGE dapat digunakan untuk memperbarui atau menyisipkan data:

forumdb=> create temp table new_data as select * from categories limit 0;
SELECT 0
forumdb=> insert into new_data (pk,title,description) values (1,'Database Discussions','Database discussions'),(2,'Unix/Linux discussion','Unix and Linux discussions');
INSERT 0 2
forumdb=> merge into categories c
using new_data n on c.pk=n.pk
when matched then
update set title=n.title,description=n.description
when not matched then
insert (pk,title,description)
OVERRIDING SYSTEM VALUE values (n.pk,n.title,n.description);
MERGE 2

Mengeksplorasi UPDATE ... RETURNING

Klausa RETURNING juga dapat digunakan pada UPDATE:

forumdb=> update categories set title='A.I' where pk=4 returning pk,title,description;
pk | title | description
----|-------|------------------------------
4  | A.I   | Machine Learning discussions
(1 row)
UPDATE 1

Mengeksplorasi DELETE ... RETURNING

Klausa RETURNING juga tersedia untuk DELETE:

forumdb=> delete from t_categories where pk=4 returning pk,title,description;
pk | title            | description
----|------------------|------------------------------
4  | Machine Learning | Machine Learning discussions
(1 row)
DELETE 1

Mengeksplorasi CTE

Common Table Expression (CTE) adalah hasil sementara dari pernyataan SQL, yang dapat berisi SELECT, INSERT, UPDATE, atau DELETE. Masa hidup CTE sama dengan masa hidup kueri. Contoh definisi CTE:

WITH cte_name (column_list) AS (
    CTE_query_definition
)
statement;

Contoh CTE untuk mencari posting oleh penulis tertentu:

forumdb=> with posts_author_1 as
(select p.* from posts p
inner join users u on p.author=u.pk
where username='enrico_pirozzi')
select pk,title from posts_author_1;
pk | title
----|------------------------------
3  | A view of Data types in C++
(1 row)

Alternatifnya, kita dapat menggunakan inline view:

forumdb=> select pk,title from
(select p.* from posts p inner join users u on p.author=u.pk where u.username='enrico_pirozzi') posts_author_1;
pk | title
----|------------------------------
3  | A view of Data types in C++
(1 row)

CTE di PostgreSQL Sejak Versi 12

Mulai dari PostgreSQL 12, opsi MATERIALIZED dan NOT MATERIALIZED diperkenalkan. Contoh dengan MATERIALIZED:

forumdb=> with posts_author_1 as materialized
(select p.* from posts p
inner join users u on p.author=u.pk
where username='enrico_pirozzi')
select pk,title from posts_author_1;
pk | title
----|------------------------------
3  | A view of Data types in C++
(1 row)

Dengan NOT MATERIALIZED:

forumdb=> with posts_author_1 as not materialized
(select p.* from posts p
inner join users u on p.author=u.pk
where username='enrico_pirozzi')
select pk,title from posts_author_1;
pk | title
----|------------------------------
3  | A view of Data types in C++
(1 row)

Jika tidak ditentukan, defaultnya adalah NOT MATERIALIZED, yang dapat memengaruhi performa saat migrasi dari versi sebelumnya.

Kasus Penggunaan CTE

Contoh penggunaan CTE:

1. Buat dua tabel baru:

forumdb=> create temp table t_posts as select * from posts;
SELECT 3
forumdb=> create table delete_posts as select * from posts limit 0;
SELECT 0

1. Pindahkan catatan yang dihapus dari t_posts ke delete_posts:

forumdb=> with del_posts as (
delete from t_posts
where category in (select pk from categories where title ='Database Discussions')
returning *)
insert into delete_posts select * from del_posts;
INSERT 0 2

Hasilnya:

forumdb=> select pk,title,category from t_posts ;
pk | title                        | category
----|------------------------------|----------
3  | A view of Data types in C++  | 3
(1 row)
forumdb=> select pk,title,category from delete_posts ;
pk | title               | category
----|---------------------|----------
1  | Indexing PostgreSQL | 1
2  | Indexing Mysql      | 1
(2 rows)

1. Contoh lain, pindahkan semua catatan dari t_posts ke inserted_posts:

forumdb=> create table inserted_posts as select * from posts limit 0;
SELECT 0
forumdb=> with ins_posts as ( insert into inserted_posts select * from t_posts returning pk) delete from t_posts where pk in (select pk from ins_posts);
DELETE 3

Kueri Rekursif

PostgreSQL mendukung kueri rekursif, yang berguna untuk basis data graf atau struktur pohon. CTE rekursif memungkinkan pernyataan tambahan mereferensikan dirinya sendiri.

Contoh:

forumdb=> insert into tags (tag,parent) values ('PostgreSQL',1);
INSERT 0 1
forumdb=> WITH RECURSIVE tags_tree AS (
    SELECT tag, pk, 1 AS level
    FROM tags WHERE parent IS NULL
    UNION
    SELECT tt.tag|| ' -> ' || ct.tag, ct.pk, tt.level + 1
    FROM tags ct
    JOIN tags_tree tt ON tt.pk = ct.parent
)
SELECT level,tag FROM tags_tree order by level;
level | tag
-------|------------------------
1     | Database
1     | Operating Systems
2     | Database -> PostgreSQL
(3 rows)

Peringatan: Hindari loop tak terbatas dalam CTE rekursif dengan memastikan rekursi berhenti dengan benar.

Ringkasan

Bab ini membahas kueri kompleks, termasuk pernyataan SELECT dengan klausa LIKE, ILIKE, DISTINCT, OFFSET, LIMIT, IN, dan NOT IN. Kita juga mempelajari agregasi dengan GROUP BY dan HAVING, serta fungsi agregasi seperti SUM(), COUNT(), AVG(), MIN(), dan MAX(). Selain itu, kita menjelajahi subkueri, berbagai jenis join, serta pernyataan UNION, EXCEPT, dan INTERSECT. Terakhir, kita membahas opsi lanjutan untuk INSERT, DELETE, UPDATE, dan MERGE, serta kekuatan CTE dalam PostgreSQL.

Pada bab berikutnya, kita akan mempelajari cara membuat agregasi menggunakan window functions, yang memungkinkan pembuatan agregasi baru dengan lebih fleksibel.