Complete Reference SQL commands and functions

📋SELECT Basics

Basic data selection operations

SELECT

Selecting data from a table

SELECT column1, column2, ... FROM table_name

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 10;

SELECT *

Selecting all columns from a table

SELECT * FROM table_name

SELECT * FROM products;

Avoid SELECT * in production — specify columns explicitly

SELECT DISTINCT

Selecting unique values

SELECT DISTINCT column1, column2 FROM table_name

SELECT DISTINCT department_id, job_title
FROM employees;

DISTINCT ON

First row for each unique value

PGGP

SELECT DISTINCT ON (column) columns FROM table ORDER BY column

SELECT DISTINCT ON (department_id) 
  department_id, employee_name, salary
FROM employees
ORDER BY department_id, salary DESC;

LIMIT / TOP / FETCH

Limiting the number of returned rows

SELECT ... LIMIT n [OFFSET m]

-- LIMIT с OFFSET
SELECT * FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20;

-- FETCH FIRST (SQL стандарт)
SELECT * FROM products
ORDER BY price DESC
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;

AS (Aliases)

Assigning aliases to columns and tables

SELECT column AS alias, ... FROM table AS t

SELECT 
  e.first_name AS "Имя",
  e.last_name AS "Фамилия",
  d.name AS "Отдел"
FROM employees e
JOIN departments d ON e.dept_id = d.id;

SELECT INTO

Creating a new table from query results

SELECT columns INTO new_table FROM source

SELECT employee_id, first_name, salary
INTO high_earners
FROM employees
WHERE salary > 100000;

🔍WHERE Filtering

Data filtering conditions

WHERE

Basic row filtering

SELECT ... FROM table WHERE condition

SELECT * FROM orders
WHERE status = 'pending'
  AND total_amount > 1000;

Comparison Operators

Comparing values

=, <>, !=, <, >, <=, >=

SELECT * FROM products
WHERE price >= 100 
  AND price <= 500
  AND stock <> 0;

AND / OR / NOT

Logical operators for combining conditions

WHERE cond1 AND cond2 OR NOT cond3

SELECT * FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND NOT is_terminated
  AND hire_date >= '2020-01-01';

IN / NOT IN

Checking membership in a list of values

WHERE column IN (value1, value2, ...)

SELECT * FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

SELECT * FROM products
WHERE category_id NOT IN (
  SELECT id FROM categories WHERE is_archived = true
);

BETWEEN

Checking if value falls within a range (inclusive)

WHERE column BETWEEN value1 AND value2

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM products
WHERE price NOT BETWEEN 100 AND 500;

LIKE / ILIKE

Pattern matching in strings

WHERE column LIKE 'pattern'

-- % - любое количество символов
-- _ - один символ
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

-- ILIKE - регистронезависимый (PostgreSQL)
SELECT * FROM products
WHERE name ILIKE '%phone%';

IS NULL / IS NOT NULL

Checking for NULL values

WHERE column IS [NOT] NULL

SELECT * FROM employees
WHERE manager_id IS NULL;

SELECT * FROM orders
WHERE shipped_date IS NOT NULL;

NULL != NULL, use IS NULL instead of = NULL

IS DISTINCT FROM

NULL-safe comparison (NULL = NULL → true)

PGGP

WHERE a IS [NOT] DISTINCT FROM b

-- IS DISTINCT FROM: NULL-safe сравнение
SELECT * FROM t1
WHERE col1 IS DISTINCT FROM col2;

-- Эквивалент: col1 <> col2 OR (col1 IS NULL) <> (col2 IS NULL)

EXISTS / NOT EXISTS

Checking for row existence in a subquery

WHERE [NOT] EXISTS (subquery)

-- Клиенты с заказами
SELECT c.* FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.customer_id = c.id
);

-- Товары без продаж
SELECT p.* FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi 
  WHERE oi.product_id = p.id
);

ANY / ALL / SOME

Comparing with subquery results

WHERE column operator ANY|ALL (subquery)

-- ANY: хотя бы одно значение
SELECT * FROM products
WHERE price > ANY (
  SELECT price FROM products WHERE category = 'Electronics'
);

-- ALL: все значения
SELECT * FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE category = 'Books'
);

↕️ORDER BY Sorting

Ordering results

ORDER BY

Sorting query results

SELECT ... ORDER BY column1 [ASC|DESC], column2 ...

SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;

NULLS FIRST / LAST

Controlling NULL position when sorting

PGGP

ORDER BY column [ASC|DESC] NULLS [FIRST|LAST]

SELECT * FROM employees
ORDER BY manager_id NULLS FIRST;

SELECT * FROM orders
ORDER BY shipped_date DESC NULLS LAST;

ORDER BY with expressions

Sorting by calculated values

ORDER BY expression

SELECT name, price, quantity
FROM products
ORDER BY price * quantity DESC;

SELECT * FROM employees
ORDER BY EXTRACT(YEAR FROM hire_date) DESC;

COLLATE

Locale-aware sorting

ORDER BY column COLLATE collation_name

SELECT * FROM customers
ORDER BY name COLLATE "ru_RU";

SELECT * FROM products
ORDER BY name COLLATE "C";  -- байтовая сортировка

📊Aggregate Functions

Calculations over groups of rows

COUNT

Counting the number of rows

COUNT(*) | COUNT(column) | COUNT(DISTINCT column)

SELECT COUNT(*) as total_rows FROM orders;

SELECT COUNT(DISTINCT customer_id) as unique_customers
FROM orders;

SELECT COUNT(shipped_date) as shipped_orders
FROM orders;  -- не считает NULL

SUM

Sum of numeric values

SUM([DISTINCT] expression)

SELECT SUM(total_amount) as revenue
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024;

SELECT SUM(DISTINCT price) FROM products;

AVG

Arithmetic mean

AVG([DISTINCT] expression)

SELECT AVG(salary) as avg_salary
FROM employees
WHERE department_id = 10;

-- Среднее с округлением
SELECT ROUND(AVG(price)::numeric, 2) FROM products;

MIN / MAX

Minimum and maximum values

MIN(expression) | MAX(expression)

SELECT 
  MIN(price) as cheapest,
  MAX(price) as most_expensive,
  MAX(price) - MIN(price) as price_range
FROM products;

