FETCH

Kirish: ushbu darsda siz so'rovdan bir qism qatorlarni olish uchun PostgreSQL FETCH bandini qanday ishlatishni o'rganasiz.

PostgreSQL FETCH bandiga kirish

PostgreSQL da OFFSET bandi LIMIT bandi kabi ishlaydi. FETCH bandi so'rovdan qaytariladigan qatorlar sonini cheklash imkonini beradi. LIMIT bandi SQL standartining bir qismi emas. Ammo FETCH bandi SQL:2008 standartining bir qismidir. Agar ilovangiz kelajakda boshqa ma'lumotlar bazalarini qo'llashini xohласангиз, LIMIT o'rniga FETCH dan foydalaning, chunki boshqa ma'lumotlar bazasi ishlab chiqaruvchilari ham uni qo'llab-quvvatlash ehtimoli yuqori.

OFFSET FETCH bandining sintaksisi:

SELECT column1, column2
FROM table_name
ORDER BY sort_expression
OFFSET skip_count
FETCH { FIRST | NEXT } [row_count] {ROW | ROWS} ONLY;

Bu sintaksisda:

• Birinchi, FROM bandida ma'lumot olishni istagan jadval nomini ko'rsating.
• Ikkinchi, SELECT bandida yakuniy natija to'plamiga kiriting­ishi kerak bo'lgan ustunlarni aniqlang.
• Uchinchi, bir yoki bir nechta ustundagi qiymatlar bo'yicha qatorlarni tartiblash uchun ORDER BY bandidan foydalaning.
• To'rtinchi, FETCH bandi bir qism qatorlarni qaytarishdan oldin o'tkazib yuboriladigan qatorlar sonini OFFSET bandida ko'rsating:

OFFSET skip_count

skip_count o'tkazib yuboriladigan qatorlar sonini aniqlaydi. Bu nol yoki musbat butun son bo'lishi mumkin. Agar skip_count nol bo'lsa, so'rov hech qaysi qatorni o'tkazib yubormagin. OFFSET bandi ixtiyoriy. Uni qo'ymasangiz, so'rov ham hech qaysi qatorni o'tkazib yubormayin.

• Beshinchi, FETCH bandida qaytariladigan qatorlar sonini (row_count) belgilang:

FETCH { FIRST | NEXT } [row_count] {ROW | ROWS} ONLY;

row_count standart qiymati 1 ga teng, ya'ni agar uni qo'ymasangiz, FETCH so'rovdan bitta qatorni qaytaradi. FIRST va NEXT, ROW va ROWS ni bir-birining o'rnida ishlatish mumkin, chunki ular sinonimlar. Masalan:

FETCH FIRST 1 ROW ONLY;
FETCH FIRST ROW ONLY;
FETCH FIRST 10 ROWS ONLY;
FETCH NEXT 1 ROW ONLY;
FETCH NEXT ROW ONLY;
FETCH NEXT 10 ROWS ONLY;

Namuna jadval tayyorlash

FETCH bandi bilan mashq qilish uchun LIMIT darsida yaratilgan products jadvalidan foydalanamiz. products jadvalini yaratish va qatorlar kiritish uchun SQL skript:

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2)
);

INSERT INTO
  products (name, price)
VALUES
  ('iPhone 16 Pro Max', 1649.00),
  ('iPhone 16', 829.00),
  ('Galaxy S24 Ultra', 1299.99),
  ('Galaxy S24 FE', 949.99),
  ('Pixel 9 Pro', 799.00),
  ('Pixel 8a', 399.00),
  ('OnePlus 12', 745.00),
  ('OnePlus Open', 1514.99),
  ('Galaxy Z Fold 6', 2019.99);

Birinchi qatorni olish

Quyidagi ifoda FETCH bandidan foydalanib, products jadvalidagi eng qimmat mahsulotni qaytaradi:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
FETCH FIRST ROW ONLY;

Natija:

name            | price
----------------+---------
 Galaxy Z Fold 6| 2019.99

Bir nechta qatorlarni olish

Quyidagi misolda FETCH bandidan foydalanib, products jadvalidagi eng qimmat uchta mahsulot qaytariladi:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
FETCH FIRST 3 ROWS ONLY;

Natija:

name              | price
------------------+---------
 Galaxy Z Fold 6  | 2019.99
 iPhone 16 Pro Max| 1649.00
 OnePlus Open     | 1514.99

Olishdan oldin ba'zi qatorlarni o'tkazib yuborish

Quyidagi ifoda products jadvalidagi ikkinchi eng qimmat mahsulotni olish uchun OFFSET va FETCH bandlaridan foydalanadi:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
OFFSET 1
FETCH NEXT 1 ROW ONLY;

Natija:

name              | price
------------------+---------
 iPhone 16 Pro Max| 1649.00

Sahifalash uchun PostgreSQL FETCH bandini ishlatish

FETCH bandi sahifalash uchun foydali bo'lishi mumkin:

OFFSET record_per_page * (page_no - 1)
FETCH NEXT record_per_page ROWS ONLY;

Bu sintaksisda:

• record_per_page — har bir sahifadagi yozuvlar soni.
• page_no — 1, 2, 3 kabi sahifa raqami.

Tasavvur qiling, har sahifada uchta mahsulot (record_per_page) ko'rsatmoqchi va 2-sahifa (page_no) uchun mahsulotlarni olmoqchisiz; quyidagi so'rovdan foydalanishingiz mumkin:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
OFFSET 3 * (2 - 1)
FETCH NEXT 3 ROW ONLY;

Natija:

name             | price
-----------------+---------
 Galaxy S24 Ultra| 1299.99
 Galaxy S24 FE   |  949.99
 iPhone 16       |  829.00

Xulosa

• So'rovdan bir qism qatorlarni qaytarish uchun PostgreSQL FETCH bandidan foydalaning.
• Ba'zi qatorlarni o'tkazib yuborib, keyingilari orasidan bir qism qatorlarni olish uchun OFFSET FETCH dan foydalaning.