Postgre SQL da o'zimiz uchun NUMERIC, VARCHAR, TIMESTAMP, BOOLEAN va boshqa type larga o'xshagan type yaratib ko'ramiz!

Masalan bizda users table bor va unda status column bor u columnda esa active va passive valuelar bo'lishi kerak biz data create qilayotganimizda adashib boshqa value berib yubormasligimiz uchun o'zimizga quyidagicha query orqali type yaratib olamiz 👇

CREATE TYPE user_status AS ENUM ('active', 'passive');

Keyin esa misol uchun users table quyidagicha yaratiladi 👇

CREATE TABLE IF NOT EXISTS movies(
id SERIAL PRIMARY KEY,
full_name VARCHAR(200),
status USER_STATUS NOT NULL
);

Users table ning status columniga type create qilganimizda bergan nomni berib ketamiz ya'ni user_status ni!

Biz yaratgan users table dagi status columni endi faqatgina active va passive value larini qabul
qiladi.✅

*MISSION PASSED

Malumotlarimiz foydali bo'layotgan bo'lsa reaksiya bosing.

Biz quyidagicha jadvalni yaratishimiz kerak 👇

user = {
id: "uuid",
phone: 998933333333,
password: "adadsfuh25235561324sdf",
full_name: "John Doe",
role: "user" // user, admin
}

Bunday holatda nima qilamiz. Bunday holatda biz PostgreSql ga o'zimizga kerakli UUID extension ni o'rnatib olamiz: 👇

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Extension o'rnatib oldik endi bizga user ning role uchun user va admin value laridan boshqa valuelarini kiritib qo'ymasligi uchun type yaratib olamiz 👇

CREATE TYPE role_user AS ENUM ('user', 'admin');

Type ham yaratib oldik endi query yozishimiz kerak. Table create query sini quyidagicha yozamiz: 👇

CREATE TABLE IF NOT EXISTS users(
id uuid DEFAULT uuid_generate_v4(),
phone INTEGER NOT NULL ,
password TEXT NOT NULL ,
full_name VARCHAR(70),
role role_user NOT NULL,
PRIMARY KEY (id)
);

Ushbu querydagi asosiy joylari bu PRIMARY KEY id uchun biz UUID dan foydalandik va DEFAULT kiritib ketishi uchun UUID extensionning ichida 4 versiyasi func ni chaqirib ishlatib yubordik! Qolgan columnlarida esa avval yozgan querylar. Role uchun esa o'zimiz yozgan USER_ROLE type dan foydalandik!

UPSERT

ni ko'rib chiqamiz!


Bizda shop table bor do'kon uchun! Va bizga mahsulot keldi agar u mahsulotni qo'shayotganimiz xuddi o'sha nom bilan yana bir mahsulot bazada mavjud bo'lsa bizda CONFLICT ya'ni xatolik chiqib qoladi agar o'sha mahsulot name UNIQUE (takrorlanmas) bo'lsa.

Bunday holatda biz CONFLICT chiqqan payti xatolik chiqmasligining olidini olish uchun UPSERT dan foydalanib bajaramiz!

INSERT INTO shop
(name, count, price)
VALUES
("Iphone", 15, 1500);

Ushbu query orqali item qo'shayotganimizda agar bazada Iphone mahsuloti bo'lsa va name UNIQUE bo'lsa CONFLICT yuzaga kelishi aniq. U xatolikni ushbu query orqali oldini olib ketishimiz mumkin. 👇

INSERT INTO shop
(name, count, price)
VALUES
("Iphone", 15, 1500)
ON CONFLICT (name)
DO NOTHING;

Ushbu query name column da CONFLICT yuzaga kelgan payti hech narsa qilmay shunchaki o'tkazib yuboradi, bazaga qo'shmaydi!

INSERT INTO shop
(name, count, price)
VALUES
("Iphone", 15, 1500)
ON CONFLICT (name)
DO UPDATE
SET count = shop.count + EXCLUDED.count, price = EXCLUDED.price;