SELECT MIN(order_date), MAX(order_date)
FROM orders;

STRING_AGG / LISTAGG

String concatenation within a group

STRING_AGG(expression, delimiter [ORDER BY ...])

SELECT department_id,
  STRING_AGG(employee_name, ', ' ORDER BY employee_name) as employees
FROM employees
GROUP BY department_id;

ARRAY_AGG

Aggregating values into an array

PGGP

ARRAY_AGG(expression [ORDER BY ...])

SELECT department_id,
  ARRAY_AGG(employee_id ORDER BY hire_date) as emp_ids
FROM employees
GROUP BY department_id;

BOOL_AND / BOOL_OR

Logical aggregates

PGGP

BOOL_AND(expression) | BOOL_OR(expression)

SELECT order_id,
  BOOL_AND(is_shipped) as all_shipped,
  BOOL_OR(is_returned) as any_returned
FROM order_items
GROUP BY order_id;

PERCENTILE_CONT / PERCENTILE_DISC

Percentile calculation

PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY column)

SELECT 
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) as p95
FROM employees;

VARIANCE / STDDEV

Variance and standard deviation

VARIANCE(column) | STDDEV(column)

SELECT 
  VARIANCE(salary) as variance,
  VAR_POP(salary) as population_variance,
  VAR_SAMP(salary) as sample_variance,
  STDDEV(salary) as stddev,
  STDDEV_POP(salary) as pop_stddev
FROM employees;

📦GROUP BY Grouping

Grouping rows for aggregation

GROUP BY

Grouping rows by columns

SELECT ... FROM ... GROUP BY column1, column2, ...

SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;

HAVING

Filtering groups after aggregation

GROUP BY ... HAVING condition

SELECT department_id, AVG(salary) as avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

HAVING применяется после GROUP BY, WHERE - до

GROUPING SETS

Multiple groupings in a single query

GROUP BY GROUPING SETS ((cols1), (cols2), ...)

SELECT 
  department_id, job_title, 
  SUM(salary) as total_salary
FROM employees
GROUP BY GROUPING SETS (
  (department_id, job_title),
  (department_id),
  (job_title),
  ()
);

ROLLUP

Hierarchical grouping with subtotals

GROUP BY ROLLUP (col1, col2, ...)

SELECT 
  year, quarter, month,
  SUM(revenue) as total
FROM sales
GROUP BY ROLLUP (year, quarter, month);
-- Создает: (year,quarter,month), (year,quarter), (year), ()

CUBE

Full grouping across all combinations

GROUP BY CUBE (col1, col2, ...)

SELECT 
  region, product_category,
  SUM(sales) as total
FROM sales_data
GROUP BY CUBE (region, product_category);
-- Создает все комбинации: (r,p), (r), (p), ()

GROUPING()

Determining if NULL is a summary row

GROUPING(column)

SELECT 
  CASE WHEN GROUPING(department_id) = 1 
       THEN 'Всего' 
       ELSE department_id::text END as dept,
  SUM(salary)
FROM employees
GROUP BY ROLLUP (department_id);

🔗JOINs

Combining data from multiple tables

INNER JOIN

Returns rows with matches in both tables

SELECT ... FROM t1 INNER JOIN t2 ON condition

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

LEFT JOIN

All rows from left table + matches from right

SELECT ... FROM t1 LEFT [OUTER] JOIN t2 ON condition

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN

All rows from right table + matches from left

SELECT ... FROM t1 RIGHT [OUTER] JOIN t2 ON condition

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

FULL OUTER JOIN

All rows from both tables

SELECT ... FROM t1 FULL [OUTER] JOIN t2 ON condition

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

CROSS JOIN

Cartesian product (all combinations)

SELECT ... FROM t1 CROSS JOIN t2

SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

SELF JOIN

Joining a table with itself

SELECT ... FROM t1 a JOIN t1 b ON condition

SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

NATURAL JOIN

Automatic join on same-named columns

PGGP

SELECT ... FROM t1 NATURAL JOIN t2

SELECT * FROM orders
NATURAL JOIN customers;
-- Соединяет по всем столбцам с одинаковыми именами

USING

Join on a same-named column

PGGP

SELECT ... FROM t1 JOIN t2 USING (column)

SELECT * FROM orders
JOIN customers USING (customer_id);

LATERAL JOIN

Subquery with access to outer query columns

SELECT ... FROM t1, LATERAL (subquery) alias

SELECT d.name, top_emp.*
FROM departments d,
LATERAL (
  SELECT * FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC
  LIMIT 3
) top_emp;

CROSS APPLY / OUTER APPLY

MSSQL-specific LATERAL JOIN

MS

SELECT ... FROM t1 CROSS|OUTER APPLY (subquery)

-- PostgreSQL: используйте LATERAL
SELECT d.name, e.*
FROM departments d,
LATERAL (SELECT * FROM employees 
         WHERE dept_id = d.id LIMIT 1) e;

🎯Subqueries

Nested queries

Scalar subquery

Subquery returning a single value

SELECT (SELECT single_value FROM ...)

SELECT name, salary,
  (SELECT AVG(salary) FROM employees) as avg_salary,
  salary - (SELECT AVG(salary) FROM employees) as diff
FROM employees;

Derived table subquery

Subquery as a virtual table in FROM

SELECT ... FROM (subquery) alias

SELECT dept_name, avg_salary
FROM (
  SELECT d.name as dept_name, AVG(e.salary) as avg_salary
  FROM departments d
  JOIN employees e ON d.id = e.dept_id
  GROUP BY d.name
) dept_stats
WHERE avg_salary > 50000;

Correlated subquery

Subquery referencing the outer query

SELECT ... WHERE col = (SELECT ... WHERE outer.col = ...)

-- Сотрудники с зарплатой выше средней в их отделе
SELECT * FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees
  WHERE dept_id = e.dept_id
);

Выполняется для каждой строки внешнего запроса - может быть медленным

📝CTE (WITH)

Common Table Expressions - named subqueries

WITH (CTE)

Defining a named subquery

WITH cte_name AS (query) SELECT ... FROM cte_name

