Menu

Array PostgreSQL: ANY, Contains (@>), dan unnest

Kolom array di PostgreSQL: cara mendeklarasikannya, indeks yang dimulai dari 1, mengecek apakah array berisi sebuah nilai dengan ANY, @>, dan &&, unnest, serta fungsi array yang kamu butuhkan.

Halaman ini dilengkapi editor yang bisa dijalankan. Edit, jalankan, dan lihat output-nya seketika.

Tipe PostgreSQL apa pun bisa menjadi array. Deklarasikan kolomnya sebagai text[] atau int[], tulis nilainya sebagai '{a,b}' atau ARRAY['a', 'b'], dan baca elemennya dengan tags[1] (indeks dimulai dari 1). Untuk mengecek apakah array berisi sebuah nilai, pakai 'value' = ANY(tags) atau tags @> ARRAY['value'].

Post 1 dan 2 punya tag sql. tags[1] adalah elemen pertama dan cardinality menghitung jumlahnya. Kedua bentuk literal menyimpan nilai yang sama; ARRAY[...] lebih mudah saat elemennya berasal dari ekspresi atau mengandung koma.

Mengecek apakah array berisi sebuah nilai

Empat operator mencakup pengecekan keanggotaan:

UjiArtiTrue untuk '{sql,json}'
'sql' = ANY(tags)satu nilai ada di dalam arrayya
tags @> '{sql,json}'array berisi semua nilai iniya
tags <@ '{sql,json,go}'setiap elemen ada di daftar iniya
tags && '{go,json}'kedua array punya minimal satu elemen yang samaya

Hanya post 1 yang punya kedua tag. Post 2 dan 3 punya elemen yang sama dengan {go,json}. Post 3 satu-satunya yang no_go-nya false. ALL adalah pasangan dari ANY: perbandingannya harus berlaku untuk setiap elemen.

= ANY mengikuti aturan NULL di SQL. Jika nilainya tidak ditemukan dan array berisi NULL, hasilnya NULL, bukan false. Jadi WHERE NOT ('x' = ANY(tags)) bisa membuang baris yang kamu harapkan muncul. @> dan && mengembalikan true atau false.

Index GIN membuat @>, <@, &&, dan = cepat di tabel besar. Index ini tidak membantu = ANY(column), jadi untuk pencarian yang memakai index, tulis tags @> ARRAY['sql']:

CREATE INDEX posts_tags_idx ON posts USING gin (tags);

Mengubah array menjadi baris dengan unnest

unnest mengembalikan satu baris per elemen, jadi kamu bisa menghitung, join, atau memfilter elemen satu per satu:

sql muncul di dua post dan tag lainnya masing-masing di satu post. Tambahkan WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) untuk mendapatkan posisi setiap elemen juga. Untuk mengumpulkan baris kembali menjadi array, pakai array_agg (lihat string_agg dan array_agg).

Menambah, menghapus, dan mengubah elemen

Hasilnya {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30}, dan a, b, c. Di UPDATE, fungsi yang sama mengubah array yang tersimpan: SET tags = array_append(tags, 'new') atau SET tags = array_remove(tags, 'old'). array_remove menghapus setiap salinan nilai itu. string_to_array('a,b,c', ',') memecah teks menjadi array.

Mendapatkan panjang array

cardinality(tags) mengembalikan jumlah elemen, dan 0 untuk array kosong. array_length(tags, 1) mengembalikan panjang dimensi 1, dan NULL untuk array kosong. Jadi WHERE array_length(tags, 1) = 0 tidak pernah cocok. Uji array kosong dengan cardinality(tags) = 0 atau tags = '{}'.

Array PostgreSQL bisa punya lebih dari satu dimensi (int[][]), tapi setiap sub-array harus sama panjangnya, dan ukuran yang dideklarasikan (int[3]) tidak ditegakkan. Untuk data bersarang atau tidak rata, JSONB biasanya lebih cocok.

Pertanyaan yang sering diajukan

Apakah array PostgreSQL dimulai dari 0 atau 1?

Secara default dimulai dari 1: tags[1] adalah elemen pertama. Indeks yang melewati akhir array mengembalikan NULL, bukan error.

Apa perbedaan IN dan = ANY di PostgreSQL?

x IN (1, 2, 3) menerima daftar yang ditulis di query; x = ANY(array) menerima satu nilai array. ANY cocok untuk kode aplikasi, karena kamu bisa mengirim satu parameter array (WHERE id = ANY($1)) alih-alih menyusun daftar placeholder.

Sebaiknya memakai kolom array atau tabel terpisah?

Array cocok untuk daftar pendek yang kamu baca dan tulis bersama barisnya, seperti tag. Saat item-itemnya butuh kolom sendiri, foreign key, atau diperbarui satu per satu, tabel terpisah dengan satu baris per item lebih mudah di-query dan diberi constraint.

Bagaimana cara mengecek apakah array kosong di PostgreSQL?

Pakai cardinality(arr) = 0 atau arr = '{}'. array_length(arr, 1) = 0 tidak pernah cocok, karena array_length mengembalikan NULL untuk array kosong.

Ilustrasi bahasa pemrograman di Coddy

Belajar coding dengan Coddy

Mulai