ARRAY

Ushbu qo'llanmada PostgreSQLda bir xil turdagi bir nechta qiymatlarni bitta ustunda saqlash uchun ARRAY (massiv) ma'lumot turidan qanday foydalanishni o'rganasiz.

PostgreSQL ARRAY ma'lumot turiga kirish

PostgreSQLda har qanday ma'lumot turi (shu jumladan foydalanuvchi yaratgan maxsus turlar ham) o'zining tegishli massiv turiga ega. Masalan, INT turi uchun INT[], VARCHAR uchun VARCHAR[] va h.k.

Massiv ustunini aniqlash sintaksisi:

column_name datatype[]

Ko'p o'lchovli massivlar uchun esa qo'shimcha qavslar qo'yiladi (masalan, datatype[][]).

Namunaviy jadval

Mahsulotlar va ularning xususiyatlarini saqlovchi jadval tuzamiz:

DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE products (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  features TEXT[]
);

Bu yerda features TEXT[] ustuni bir nechta matnli xususiyatlarni massiv ko'rinishida saqlaydi.

Massivga ma'lumot kiritish (INSERT)

Massiv qiymatlarini kiritishning ikkita asosiy usuli bor:

1) ARRAY konstruktori yordamida:

INSERT INTO products (name, price, features)
VALUES (
  'Smartphone X1',
  699.99,
  ARRAY['5G', 'OLED Display', '512GB Storage']
);

2) Figurali qavslar {} yordamida:

INSERT INTO products (name, price, features)
VALUES (
  'Laptop Pro 15',
  1299.99,
  '{"Touchscreen", "32GB RAM", "1024GB SSD"}'
);

Massiv ma'lumotlarini o'qish (SELECT)

[!IMPORTANT] Ko'pgina dasturlash tillaridan farqli o'laroq, PostgreSQL massivlarida indekslash 1 dan boshlanadi (0 dan emas!).

1) Alohida elementni o'qish

Mahsulotning birinchi xususiyatini olish uchun:

SELECT
  name,
  features[1] AS main_feature
FROM
  products;

Natija:

     name      | main_feature 
---------------+--------------
 Smartphone X1 | 5G
 Laptop Pro 15 | Touchscreen

2) Butun massivni chiqarish:

SELECT name, features FROM products;

Natija:

     name      |               features                
---------------+---------------------------------------
 Smartphone X1 | {5G,"OLED Display","512GB Storage"}
 Laptop Pro 15 | {Touchscreen,"32GB RAM","1024GB SSD"}

Massiv elementlarini yangilash (UPDATE)

Aniq bitta elementni o'zgartirish:

UPDATE products
SET features[2] = 'Super Retina Display'
WHERE id = 1;

Butun massivni to'liq yangilash:

UPDATE products
SET features = ARRAY['OLED Display', '256GB Storage', 'Water Resistant']
WHERE id = 2;

Massiv ichidan qidirish (ANY operatori)

Massiv ichida muayyan qiymat bor-yo'qligini tekshirish uchun ANY operatoridan foydalaniladi:

SELECT name, features
FROM products
WHERE '5G' = ANY (features);

Natija:

     name      |                   features                    
---------------+-----------------------------------------------
 Smartphone X1 | {5G,"Super Retina Display","512GB Storage"}

Massivni qatorlarga yoyish (UNNEST funksiyasi)

UNNEST() funksiyasi massiv elementlarini alohida SQL qatorlariga aylantirib beradi:

SELECT
  name,
  UNNEST(features) AS feature
FROM
  products;

Natija:

     name      |       feature        
---------------+----------------------
 Smartphone X1 | 5G
 Smartphone X1 | Super Retina Display
 Smartphone X1 | 512GB Storage
 Laptop Pro 15 | OLED Display
 Laptop Pro 15 | 256GB Storage
 Laptop Pro 15 | Water Resistant

Massiv funksiyalari

Elementlar sonini bilish (ARRAY_LENGTH)

SELECT name, array_length(features, 1) AS total_features
FROM products;

Element qo'shish (ARRAY_APPEND)

UPDATE products
SET features = ARRAY_APPEND(features, 'Fingerprint')
WHERE id = 1;

Elementni o'chirish (ARRAY_REMOVE)

UPDATE products
SET features = ARRAY_REMOVE(features, 'Water Resistant')
WHERE id = 2;

Massivlarni indekslash (GIN Index)

Massiv elementlari bo'yicha tezkor qidiruvni ta'minlash uchun GIN (Generalized Inverted Index) indeksidan foydalaniladi:

CREATE INDEX idx_products_features ON products USING GIN (features);

Xulosa

• ARRAY bitta ustunda bir nechta bir xil turdagi qiymatlarni (teglar, xususiyatlar, telefon raqamlari) saqlash uchun qulay.
• Massiv indekslari 1 dan boshlanadi.
• Massiv ichidan qidirish uchun ANY, massivni qatorlarga ajratish uchun UNNEST ishlatiladi.
• Massivlar bo'yicha qidiruvni tezlashtirish uchun GIN indekslari qo'llanadi.