WITH dept_stats AS (
  SELECT dept_id, AVG(salary) as avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, ds.avg_sal
FROM employees e
JOIN dept_stats ds ON e.dept_id = ds.dept_id
WHERE e.salary > ds.avg_sal;

Multiple CTEs

Several CTEs in a single query

WITH cte1 AS (...), cte2 AS (...) SELECT ...

WITH 
  active_customers AS (
    SELECT * FROM customers WHERE is_active = true
  ),
  recent_orders AS (
    SELECT * FROM orders 
    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
  )
SELECT c.name, COUNT(o.id) as order_count
FROM active_customers c
LEFT JOIN recent_orders o ON c.id = o.customer_id
GROUP BY c.name;

WITH RECURSIVE

Recursive queries for hierarchies and graphs

WITH RECURSIVE cte AS (base UNION ALL recursive) SELECT ...

-- Иерархия сотрудников
WITH RECURSIVE emp_hierarchy AS (
  -- Базовый случай: топ-менеджеры
  SELECT id, name, manager_id, 1 as level
  FROM employees WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Рекурсивный случай
  SELECT e.id, e.name, e.manager_id, h.level + 1
  FROM employees e
  JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM emp_hierarchy ORDER BY level;

Sequence generation

Creating numeric/time series

WITH RECURSIVE seq AS (...) SELECT ...

-- Числа от 1 до 100
WITH RECURSIVE numbers AS (
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;

-- Даты за последний год
WITH RECURSIVE dates AS (
  SELECT CURRENT_DATE - INTERVAL '1 year' as d
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM dates 
  WHERE d < CURRENT_DATE
)
SELECT * FROM dates;

🪟Window Functions

Calculations over row groups without collapsing

OVER()

Basic window function syntax

function() OVER ([PARTITION BY ...] [ORDER BY ...])

SELECT name, department, salary,
  SUM(salary) OVER () as total_salary,
  SUM(salary) OVER (PARTITION BY department) as dept_salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

ROW_NUMBER()

Assigning a unique number to each row

ROW_NUMBER() OVER (ORDER BY column)

SELECT 
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank,
  name, salary
FROM employees;

-- Нумерация внутри групп
SELECT 
  ROW_NUMBER() OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as dept_rank,
  name, department, salary
FROM employees;

RANK() / DENSE_RANK()

Ranking with handling of ties

RANK() OVER (...) | DENSE_RANK() OVER (...)

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;
-- RANK: 1,2,2,4 (пропускает номера)
-- DENSE_RANK: 1,2,2,3 (не пропускает)

NTILE(n)

Splitting into n equal groups

NTILE(n) OVER (ORDER BY column)

-- Разбиение на квартили
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

LEAD() / LAG()

Accessing next/previous row

LEAD|LAG(column, offset, default) OVER (...)

SELECT order_date, amount,
  LAG(amount, 1, 0) OVER (ORDER BY order_date) as prev_amount,
  LEAD(amount, 1, 0) OVER (ORDER BY order_date) as next_amount,
  amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) as diff
FROM orders;

FIRST_VALUE() / LAST_VALUE()

First/last value in a window

FIRST_VALUE(col) OVER (...)

SELECT name, department, salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as top_earner,
  LAST_VALUE(name) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as lowest_earner
FROM employees;

ROWS / RANGE / GROUPS

Defining window boundaries

OVER (... ROWS BETWEEN start AND end)

SELECT date, amount,
  -- Скользящее среднее за 7 дней
  AVG(amount) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7d,
  
  -- Накопительная сумма
  SUM(amount) OVER (
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM sales;

PERCENT_RANK() / CUME_DIST()

Relative rank as percentage

PERCENT_RANK() OVER (...)

SELECT name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) as pct_rank,
  CUME_DIST() OVER (ORDER BY salary) as cume_dist
FROM employees;
-- PERCENT_RANK: (rank-1)/(count-1)
-- CUME_DIST: count(<=value)/count(*)

FILTER (WHERE)

Conditional aggregation in window functions

PGGP

aggregate FILTER (WHERE condition) OVER (...)

SELECT department, month, revenue,
  SUM(revenue) FILTER (WHERE revenue > 0) OVER (
    PARTITION BY department
  ) as positive_revenue_total,
  COUNT(*) FILTER (WHERE status = 'completed') OVER (
    PARTITION BY department
  ) as completed_count
FROM sales;

⊕Set Operations

Union, intersection, and difference of results

UNION / UNION ALL

Combining query results

SELECT ... UNION [ALL] SELECT ...

-- UNION: без дубликатов
SELECT name FROM customers
UNION
SELECT name FROM suppliers;

-- UNION ALL: с дубликатами (быстрее)
SELECT email FROM users
UNION ALL
SELECT email FROM newsletter_subscribers;

INTERSECT

Intersection of results (common rows)

SELECT ... INTERSECT SELECT ...

-- Клиенты, которые также являются поставщиками
SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers;

EXCEPT / MINUS

Set difference (rows in first but not second)

SELECT ... EXCEPT SELECT ...

-- Клиенты без заказов
SELECT customer_id FROM customers
EXCEPT
SELECT DISTINCT customer_id FROM orders;

❓Conditional Logic

Conditional expressions and NULL handling

CASE WHEN

Conditional expression

CASE WHEN cond1 THEN val1 ... ELSE default END

SELECT name, salary,
  CASE 
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 50000 THEN 'Middle'
    ELSE 'Junior'
  END as level
FROM employees;

Simple CASE

Simple value comparison

CASE expression WHEN val1 THEN res1 ... END

SELECT order_id,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    ELSE 'Unknown'
  END as status_name
FROM orders;

COALESCE

First non-NULL value from a list

COALESCE(val1, val2, ..., default)

SELECT 
  COALESCE(phone, mobile, email, 'No contact') as contact
FROM customers;

SELECT 
  COALESCE(discount, 0) as final_discount
FROM orders;

NULLIF

Returns NULL if values are equal

NULLIF(expression1, expression2)

-- Избежание деления на ноль
SELECT total / NULLIF(count, 0) as average
FROM stats;

-- Замена пустых строк на NULL
SELECT NULLIF(TRIM(name), '') as clean_name
FROM products;

GREATEST / LEAST

Maximum/minimum from a list of values

GREATEST(val1, val2, ...) | LEAST(val1, val2, ...)

SELECT 
  GREATEST(price1, price2, price3) as max_price,
  LEAST(stock1, stock2) as min_stock
FROM products;

IIF (MSSQL)

Short CASE form for MSSQL

MS

IIF(condition, true_value, false_value)

-- PostgreSQL: используйте CASE
SELECT 
  CASE WHEN stock > 0 THEN 'In Stock' ELSE 'Out of Stock' END
FROM products;

📝String Functions

Working with strings

CONCAT / ||

String concatenation

CONCAT(str1, str2, ...) | str1 || str2

SELECT first_name || ' ' || last_name as full_name
FROM employees;

SELECT CONCAT(first_name, ' ', last_name) as full_name
FROM employees;

LENGTH / LEN

String length

LENGTH(string) | LEN(string)

SELECT name, LENGTH(name) as name_length
FROM products;

SELECT CHAR_LENGTH(description) as char_count
FROM articles;

SUBSTRING / SUBSTR

Extracting a substring

SUBSTRING(string FROM start [FOR length])

SELECT SUBSTRING(phone FROM 1 FOR 3) as area_code
FROM customers;

SELECT SUBSTR(email, 1, POSITION('@' IN email) - 1) as username
FROM users;

LEFT / RIGHT

First/last n characters

LEFT(string, n) | RIGHT(string, n)

SELECT LEFT(name, 10) as short_name,
  RIGHT(phone, 4) as last_four
FROM customers;

TRIM / LTRIM / RTRIM

Removing spaces and characters

TRIM([chars FROM] string)

SELECT TRIM('  hello  ') as trimmed;
SELECT LTRIM('  hello') as left_trimmed;
SELECT RTRIM('hello  ') as right_trimmed;

-- Удаление конкретных символов
SELECT TRIM(BOTH '-' FROM '--hello--') as trimmed;

UPPER / LOWER / INITCAP

Case conversion

UPPER(string) | LOWER(string) | INITCAP(string)

SELECT 
  UPPER(name) as upper_name,
  LOWER(email) as lower_email,
  INITCAP(title) as title_case
FROM users;

REPLACE

Substring replacement

REPLACE(string, from, to)

SELECT REPLACE(phone, '-', '') as clean_phone
FROM customers;

SELECT REPLACE(REPLACE(text, '
', ' '), '	', ' ')
FROM documents;

POSITION / CHARINDEX

Finding substring position

POSITION(substring IN string)

SELECT POSITION('@' IN email) as at_position
FROM users;

SELECT STRPOS(url, '://') as protocol_end
FROM links;

LPAD / RPAD

Padding a string to a given length

LPAD(string, length, fill)

SELECT LPAD(CAST(id AS TEXT), 6, '0') as padded_id
FROM orders;  -- '000123'

SELECT RPAD(name, 20, '.') as formatted_name
FROM products;

SPLIT_PART / STRING_SPLIT

Splitting a string by delimiter

SPLIT_PART(string, delimiter, part)

SELECT SPLIT_PART(email, '@', 1) as username,
  SPLIT_PART(email, '@', 2) as domain
FROM users;

-- Разворачивание в строки
SELECT id, tag
FROM products, 
  UNNEST(STRING_TO_ARRAY(tags, ',')) as tag;

Regular expressions

Working with regular expressions

column ~ pattern | REGEXP_REPLACE(...)

-- Проверка паттерна
SELECT * FROM users WHERE email ~ '^[a-z]+@';

-- Замена
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') as digits_only
FROM customers;

-- Извлечение
SELECT REGEXP_MATCHES(log, '\d{4}-\d{2}-\d{2}')
FROM logs;

🔢Numeric Functions

Mathematical operations

ROUND

Rounding a number

ROUND(number, decimals)

SELECT ROUND(price, 2) as rounded_price
FROM products;

SELECT ROUND(123.456, 0);  -- 123
SELECT ROUND(123.456, -1); -- 120

CEIL / FLOOR

Rounding up/down

CEIL(number) | FLOOR(number)

SELECT CEIL(4.2) as ceiling,   -- 5
  FLOOR(4.8) as floor_val;     -- 4

SELECT CEIL(price) as min_whole_price
FROM products;

ABS

Absolute value

ABS(number)

SELECT ABS(-15) as absolute;  -- 15

SELECT * FROM transactions
WHERE ABS(amount) > 1000;

MOD / %

Remainder of division

MOD(dividend, divisor) | dividend % divisor

SELECT MOD(17, 5) as remainder;  -- 2
SELECT 17 % 5 as remainder;       -- 2

-- Четные/нечетные
SELECT * FROM products
WHERE id % 2 = 0;

POWER / SQRT

Power and square root

POWER(base, exponent) | SQRT(number)

SELECT POWER(2, 10) as two_to_ten;  -- 1024
SELECT SQRT(144) as square_root;    -- 12

SELECT POWER(base, 2) as squared,
  SQRT(value) as root
FROM numbers;

LOG / LN / EXP

Logarithms and exponent

LOG(number) | LN(number) | EXP(number)

SELECT LN(2.718281828) as natural_log;  -- ~1
SELECT LOG(10, 100) as log_base_10;     -- 2
SELECT EXP(1) as e;                      -- ~2.718

RANDOM / RAND

Random number

RANDOM() | RAND()

SELECT RANDOM();  -- 0.0 до 1.0

-- Случайное целое от 1 до 100
SELECT FLOOR(RANDOM() * 100 + 1)::int;

-- Случайная выборка
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 5;

TRUNC / TRUNCATE

Truncating a number (without rounding)

TRUNC(number, decimals)

SELECT TRUNC(123.789, 2);  -- 123.78
SELECT TRUNC(123.789, 0);  -- 123
SELECT TRUNC(123.789, -1); -- 120

📅Date and Time

Working with dates and time

CURRENT_DATE / NOW

Current date and time

CURRENT_DATE | CURRENT_TIMESTAMP | NOW()

SELECT CURRENT_DATE;        -- 2024-03-15
SELECT CURRENT_TIME;        -- 10:30:45
SELECT CURRENT_TIMESTAMP;   -- 2024-03-15 10:30:45
SELECT NOW();               -- То же что CURRENT_TIMESTAMP

EXTRACT / DATE_PART

Extracting a date component

EXTRACT(part FROM date)

SELECT 
  EXTRACT(YEAR FROM order_date) as year,
  EXTRACT(MONTH FROM order_date) as month,
  EXTRACT(DAY FROM order_date) as day,
  EXTRACT(DOW FROM order_date) as day_of_week,
  EXTRACT(EPOCH FROM created_at) as unix_timestamp
FROM orders;

DATE_TRUNC

Truncating date to specified precision

DATE_TRUNC('precision', date)

SELECT 
  DATE_TRUNC('month', order_date) as month_start,
  DATE_TRUNC('year', order_date) as year_start,
  DATE_TRUNC('hour', created_at) as hour_start
FROM orders;

-- Группировка по месяцам
SELECT DATE_TRUNC('month', order_date), COUNT(*)
FROM orders
GROUP BY 1;

Adding an interval

Adding a period to a date

date + INTERVAL | DATEADD(part, n, date)

SELECT 
  order_date + INTERVAL '7 days' as week_later,
  order_date + INTERVAL '1 month' as month_later,
  order_date - INTERVAL '1 year' as year_ago
FROM orders;

SELECT NOW() + INTERVAL '2 hours 30 minutes';

Date difference

Calculating difference between dates

date1 - date2 | DATEDIFF(part, start, end)

SELECT shipped_date - order_date as days_to_ship
FROM orders;

SELECT AGE(NOW(), hire_date) as tenure
FROM employees;

SELECT EXTRACT(EPOCH FROM (end_time - start_time)) as seconds
FROM events;

Date formatting

Converting date to string

TO_CHAR(date, format) | FORMAT(date, format)

SELECT 
  TO_CHAR(order_date, 'DD.MM.YYYY') as formatted,
  TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') as iso,
  TO_CHAR(order_date, 'Day, DD Month YYYY') as full_date
FROM orders;

Creating a date

Creating a date from components

MAKE_DATE(year, month, day)

SELECT MAKE_DATE(2024, 3, 15);
SELECT MAKE_TIMESTAMP(2024, 3, 15, 10, 30, 0);

SELECT MAKE_DATE(year, 1, 1) as year_start
FROM (SELECT DISTINCT EXTRACT(YEAR FROM order_date)::int as year 
      FROM orders) y;

AT TIME ZONE

Working with time zones

timestamp AT TIME ZONE 'zone'

SELECT 
  created_at,
  created_at AT TIME ZONE 'UTC' as utc,
  created_at AT TIME ZONE 'Europe/Moscow' as moscow
FROM events;

-- Конвертация
SELECT timestamp '2024-03-15 10:00:00' AT TIME ZONE 'America/New_York';

🔄Data Types and Conversions

Data type conversion

CAST

Standard type conversion

CAST(expression AS type)

SELECT CAST('123' AS INTEGER);
SELECT CAST(price AS NUMERIC(10,2));
SELECT CAST(created_at AS DATE);

:: (PostgreSQL)

Short CAST form for PostgreSQL

PGGP

expression::type

SELECT '123'::integer;
SELECT price::numeric(10,2);
SELECT '2024-03-15'::date;
SELECT row_to_json(t)::text FROM table t;

CONVERT (MSSQL)

MSSQL-specific conversion with styles

MS

CONVERT(type, expression [, style])

-- PostgreSQL: используйте CAST или TO_CHAR
SELECT TO_CHAR(date, 'DD.MM.YYYY');

TRY_CAST / TRY_CONVERT

Safe conversion (NULL on error)

MS

TRY_CAST(expression AS type)

-- PostgreSQL: функция-обертка или обработка ошибок
CREATE OR REPLACE FUNCTION try_cast_int(text)
RETURNS integer AS $$
BEGIN
  RETURN $1::integer;
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

📋JSON Functions

Working with JSON data

Extracting from JSON

Getting values from JSON

json->'key' | json->>'key' | JSON_VALUE()

-- -> возвращает JSON, ->> возвращает текст
SELECT 
  data->'name' as json_name,
  data->>'name' as text_name,
  data->'address'->>'city' as city
FROM users;

-- #> для пути, #>> для текста
SELECT data#>>'{address,city}' as city FROM users;

Creating JSON

Creating JSON objects

JSON_BUILD_OBJECT(k1, v1, ...) | JSON_OBJECT()

SELECT JSON_BUILD_OBJECT(
  'id', id,
  'name', name,
  'tags', JSON_BUILD_ARRAY('tag1', 'tag2')
) FROM products;

SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name))
FROM products;

JSONB operators

Checking JSONB contents (PostgreSQL)

PGGP

jsonb @> jsonb | jsonb ? key

-- @> содержит
SELECT * FROM products
WHERE tags @> '["electronics"]';

-- ? содержит ключ
SELECT * FROM users
WHERE profile ? 'premium';

-- ?& все ключи, ?| любой ключ
SELECT * FROM data WHERE info ?& ARRAY['a','b'];

Unwinding JSON

Converting JSON to rows/columns

JSONB_EACH() | OPENJSON()

-- Ключи и значения
SELECT key, value FROM users,
  JSONB_EACH(profile);

-- Массив в строки
SELECT id, elem
FROM products,
  JSONB_ARRAY_ELEMENTS(tags) as elem;

✏️DML (Data Modification)

INSERT, UPDATE, DELETE, MERGE

INSERT VALUES

Inserting rows with explicit values

INSERT INTO table (cols) VALUES (vals), ...

INSERT INTO products (name, price, category)
VALUES 
  ('Product A', 99.99, 'Electronics'),
  ('Product B', 49.99, 'Books');

INSERT SELECT

Inserting from query results

INSERT INTO table (cols) SELECT ...

INSERT INTO archive_orders (id, customer, total)
SELECT id, customer_id, total_amount
FROM orders
WHERE order_date < '2023-01-01';

UPSERT (ON CONFLICT)

Insert or update on conflict

INSERT ... ON CONFLICT ... DO UPDATE/NOTHING

INSERT INTO products (sku, name, price)
VALUES ('SKU123', 'Widget', 29.99)
ON CONFLICT (sku) 
DO UPDATE SET 
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = NOW();

-- Игнорировать конфликт
INSERT INTO logs (id, message)
VALUES (1, 'test')
ON CONFLICT DO NOTHING;

UPDATE

Updating existing rows

UPDATE table SET col = val [WHERE ...]

UPDATE products
SET price = price * 1.1,
    updated_at = NOW()
WHERE category = 'Electronics';

-- UPDATE с JOIN
UPDATE orders o
SET status = 'VIP'
FROM customers c
WHERE o.customer_id = c.id AND c.is_vip = true;

DELETE

Deleting rows

DELETE FROM table [WHERE ...]

DELETE FROM sessions
WHERE expires_at < NOW();

-- DELETE с USING (JOIN)
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id AND o.status = 'cancelled';

RETURNING / OUTPUT

Returning modified rows

INSERT/UPDATE/DELETE ... RETURNING cols

INSERT INTO users (email, name)
VALUES ('test@test.com', 'Test')
RETURNING id, created_at;

UPDATE products
SET price = price * 0.9
WHERE category = 'Sale'
RETURNING id, name, price as new_price;

DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING user_id;

TRUNCATE

Fast deletion of all rows

TRUNCATE TABLE table_name [CASCADE]

TRUNCATE TABLE logs;

-- С каскадом для зависимых таблиц
TRUNCATE TABLE orders CASCADE;

-- Сброс sequence
TRUNCATE TABLE users RESTART IDENTITY;

Быстрее DELETE, но не вызывает триггеры

🏗️DDL (Data Definition)

CREATE, ALTER, DROP tables and objects

CREATE TABLE

Creating a table

CREATE TABLE name (column type [constraints], ...)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  is_active BOOLEAN DEFAULT true
);

CREATE TABLE AS (CTAS)

Creating a table from query results

CREATE TABLE new_table AS SELECT ...

CREATE TABLE active_users AS
SELECT id, email, name
FROM users
WHERE is_active = true;

ALTER TABLE

Modifying table structure

ALTER TABLE name ADD/DROP/ALTER COLUMN ...

-- Добавление столбца
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Удаление столбца
ALTER TABLE users DROP COLUMN phone;

-- Изменение типа
ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(12,2);

-- Установка NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

DROP TABLE

Deleting a table

DROP TABLE [IF EXISTS] name [CASCADE]

DROP TABLE IF EXISTS temp_data;
DROP TABLE orders CASCADE;  -- Удаляет зависимые объекты

CREATE INDEX

Creating an index

CREATE [UNIQUE] INDEX name ON table (columns)

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_products_sku ON products (sku);

-- Частичный индекс
CREATE INDEX idx_active_users ON users (id) WHERE is_active = true;

-- Составной индекс
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);

