Перейти к основному содержимому

9. PostgreSQL

Что такое PostgreSQL и чем она отличается от MySQL?​

Junior Ответ: PostgreSQL — это мощная объектно-реляционная система управления базами данных (ORDBMS) с открытым исходным кодом. В отличие от MySQL, которая является чисто реляционной, PostgreSQL поддерживает расширенные типы данных (такие как массивы и геометрические типы) и известна своим строгим соответствием стандартам SQL и расширяемостью. Сравнение MySQL и PostgreSQL

Какие основные числовые типы данных есть в PostgreSQL?​

Junior Ответ:

  • целые числа: smallint (2 байта), integer (4 байта), bigint (8 байт).
  • числа с плавающей точкой: real (4 байта), double precision (8 байт).
  • числа с фиксированной точкой: numeric или decimal (точная точность для финансовых данных).
  • автоинкремент: serial, smallserial, bigserial. Подробные типы данных PostgreSQL

В чём разница между VARCHAR(n), CHAR(n) и TEXT в PostgreSQL?​

Junior Ответ:

  • CHAR(n): фиксированная длина, дополняется пробелами.
  • VARCHAR(n): переменная длина с максимальным ограничением.
  • TEXT: переменная длина без конкретного ограничения. В PostgreSQL нет разницы в производительности между этими тремя типами, поэтому для гибкости часто предпочитают TEXT или VARCHAR (без указания n).

Как определить первичный ключ (Primary Key) в PostgreSQL?​

Junior Ответ: Вы можете использовать PRIMARY KEY на уровне столбца или таблицы. Он однозначно идентифицирует каждую строку и не может быть NULL.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL
);

Что такое внешний ключ (Foreign Key) и как он используется?​

Junior Ответ: Внешний ключ связывает столбец в одной таблице с первичным ключом другой таблицы. Он обеспечивает ссылочную целостность.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

В чём разница между INNER JOIN и LEFT JOIN?​

Junior Ответ: INNER JOIN возвращает только совпадающие строки из обеих таблиц. LEFT JOIN возвращает все строки из левой таблицы и совпадающие строки из правой таблицы (с NULL, если совпадений нет). Связи между таблицами в PostgreSQL

Что такое Представление (View) и зачем оно используется?​

Junior Ответ: Представление — это виртуальная таблица, созданная на основе SQL-запроса. Оно не хранит данные само по себе, а отображает данные из базовых таблиц. Используется для упрощения сложных запросов, повышения безопасности (путем скрытия конфиденциальных колонок) и обеспечения единого интерфейса для пользователей. Подробное руководство по представлениям

Что такое ограничения CHECK и UNIQUE?​

Middle Ответ:

  • UNIQUE: гарантирует, что все значения в столбце (или группе столбцов) уникальны.
  • CHECK: гарантирует, что значения в столбце удовлетворяют определённому логическому условию (например, price > 0). Ограничения в PostgreSQL

Что такое массивы (Arrays) в PostgreSQL?​

Middle Ответ: PostgreSQL позволяет столбцам хранить массивы любого встроенного или пользовательского базового типа.

CREATE TABLE posts (
    tags TEXT[]
);
INSERT INTO posts (tags) VALUES ('{"php", "postgres"}');

Сложные структуры данных в PostgreSQL

Что такое тип ENUM в PostgreSQL?​

Middle Ответ: ENUM (перечисление) — это тип данных, который представляет собой статический упорядоченный набор значений.

CREATE TYPE status AS ENUM ('pending', 'active', 'closed');

Сложные структуры данных в PostgreSQL

В чём разница между WHERE и HAVING в PostgreSQL?​

Middle Ответ: WHERE фильтрует строки до группировки/агрегации. HAVING фильтрует результаты после применения GROUP BY.

Что такое агрегатные функции в PostgreSQL?​

Middle Ответ: Функции, которые вычисляют единый результат на основе набора входных значений, такие как COUNT(), SUM(), AVG(), MIN(), MAX() и STRING_AGG().

Что такое материализованное представление (Materialized View) и чем оно отличается от обычного представления?​

Middle Ответ: Обычное Представление (View) — это виртуальная таблица, которая выполняет запрос заново при каждом обращении. Материализованное представление сохраняет фактические результаты запроса на диске (как таблицу). Это дает значительный прирост производительности для сложных запросов, но требует ручного или запланированного обновления для поддержания актуальности данных. Подробное руководство по представлениям

Что такое MVCC (Multi-Version Concurrency Control) в PostgreSQL?​

Senior Ответ: MVCC позволяет нескольким транзакциям видеть согласованный снимок базы данных, не блокируя друг друга. Он использует скрытые столбцы, такие как xmin and xmax, для отслеживания версий строк.

Что такое коррелирующие подзапросы (Correlated Subqueries)?​

Senior Ответ: Подзапрос, который ссылается на столбцы внешнего запроса. Он выполняется один раз для каждой строки, обрабатываемой внешним запросом.

SELECT name, (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category)
FROM products p1;

Как работают GROUPING SETS, ROLLUP и CUBE?​

Senior Ответ: Это расширения GROUP BY для создания нескольких наборов группировок в одном запросе (полезно для отчётов и аналитики).

  • ROLLUP: генерирует иерархические промежуточные итоги.
  • CUBE: генерирует промежуточные итоги для всех возможных комбинаций столбцов.
  • GROUPING SETS: позволяет указать точные наборы столбцов для группировки.

Как можно изменить структуру таблицы с помощью ALTER TABLE?​

Senior Ответ: Вы можете добавлять/удалять столбцы, изменять типы и добавлять/удалять ограничения.

ALTER TABLE users ADD COLUMN email TEXT UNIQUE;
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;