Massivlar

CREATE TABLE maqola (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sarlavha text NOT NULL,
    teglar   text[] NOT NULL DEFAULT '{}',
    ballar   int[]
);

INSERT INTO maqola (sarlavha, teglar, ballar) VALUES
    ('PostgreSQL asoslari', ARRAY['baza','postgresql','sql'], ARRAY[5,4,5]),
    ('Indekslar haqida',    ARRAY['baza','optimizatsiya'],    ARRAY[4,4,3]),
    ('JSONB bilan ishlash', ARRAY['baza','postgresql','json'],ARRAY[5,5,5]),
    ('Tarmoq asoslari',     ARRAY['tarmoq'],                  ARRAY[3,4]),
    ('Bo''sh maqola',       '{}',                             NULL);

SELECT ARRAY[1,2,3] AS birinchi_usul,
       '{4,5,6}'::int[] AS ikkinchi_usul,
       array_length(ARRAY['a','b','c'], 1) AS uzunlik;

SELECT sarlavha,
       teglar[1] AS birinchi_teg,
       teglar[2:3] AS kesim,
       array_length(teglar, 1) AS teglar_soni
FROM maqola
WHERE sarlavha = 'PostgreSQL asoslari';

SELECT sarlavha, teglar
FROM maqola
WHERE 'postgresql' = ANY(teglar)
ORDER BY id;

SELECT sarlavha, teglar
FROM maqola
WHERE teglar @> ARRAY['baza','postgresql']
ORDER BY id;

SELECT sarlavha
FROM maqola
WHERE teglar && ARRAY['tarmoq','json']
ORDER BY id;

SELECT teg, count(*) AS soni
FROM maqola, unnest(teglar) AS teg
GROUP BY teg
ORDER BY soni DESC, teg;

SELECT array_agg(sarlavha ORDER BY id) AS sarlavhalar
FROM maqola
WHERE 'baza' = ANY(teglar);

SELECT sarlavha,
       ballar,
       (SELECT round(avg(b), 2) FROM unnest(ballar) AS b) AS ortacha
FROM maqola
WHERE ballar IS NOT NULL
ORDER BY id;

SELECT ARRAY['a','b'] || 'c'::text        AS oxiriga_qoshish,
       ARRAY['a','b'] || ARRAY['c','d']  AS ikki_massiv,
       array_remove(ARRAY['a','b','a'], 'a') AS olib_tashlash,
       array_position(ARRAY['x','y','z'], 'y') AS orni;