CREATE VIEW

Creating a view

CREATE [OR REPLACE] VIEW name AS SELECT ...

CREATE OR REPLACE VIEW active_orders AS
SELECT o.*, c.name as customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status != 'cancelled';

🔒Constraints

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK

PRIMARY KEY

Table primary key

column type PRIMARY KEY | CONSTRAINT pk PRIMARY KEY (cols)

-- При создании таблицы
CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

-- Составной первичный ключ
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  PRIMARY KEY (order_id, product_id)
);

-- Добавление к существующей таблице
ALTER TABLE users ADD PRIMARY KEY (id);

FOREIGN KEY

Foreign key (reference to another table)

FOREIGN KEY (col) REFERENCES table(col) [ON DELETE ...]

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

-- Добавление FK
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

UNIQUE

Value uniqueness

column type UNIQUE | CONSTRAINT uq UNIQUE (cols)

CREATE TABLE users (
  email VARCHAR(255) UNIQUE
);

-- Составной уникальный ключ
ALTER TABLE employee_assignments
ADD CONSTRAINT uq_emp_project 
UNIQUE (employee_id, project_id);

CHECK

Condition check for values

CHECK (condition)

CREATE TABLE products (
  price DECIMAL CHECK (price > 0),
  quantity INT CHECK (quantity >= 0),
  CONSTRAINT valid_discount CHECK (discount BETWEEN 0 AND 100)
);