Ushbu query esa bazaga yangi Iphone ni qo'shayotganimizda agar iphone bazada bo'lsa shunchaki uning COUNT i bilan PRICE sini UPDATE qilib qo'yayapti xolos!


Malumotlarimiz foydali bo'layotgan bo'lsa, reaksiya qoldiring 🔥

Assalomu alaykum hammaga, bugun sizlarga o'zim juda kop ishlatadigan Jetbrains mahsuloti bo'lgan DataGrip ni tavsiya qilmoqchiman, Ushbu programma bilan siz SQL bilan juda oson ishlay olasiz, qulay va sifatli yaxshi programma!
Proyektlarda Database Design qilayotganingizda juda ham kerak bo'ladi!

📥 Yuklab olish

P/s: To'g'risi, shell dan bezor bo'lgan edingiz a? 😁

SELECT
    COUNT("id") AS jami,
    SUM(CASE WHEN lang = 'en' THEN 1 ELSE 0 END) AS en_foiz,
    SUM(CASE WHEN lang = 'uz' THEN 1 ELSE 0 END) AS uz_foiz,
    SUM(CASE WHEN lang = 'ru' THEN 1 ELSE 0 END) AS ru_foiz,
    (SUM(CASE WHEN lang = 'en' THEN 1 ELSE 0 END) / COUNT("id")) * 100 AS en_foiz_percent,
    (SUM(CASE WHEN lang = 'uz' THEN 1 ELSE 0 END) / COUNT("id")) * 100 AS uz_foiz_percent,
    (SUM(CASE WHEN lang = 'ru' THEN 1 ELSE 0 END) / COUNT("id")) * 100 AS ru_foiz_percent
FROM users;

Ushbu query orqali siz telegram botingizda uzbek, english, rus foydalanuvchilarini foizda ola olasiz. Statistikada juda ham kerakli bo'lgan query larda bittasi bu. Ushbu query ishlashi uchun sizda quyidagi table bo'lishi kerak 👇

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR(100) NOT NULL,
  status VARCHAR(200) NOT NULL,
  lang VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ushbu query orqali esa siz oxirgi bir soatda, bugun, shu haftada, shu oyda va hamma foydalanuvchilar sonini olishingiz mumkin va bu query ham yuqoridagi table uchun qilingan o'zingizga moslashtirib olasiz degan umiddaman 👇


SELECT
SUM(CASE WHEN created_at >= NOW() - INTERVAL '12 hours' THEN 1 ELSE 0 END) AS last_12_hours,
SUM(CASE WHEN DATE(created_at) = CURRENT_DATE THEN 1 ELSE 0 END) AS today,
SUM(CASE WHEN created_at >= CURRENT_DATE - INTERVAL '1 week' AND created_at < CURRENT_DATE + INTERVAL '1 day' THEN 1 ELSE 0 END) AS last_week,
SUM(CASE WHEN created_at >= CURRENT_DATE - INTERVAL '1 month' AND created_at < CURRENT_DATE + INTERVAL '1 day' THEN 1 ELSE 0 END) AS last_month
FROM users;


Hamma kerakli querylarni pastda sql faylda qoldiraman! 😉
Foydasi tekkan bo'lsa reaksiyani bosib ketamiz 🥹

🖥 DataGrip database diagrammalarini ham chiqarib beradi 😉

🖥 Bugun qilgan loyihamda ishlatilgan querylar jamlanmasi kimgadir kerak bo'lib qolar!
🖥 Koproq JOIN lardan foydalanganman.

Tez orada JOIN lar haqida POST
bo'ladi😊

INNER JOIN ga misol: 👇

SELECT
upt.id,
jsonb_build_object('id', u.id, 'login', u.login, 'password', u.password, 'full_name', u.full_name,
'birthdate', u.birthdate, 'role', u.role, 'file',
row_to_json(f.*)) AS users,
row_to_json(t.*) as test,
upt.total_questions, upt.passed_questions, upt.created_at
FROM user_passed_tests upt
INNER JOIN users u ON upt.user_id = u.id
INNER JOIN tests t ON upt.test_id = t.id
INNER JOIN files f ON f.id = u.file_id
WHERE upt.user_id = 1;