Extensions nima — CREATE EXTENSION, mashhur extensionlar

~15 daq · PostgreSQL · Extensions

PostgreSQL yadroida barcha imkoniyat yo'q — lekin keraklilarini extension sifatida qo'shish mumkin. UUID generatsiya, fuzzy search, geografik ma'lumotlar, statistik funksiyalar — hammasi extension orqali.

Extension nima?

Extension — PostgreSQL ga qo'shiluvchi modul. Yangi turlar, funksiyalar, operatorlar, indeks turlari qo'shadi.

-- Extension o'rnatish
CREATE EXTENSION IF NOT EXISTS extension_nomi;

-- O'chirish
DROP EXTENSION extension_nomi;

-- O'rnatilgan extensionlar
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

Mashhur extensionlar

uuid-ossp — UUID generatsiya

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v4(); -- a7c5a3b2-...

-- Jadvalda
CREATE TABLE foydalanuvchilar (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  ism TEXT
);

PostgreSQL 13+ da gen_random_uuid() built-in — extension shart emas.

pg_trgm — fuzzy search

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Trigram o'xshashlik
SELECT similarity('Noutbuk', 'Noutbok'); -- 0.6

-- ILIKE uchun GIN index
CREATE INDEX ON mahsulotlar USING GIN (nomi gin_trgm_ops);
WHERE nomi ILIKE '%noutb%'; -- endi tez!

hstore — kalit-qiymat turi

CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE sozlamalar (id INT, sozlama hstore);
INSERT INTO sozlamalar VALUES (1, 'til=>uz, mavzu=>qorong_i');
SELECT sozlama->'til' FROM sozlamalar WHERE id = 1; -- uz

pgcrypto — kriptografiya

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Parol hashlash
SELECT crypt('my_password', gen_salt('bf'));

-- UUID (alternative)
SELECT gen_random_uuid();

-- Shifrlash
SELECT pgp_sym_encrypt('maxfiy matn', 'kalit');

tablefunc — crosstab (pivot)

CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Jadvaldan pivot (oy x kategoriya)
SELECT * FROM crosstab('...') AS ...;

pg_stat_statements — so'rovlar statistikasi

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

Available extensions ro'yxati

SELECT name, comment FROM pg_available_extensions ORDER BY name;

Mashhurlar:

uuid-ossp          — UUID generatsiya
pg_trgm            — fuzzy search, trigram
hstore             — kalit-qiymat turi
pgcrypto           — kriptografiya funksiyalari
pg_stat_statements — so'rovlar statistikasi
PostGIS            — geografik ma'lumotlar
timescaledb        — vaqt seriyali ma'lumotlar
pg_partman         — partitioning avtomatsiya

Xulosa

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";   -- UUID
CREATE EXTENSION IF NOT EXISTS pg_trgm;        -- fuzzy search
CREATE EXTENSION IF NOT EXISTS pgcrypto;       -- kriptografiya
CREATE EXTENSION IF NOT EXISTS hstore;         -- kalit-qiymat

-- Ko'rish
SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;

-- O'chirish
DROP EXTENSION extension_nomi;

Extension — PostgreSQL imkoniyatlarini kengaytirish. O'rnatish — bir qator SQL.

Keyingi dars

59-dars: PostGIS — geografik ma'lumotlar, location based queries