NOT NULL / DEFAULT

Required field and default value

column type NOT NULL DEFAULT value

CREATE TABLE users (
  email VARCHAR(255) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;

🔐Transactions

BEGIN, COMMIT, ROLLBACK, isolation levels

BEGIN / COMMIT / ROLLBACK

Transaction management

BEGIN; ... COMMIT; | ROLLBACK;

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- При ошибке
BEGIN;
  INSERT INTO orders (...) VALUES (...);
  -- Если что-то пошло не так
ROLLBACK;

SAVEPOINT

Savepoints within a transaction

SAVEPOINT name; ROLLBACK TO name;

BEGIN;
  INSERT INTO orders (...) VALUES (...);
  SAVEPOINT order_created;
  
  INSERT INTO order_items (...) VALUES (...);
  -- Ошибка в items
  ROLLBACK TO order_created;
  
  -- Продолжаем с другими данными
  INSERT INTO order_items (...) VALUES (...);
COMMIT;

Isolation levels

Controlling visibility of changes between transactions

SET TRANSACTION ISOLATION LEVEL ...

-- Уровни: READ UNCOMMITTED, READ COMMITTED (default), 
--         REPEATABLE READ, SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  SELECT * FROM accounts WHERE id = 1;
  -- Гарантированно не изменится до COMMIT
COMMIT;

-- Или при старте
BEGIN ISOLATION LEVEL REPEATABLE READ;

FOR UPDATE / FOR SHARE

Locking rows when reading

SELECT ... FOR UPDATE [NOWAIT|SKIP LOCKED]

-- Эксклюзивная блокировка (для UPDATE/DELETE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Разделяемая блокировка (для чтения)
SELECT * FROM products WHERE id = 1 FOR SHARE;

-- Не ждать, если заблокировано
SELECT * FROM inventory WHERE sku = 'ABC' FOR UPDATE NOWAIT;

-- Пропустить заблокированные (для очередей)
SELECT * FROM tasks WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED LIMIT 1;

⚙️System Functions

Metadata, version, current user

VERSION()

DBMS version

SELECT VERSION();

SELECT VERSION();
-- PostgreSQL 15.2 on x86_64-pc-linux-gnu...

CURRENT_USER

Current user

SELECT CURRENT_USER;

SELECT CURRENT_USER;
SELECT SESSION_USER;
SELECT CURRENT_DATABASE();
SELECT CURRENT_SCHEMA();

EXPLAIN

Query execution plan

EXPLAIN [ANALYZE] query

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

-- С реальным выполнением
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;

-- Подробный вывод
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 1;

information_schema

Standard metadata views

SELECT * FROM information_schema.tables

-- Список таблиц
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public';

-- Столбцы таблицы
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users';

∅NULL Handling

Working with NULL values

IS NULL / IS NOT NULL

Checking for NULL values

WHERE column IS [NOT] NULL

SELECT * FROM employees
WHERE manager_id IS NULL;  -- топ-менеджеры

SELECT * FROM orders
WHERE shipped_date IS NOT NULL;  -- отгруженные

NULL != NULL, нельзя сравнивать через = или <>

COALESCE

First non-NULL value from a list

COALESCE(val1, val2, ..., default)

SELECT 
  COALESCE(phone, mobile, email, 'Нет контакта') as contact
FROM customers;

-- Замена NULL на 0
SELECT COALESCE(discount, 0) as discount FROM orders;

NULLIF

Returns NULL if values are equal

NULLIF(expression1, expression2)

-- Защита от деления на ноль
SELECT total / NULLIF(count, 0) as average
FROM stats;

-- Пустая строка → NULL
SELECT NULLIF(TRIM(name), '') as clean_name
FROM products;

ISNULL / NVL

Replacing NULL with a specified value

ISNULL(expr, replacement) | NVL(expr, replacement)

-- PostgreSQL: используйте COALESCE
SELECT COALESCE(phone, 'N/A') FROM customers;

IS [NOT] DISTINCT FROM

NULL-safe comparison (NULL = NULL → true)

PGGP

a IS [NOT] DISTINCT FROM b

-- Стандартное сравнение: NULL = NULL → NULL (false)
-- IS NOT DISTINCT FROM: NULL = NULL → true

SELECT * FROM t1
WHERE col1 IS NOT DISTINCT FROM col2;

-- Эквивалент:
-- WHERE col1 = col2 OR (col1 IS NULL AND col2 IS NULL)

NULLS FIRST / LAST

Controlling NULL position when sorting

PGGP

ORDER BY col [ASC|DESC] NULLS [FIRST|LAST]

-- NULL в начале
SELECT * FROM employees
ORDER BY manager_id NULLS FIRST;

-- NULL в конце (по умолчанию для ASC)
SELECT * FROM orders
ORDER BY shipped_date DESC NULLS LAST;

📦Arrays

Working with arrays (PostgreSQL/Greenplum)

ARRAY[]

Создание массива

PGGP

ARRAY[val1, val2, ...] | '{val1,val2}'

SELECT ARRAY[1, 2, 3];
SELECT ARRAY['a', 'b', 'c'];
SELECT '{1,2,3}'::int[];

-- Многомерный массив
SELECT ARRAY[[1,2], [3,4]];

ARRAY_AGG

Aggregating values into an array

ARRAY_AGG(expression [ORDER BY ...])

SELECT department_id,
  ARRAY_AGG(employee_name ORDER BY hire_date) as employees
FROM employees
GROUP BY department_id;

-- С DISTINCT
SELECT ARRAY_AGG(DISTINCT category) FROM products;

UNNEST

Expanding an array into rows

PGGP

UNNEST(array)

SELECT UNNEST(ARRAY[1,2,3]) as num;

-- С сохранением индекса
SELECT * FROM UNNEST(ARRAY['a','b','c']) 
  WITH ORDINALITY as t(val, idx);

-- Разворачивание столбца
SELECT id, UNNEST(tags) as tag
FROM products;

Element access

Getting array elements by index

PGGP

array[index] | array[start:end]

SELECT (ARRAY['a','b','c'])[1];  -- 'a' (индексы с 1)
SELECT (ARRAY['a','b','c'])[2:3]; -- {'b','c'}

-- Для столбца
SELECT tags[1] as first_tag FROM products;

Array functions

Array operations

PGGP

ARRAY_LENGTH, ARRAY_CAT, ARRAY_APPEND, ...

-- Длина
SELECT ARRAY_LENGTH(ARRAY[1,2,3], 1);  -- 3
SELECT CARDINALITY(ARRAY[1,2,3]);      -- 3

-- Конкатенация
SELECT ARRAY[1,2] || ARRAY[3,4];       -- {1,2,3,4}
SELECT ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]);

