PostgreSQL یکی از قدرتمندترین پایگاه دادههای رابطهای-شیءگرا است. با Postgres میتوانید دادههای حجیم با میلیونها ردیف را به راحتی مدیریت کنید.
استفاده صحیح از توابع و قابلیتهای PostgreSQL، باعث میشود بسیاری از محاسبات و تحلیلها به جای کدنویسی پیچیده در برنامهها، مستقیم در سطح دیتابیس انجام شود.
در ادامه توابع و قابلیتهای پیشرفته را با مثالهای عملی بررسی میکنیم.
در SQL، مقدار NULL به معنی «ناموجود» است و میتواند در محاسبات و فیلترها مشکل ایجاد کند. PostgreSQL چند ابزار برای مدیریت آن دارد.
مثال: جایگزینی NULL در شهر ارسال
SELECT order_id, COALESCE(ship_city, 'Unknown') AS ship_city
FROM orders;
مثال: جمع کل هزینه حمل با جایگزینی NULL با صفر
SELECT customer_id, SUM(COALESCE(freight,0)) AS total_freight
FROM orders
GROUP BY customer_id;
SELECT product_name, unit_price / NULLIF(units_in_stock,0) AS price_per_unit_in_stock
FROM products;
SELECT UPPER(company_name), LOWER(ship_city), TRIM(product_name)
FROM customers;
|| برای اتصال رشتهها. مشکل: اگر یکی از ستونها NULL باشد، نتیجه NULL میشود.CONCAT() یا CONCAT_WS() که NULL را نادیده میگیرد.SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
SELECT CONCAT_WS('|', first_name, last_name) AS full_name
FROM employees;
SELECT SUBSTRING('PostgreSQL',1,8); -- PostgreS
SELECT SUBSTRING('PostgreSQL',8); -- SQL
SELECT
regexp_replace(phone, '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS formatted_phone,
regexp_replace(email, '@.*$', '@[hidden]') AS masked_email
FROM contacts;
SELECT
email,
split_part(email, '@', 1) AS username,
split_part(email, '@', 2) AS domain
FROM users;
| تابع | کاربرد |
|---|---|
| ROUND() | گرد کردن اعداد |
| CEIL()/CEILING() | گرد به بالا |
| FLOOR() | گرد به پایین |
| ABS() | قدر مطلق |
| MOD() | باقیمانده تقسیم |
مثال: مجموع هزینه حمل گرد شده
SELECT customer_id, ROUND(SUM(freight),2) AS total_freight
FROM orders
GROUP BY customer_id;
| تابع | کاربرد |
|---|---|
| EXTRACT() | استخراج سال، ماه، روز، روز هفته و … |
| DATE_TRUNC() | برش تاریخ به ماه، سال، فصل |
| NOW() / CURRENT_TIMESTAMP | زمان جاری |
| AGE() | اختلاف بین دو تاریخ |
| DATE_PART() | مشابه EXTRACT |
مثال: تعداد سفارشها در هر ماه
SELECT DATE_TRUNC('month', order_date) AS month_start, COUNT(*) AS total_orders
FROM orders
GROUP BY month_start
ORDER BY month_start;
SELECT category_id, ROUND(AVG(unit_price),2) AS avg_price
FROM products
GROUP BY category_id;
SELECT region, ARRAY_AGG(title) AS titles
FROM employees
GROUP BY region;
مزیت: تمام مقادیر مرتبط در یک ردیف و یک ستون نمایش داده میشوند.
SELECT region, JSON_AGG(title) AS titles_json
FROM employees
GROUP BY region;
SELECT region, STRING_AGG(title,' | ') AS titles_str
FROM employees
GROUP BY region;
SELECT emp_name,
CASE
WHEN salary < 30000 THEN 'Low'
WHEN salary >= 30000 AND salary < 70000 THEN 'Medium'
ELSE 'High'
END AS category
FROM employees;
SELECT
COUNT(CASE WHEN salary < 30000 THEN 1 END) AS "# Low",
COUNT(CASE WHEN salary >= 30000 AND salary < 70000 THEN 1 END) AS "# Medium",
COUNT(CASE WHEN salary >= 70000 THEN 1 END) AS "# High"
FROM employees;
SELECT
CAST('2020-09-25' AS DATE),
price::numeric(10,2) AS formatted_price,
created_at::date AS date_only
FROM orders;
SELECT * FROM companies
WHERE company_name SIMILAR TO '%(apple|google|microsoft)%';
SELECT DISTINCT ON (department) *
FROM employees
ORDER BY department, salary DESC;
WITH (CTE) برای خوانایی و استفاده مجددمثال: تعداد سفارشهای هر کشور بیشتر از ۲۰
WITH country_orders AS (
SELECT ship_country, COUNT(*) AS total_orders
FROM orders
GROUP BY ship_country
)
SELECT *
FROM country_orders
WHERE total_orders > 20;
Pivot Table دادهها را از حالت ستونی-ردیفی به ستونی-ستونی تبدیل میکند.
در PostgreSQL از ماژول tablefunc و تابع crosstab() استفاده میکنیم.
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT *
FROM crosstab(
$$
SELECT
TO_CHAR(DATE_TRUNC('month', order_date),'YYYY-MM') AS month_label,
ship_country,
COUNT(*) AS cnt
FROM orders
GROUP BY 1, ship_country
ORDER BY 1, ship_country
$$,
$$
SELECT DISTINCT ship_country
FROM orders
ORDER BY ship_country
$$
) AS ct(
month_label text,
"Argentina" int,
"Austria" int,
"Belgium" int,
"Brazil" int,
"Canada" int,
"Denmark" int,
"Finland" int,
"France" int,
"Germany" int,
"Ireland" int,
"Italy" int,
"Mexico" int,
"Norway" int,
"Poland" int,
"Portugal" int,
"Spain" int,
"Sweden" int,
"Switzerland" int,
"UK" int,
"USA" int,
"Venezuela" int
);
✅ مزیت: تمام ماهها در ردیف و کشورهای مختلف در ستونها نمایش داده میشوند.
💡 نکته: تعداد ستونها درAS ct(...)باید دقیقاً با تعداد ستونهای خروجی تطابق داشته باشد.
SELECT id, name, random() AS random_value
FROM products
ORDER BY random()
LIMIT 10;
| دسته | توابع / قابلیت | کاربرد |
|---|---|---|
| Null | COALESCE, NULLIF, ISNULL | مدیریت مقادیر NULL |
| رشته | UPPER, LOWER, TRIM, CONCAT, CONCAT_WS, SUBSTRING, REGEXP_REPLACE, SPLIT_PART | پردازش متن |
| عدد | ROUND, CEIL, FLOOR, ABS, MOD | عملیات عددی |
| تاریخ | EXTRACT, DATE_TRUNC, NOW, AGE, DATE_PART | پردازش تاریخ |
| تجمعی | COUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, STRING_AGG, JSON_AGG | تحلیل و جمعبندی دادهها |
حتماً! من ادامه بخش توابع کاربردی و پیشرفته PostgreSQL را با توابع جدید PostgreSQL 18 و امکانات جالبی مثل any_value(), توابع تجمیعی سفارشی و generate_series() آماده کردهام. همه مثالها با توضیح روش سنتی و مزیت استفاده از PostgreSQL ارائه شدهاند و برای Northwind و دادههای نمونه قابل اجرا هستند.
MIN یا MAX بگیریم.MIN, MAX یا زیرپرسوجو برای گرفتن یک مقدار دلخواه استفاده میشد.مثال – گرفتن یک عنوان کارمند دلخواه از هر منطقه
SELECT region, ANY_VALUE(title) AS sample_title
FROM employees
GROUP BY region;
✅ مزیت: سریع، بدون نیاز به زیرپرسوجو یا aggregation پیچیده.
PostgreSQL از نسخه ۱۳ به بعد، امکان ایجاد توابع تجمعی سفارشی را فراهم کرده است. این توابع به شما اجازه میدهند رفتار دلخواه برای جمعآوری یا ترکیب دادهها را در خود دیتابیس تعریف کنید، بدون نیاز به پردازش در برنامه بیرونی.
🔹 کلیت کار:
یک تابع تجمعی معمولاً برای ترکیب یا جمعآوری چند مقدار ورودی به یک خروجی استفاده میشود، مانندSUM(),AVG()یاSTRING_AGG().
اما گاهی نیاز داریم رفتار دلخواه خود را بسازیم، مثلاً ترکیب رشتهها با یک جداکننده خاص یا محاسباتی که توابع استاندارد ارائه نمیکنند.
برای ایجاد یک تابع تجمعی سفارشی، سه بخش اصلی وجود دارد:
CREATE AGGREGATE:string_concat_with_separator)فرض کنید میخواهیم تابعی شبیه STRING_AGG() بسازیم که رشتهها را با یک جداکننده دلخواه ترکیب کند، ولی قابلیت تعریف جداکننده به صورت پارامتر دلخواه داشته باشد.
CREATE FUNCTION string_concat_sfunc(text, text, text) RETURNS text AS $$
BEGIN
IF $1 IS NULL THEN
RETURN $2;
ELSE
RETURN $1 || $3 || $2;
END IF;
END;
$$ LANGUAGE plpgsql;
$۱: رشته انباشته شده فعلی (state)$۲: رشته جدید برای اضافه کردن (input)$۳: جداکننده بین رشتههاتوضیح: اگر state اولیه NULL باشد، رشته جدید جایگزین آن میشود؛ در غیر این صورت رشته جدید همراه با جداکننده به state اضافه میشود.
CREATE AGGREGATE string_concat_with_separator(text, text) (
SFUNC = string_concat_sfunc,
STYPE = text,
INITCOND = NULL
);
string_concat_with_separator(text, text): نام تابع و نوع ورودیها (رشته + جداکننده)SFUNC = string_concat_sfunc: تابع انتقال وضعیتSTYPE = text: نوع داده داخلی stateINITCOND = NULL: مقدار اولیه stateSELECT string_concat_with_separator(title, ', ') AS all_titles
FROM employees;
title) از جدول employees با جداکننده , ترکیب میشوند.مثال: تولید ۱۰۰ یوزر نمونه با ایمیل، پسورد و نام و نام خانوادگی
SELECT
'user' || gs AS username,
CONCAT('first', gs) AS first_name,
CONCAT('last', gs) AS last_name,
CONCAT('user', gs, '@example.com') AS email,
MD5('pass' || gs) AS password_hash
FROM generate_series(1,100) AS gs;
توضیح:
generate_series(1,100)۱ تا ۱۰۰ را تولید میکند.||وCONCAT()برای ساخت رشتهها.MD5()برای هش کردن پسورد.- نتیجه: ۱۰۰ ردیف با اطلاعات کامل و امن برای تست یا بارگذاری اولیه.
| دسته | توابع / قابلیت | کاربرد |
|---|---|---|
| Aggregation جدید | ANY_VALUE() | گرفتن یک نمونه دلخواه از هر گروه |
| Aggregation سفارشی | CREATE AGGREGATE | تعریف محاسبات دلخواه |
| تولید داده | generate_series() | ایجاد داده یا اعداد ترتیبی |
| رشته و هش | MD5(), CONCAT(), |
💡 نکات کاربردی:
generate_series() و توابع متنی میتوان دادههای آزمایشی یا seed برای جدولها تولید کرد.any_value() مخصوصاً برای گزارشگیری سریع و نمونهگیری در گروهها بسیار مفید است.