-- Добавление элементов
SELECT ARRAY_APPEND(ARRAY[1,2], 3);    -- {1,2,3}
SELECT ARRAY_PREPEND(0, ARRAY[1,2]);   -- {0,1,2}

-- Удаление
SELECT ARRAY_REMOVE(ARRAY[1,2,2,3], 2); -- {1,3}

-- Позиция элемента
SELECT ARRAY_POSITION(ARRAY['a','b','c'], 'b'); -- 2

Array operators

Array comparison and checking

PGGP

@> | <@ | && | = | <>

-- Содержит
SELECT ARRAY[1,2,3] @> ARRAY[2,3];  -- true

-- Содержится в
SELECT ARRAY[2] <@ ARRAY[1,2,3];   -- true

-- Пересечение (есть общие элементы)
SELECT ARRAY[1,2] && ARRAY[2,3];   -- true

-- ANY: элемент в массиве
SELECT * FROM products
WHERE 'electronics' = ANY(tags);

-- ALL: все элементы удовлетворяют
SELECT * FROM data
WHERE 100 > ALL(values);

ARRAY_TO_STRING / STRING_TO_ARRAY

Converting between array and string

ARRAY_TO_STRING(array, delimiter)

-- Массив → строка
SELECT ARRAY_TO_STRING(ARRAY['a','b','c'], ', ');
-- 'a, b, c'

-- Строка → массив
SELECT STRING_TO_ARRAY('a,b,c', ',');
-- {a,b,c}

-- С NULL обработкой
SELECT ARRAY_TO_STRING(ARRAY['a',NULL,'c'], ',', 'N/A');
-- 'a,N/A,c'

⚡Indexes

Creating and managing indexes

B-tree index

Standard index for comparisons and sorting

CREATE INDEX name ON table (columns)

CREATE INDEX idx_users_email ON users (email);

-- Составной индекс
CREATE INDEX idx_orders_customer_date 
ON orders (customer_id, order_date DESC);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_products_sku ON products (sku);

Partial / Filtered index

Index for a subset of rows only

CREATE INDEX ... WHERE condition

-- Индекс только для активных пользователей
CREATE INDEX idx_active_users 
ON users (email) 
WHERE is_active = true;

-- Индекс для необработанных заказов
CREATE INDEX idx_pending_orders 
ON orders (created_at) 
WHERE status = 'pending';

Expression index

Index on a computed expression

CREATE INDEX ... ON table (expression)

-- Индекс по нижнему регистру
CREATE INDEX idx_users_email_lower 
ON users (LOWER(email));

-- Индекс по году
CREATE INDEX idx_orders_year 
ON orders (EXTRACT(YEAR FROM order_date));

-- Использование:
SELECT * FROM users WHERE LOWER(email) = 'test@test.com';

Covering index (INCLUDE)

Index with extra columns for index-only scan

CREATE INDEX ... INCLUDE (columns)

-- Покрывающий индекс: все нужные данные в индексе
CREATE INDEX idx_orders_customer 
ON orders (customer_id) 
INCLUDE (order_date, total_amount);

-- Запрос использует только индекс:
SELECT order_date, total_amount 
FROM orders 
WHERE customer_id = 123;

Index types

Specialized index types

CREATE INDEX ... USING method

-- Hash: только для равенства
CREATE INDEX idx_hash ON users USING hash (email);

-- GIN: для массивов, JSONB, полнотекстового поиска
CREATE INDEX idx_tags ON products USING gin (tags);
CREATE INDEX idx_data ON docs USING gin (data jsonb_path_ops);

-- GiST: для геометрии, диапазонов, полнотекста
CREATE INDEX idx_location ON places USING gist (coordinates);

-- BRIN: для больших таблиц с естественной сортировкой
CREATE INDEX idx_created ON logs USING brin (created_at);

CONCURRENTLY

Creating an index without locking the table

PGMS

CREATE INDEX CONCURRENTLY ...

-- Без блокировки записи (но медленнее)
CREATE INDEX CONCURRENTLY idx_users_email 
ON users (email);

-- Удаление без блокировки
DROP INDEX CONCURRENTLY idx_users_email;

Index maintenance

Rebuilding and analyzing indexes

REINDEX | REBUILD | ANALYZE

-- Перестроение индекса
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- Обновление статистики
ANALYZE users;

-- Проверка использования индексов
SELECT * FROM pg_stat_user_indexes 
WHERE relname = 'users';

🔀Dialect Specifics

Unique capabilities of each DBMS

PostgreSQL specifics

Unique PostgreSQL features

PGGP

Различные PostgreSQL-only возможности

-- DISTINCT ON
SELECT DISTINCT ON (department_id) *
FROM employees ORDER BY department_id, salary DESC;

-- FILTER в агрегатах
SELECT 
  COUNT(*) FILTER (WHERE status = 'active') as active,
  COUNT(*) FILTER (WHERE status = 'pending') as pending
FROM users;

-- RETURNING
INSERT INTO users (email) VALUES ('test@test.com')
RETURNING id, created_at;

-- Upsert
INSERT INTO products (sku, name) VALUES ('A1', 'Product')
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name;

-- Generate series
SELECT generate_series(1, 10);
SELECT generate_series('2024-01-01'::date, '2024-12-31', '1 month');

-- Lateral join
SELECT d.*, e.* FROM departments d,
LATERAL (SELECT * FROM employees 
         WHERE dept_id = d.id LIMIT 3) e;

Greenplum specifics

Unique Greenplum MPP features

GP

DISTRIBUTED BY, партиционирование, внешние таблицы

-- См. PostgreSQL специфика

MS SQL Server specifics

Unique MS SQL Server features

MS

TOP, APPLY, PIVOT, временные таблицы

-- См. PostgreSQL специфика

Syntax differences

Main syntax differences table

Сравнение синтаксиса между диалектами

-- Ограничение строк
SELECT * FROM t LIMIT 10 OFFSET 20;
SELECT * FROM t FETCH FIRST 10 ROWS ONLY;

-- Конкатенация
SELECT 'Hello' || ' ' || 'World';
SELECT CONCAT('Hello', ' ', 'World');

-- Текущее время
SELECT NOW(), CURRENT_TIMESTAMP;

-- Автоинкремент
CREATE TABLE t (id SERIAL PRIMARY KEY);
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY);

-- Boolean
SELECT * FROM t WHERE flag = true;
SELECT * FROM t WHERE flag IS TRUE;

-- Комментарии
-- однострочный
/* многострочный */

Complete ReferenceSQL commands and functions

📋SELECT Basics

SELECT

SELECT *

SELECT DISTINCT

DISTINCT ON

LIMIT / TOP / FETCH

AS (Aliases)

SELECT INTO

🔍WHERE Filtering

WHERE

Comparison Operators

AND / OR / NOT

IN / NOT IN

BETWEEN

LIKE / ILIKE

IS NULL / IS NOT NULL

IS DISTINCT FROM

EXISTS / NOT EXISTS

ANY / ALL / SOME

↕️ORDER BY Sorting

ORDER BY

NULLS FIRST / LAST

ORDER BY with expressions

COLLATE

📊Aggregate Functions

COUNT

SUM

AVG

MIN / MAX

STRING_AGG / LISTAGG

ARRAY_AGG

BOOL_AND / BOOL_OR

PERCENTILE_CONT / PERCENTILE_DISC

VARIANCE / STDDEV

📦GROUP BY Grouping

GROUP BY

HAVING

GROUPING SETS

ROLLUP

CUBE

GROUPING()

🔗JOINs

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

NATURAL JOIN

USING

LATERAL JOIN

CROSS APPLY / OUTER APPLY

🎯Subqueries

Scalar subquery

Derived table subquery

Correlated subquery

📝CTE (WITH)

WITH (CTE)

Multiple CTEs

WITH RECURSIVE

Sequence generation

🪟Window Functions

OVER()

ROW_NUMBER()

RANK() / DENSE_RANK()

NTILE(n)

LEAD() / LAG()

FIRST_VALUE() / LAST_VALUE()

ROWS / RANGE / GROUPS

PERCENT_RANK() / CUME_DIST()

FILTER (WHERE)

⊕Set Operations

UNION / UNION ALL

INTERSECT

EXCEPT / MINUS

❓Conditional Logic

CASE WHEN

Simple CASE

COALESCE