Занятие №1

Реляционная алгебра

В дальнейшем в курсе пойдёт речь о работе с таблицами. В таблице мы можем хранить информацию.

Рассмотрим, например, такую таблицу с некоторой информацией про студентов:

Каждая строка описывает некоторого студента.
Каждый столбец обозначает некоторое свойство - ФИО, возраст, средний балл.
В столбце со средним баллом ожидаем дробное число;
В столбце Major ожидаем название предмета.

Для формализации правил работы с таблицами вводится реляционная алгебра.
С некоторыми допущениями будем частично отождествлять понятия из реляционной алгебры и понятия, известные нам из работы с таблицами.

Основные понятия реляционной алгебры

Реляционная алгебра представляет собой теоретический язык для работы с отношениями в реляционных базах данных. Она основана на теории множеств и включает набор операций, позволяющих манипулировать данными. Ключевые понятия и операции реляционной алгебры включают следующие элементы:

Основные термины

• Сущность: Объект предметной области, который моделируется в базе данных.

• Кортеж: Экземпляр сущности, (строка в таблице, содержащая значения атрибутов).

• Атрибут: Свойство сущности, отражающее характеристики (столбец).

• Домен: Множество допустимых значений для атрибута.

• Отношение: Таблица, состоящая из заголовка и множества кортежей (строк).

• Заголовок отношения: Список атрибутов;

• Тело отношения: Множество кортежей, входящих в состав отношения; или

• Тело отношения: Подмножество декартова произведения доменов.

Опишите пример, используя новые термины:

Для атрибута Major доменом будет являться множество предметов:
{Информатика, Математика, Физика, Химия, Биология}.

Свойства отношений

1. Уникальность кортежей

2. Степень отношения (арность отношения): Степень отношения определяется количеством атрибутов (столбцов) в нем. Например, если отношение имеет три атрибута, его степень равна 3.

3. Мощность отношения (кардинальность отношения): количество кортежей в отношении.

4. Домен атрибутов: Каждый атрибут в отношении связан с определенным доменом, который представляет собой множество допустимых значений для этого атрибута. Это обеспечивает семантическую однозначность данных.

5. Замкнутость операций: Все операции реляционной алгебры над отношениями возвращают новое отношение, что позволяет комбинировать результаты различных операций и строить сложные запросы.

Эти свойства формируют основу для работы с данными в реляционных базах и обеспечивают их целостность и структурированность.

Операции реляционной алгебры

Реляционная алгебра включает восемь основных операций, предложенных Эдгаром Коддом:

1. Объединение (Union): Возвращает отношение, содержащее все кортежи, которые принадлежат хотя бы одному из двух заданных отношений.
2. Пересечение (Intersection): Возвращает отношение с кортежами, которые принадлежат обоим заданным отношениям.
3. Разность (Set Difference): Возвращает отношение с кортежами, которые принадлежат первому отношению, но не принадлежат второму.
4. Декартово произведение (Cartesian Product): Создает новое отношение, состоящее из всех возможных пар кортежей из двух заданных отношений.
5. Выборка (Ограничение, Selection): Извлекает кортежи из отношения, удовлетворяющие определенному условию.
6. Проекция (Projection): Извлекает определенные атрибуты из отношения, создавая новое отношение с выбранными атрибутами.
7. Соединение (Join): Объединяет два отношения по общему атрибуту, создавая новое отношение с соответствующими кортежами.
8. Деление (Division): Используется для получения кортежей из одного отношения, которые соответствуют всем значениям атрибутов другого отношения.

Примеры операций реляционной алгебры

Рассмотрим примеры для каждой из основных операций реляционной алгебры, используя две таблицы: Students и Courses.

Обсудите, какое отношение будет являться результатом для каждой операции.

Таблица: Students

Таблица: MoreStudents

Таблица: Courses

1. Объединение (Union)

Объединение двух отношений с совпадающими заголовками.

Например, если у нас есть ещё одна таблица MoreStudents, содержащая студентов, мы можем объединить их:

$$ Students \cup MoreStudents $$

2. Пересечение (Intersection)

Возвращает кортежи, которые присутствуют в обоих отношениях с совпадающими заголовками. Например: $$ Students \cap MoreStudents $$

3. Разность (Set Difference)

Возвращает кортежи из первого отношения, которые отсутствуют во втором. У отношений должны совпадать заголовки.

$$ Students - MoreStudents $$

4. Декартово произведение (Cartesian Product)

Создает новое отношение, состоящее из всех возможных пар кортежей из двух отношений:

$$ Students \times Courses $$

Сколько кортежей будет в таком отношении?

5. Выборка (Ограничение, Selection)

Извлекает кортежи из отношения, удовлетворяющие заданному условию. Например, чтобы выбрать студентов, которые изучают “Информатику”:

$$ \sigma_{Major=‘Информатика’}(Students) $$

6. Проекция (Projection)

Извлекает определенные атрибуты из отношения. Например, чтобы получить только имена студентов:

$$ \pi_{Name}(Students) $$

7. Соединение (Join)

Объединяет два отношения по общему атрибуту. Например, чтобы получить список студентов и названий их курсов:

$$ Students \bowtie Courses $$

Упражнение: в данном примере выразить Join через декартово произведение, ограничение и проекцию.

8. Деление (Division)

Используется для получения кортежей из одного отношения, которые соответствуют всем значениям атрибутов другого отношения. Например, если мы хотим найти студентов, которые изучают все курсы:

$$ Students \div Courses $$

Какой будет результат операции деления для отношений ниже?

Students

Courses

Схема и таблица в PostgreSQL

Схема

Схема в PostgreSQL представляет собой логическую группировку объектов базы данных, таких как таблицы, представления, индексы, функции и типы данных. Она служит пространством имен, позволяя организовать объекты и избегать конфликтов имен. В одной базе данных может быть несколько схем, и объекты с одинаковыми именами могут существовать в разных схемах.

Основные характеристики схемы:

• Создание схемы: Для создания схемы используется команда:

  CREATE SCHEMA имя_схемы;
  

• Пространство имен: Схемы позволяют использовать одни и те же имена для объектов в разных схемах. Например, таблицы schema1.table1 и schema2.table1 могут существовать одновременно.
• Управление доступом: Схемы позволяют управлять правами доступа к объектам на уровне группы или пользователя, что упрощает безопасность и администрирование базы данных[1][5][6].

Таблица

Таблица в PostgreSQL — это основная структура хранения данных, представляющая собой набор строк и столбцов. Каждая таблица состоит из кортежей (строк), которые содержат данные, и атрибутов (столбцов), которые описывают характеристики этих данных.

Основные характеристики таблицы:

• Создание таблицы: Для создания таблицы используется команда:

  CREATE TABLE имя_таблицы (
      имя_столбца1 тип_данных1,
      имя_столбца2 тип_данных2,
      ...
  );
  

• Структура данных: Каждая таблица имеет определенные атрибуты с заданными типами данных (например, INTEGER, VARCHAR, DATE), что позволяет контролировать формат хранимых данных.

Пример использования

Для создания схемы и таблицы в PostgreSQL можно использовать следующий пример:

-- Создание схемы
CREATE SCHEMA school;

-- Создание таблицы в схеме school
CREATE TABLE school.students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

В этом примере создается схема school, которая содержит таблицу students с тремя атрибутами: student_id, name и age.

Общая структура SELECT-запроса и порядок выполнения запроса

Общая структура SELECT-запроса

Запросы на выборку данных в SQL начинаются с ключевого слова SELECT, за которым следуют указания о том, какие данные необходимо извлечь. Основная структура запроса выглядит следующим образом:

SELECT [DISTINCT | ALL] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC | DESC]]
[LIMIT number];

Описание компонентов:

1. SELECT: Указывает, какие столбцы нужно выбрать. Можно использовать * для выбора всех столбцов или перечислить конкретные столбцы.
2. FROM: Указывает таблицу (или несколько таблиц), из которой выбираются данные.
3. WHERE: (необязательный) Фильтрует строки на основе заданного условия.
4. GROUP BY: (необязательный) Группирует строки по указанным столбцам для агрегирования данных.
5. HAVING: (необязательный) Фильтрует группы, созданные с помощью GROUP BY, на основе заданного условия.
6. ORDER BY: (необязательный) Указывает порядок сортировки результатов по одному или нескольким столбцам.
7. LIMIT / OFFSET / FETCH: (необязательный) Ограничивает количество возвращаемых строк.

Порядок выполнения запроса

При выполнении SQL-запроса порядок обработки различных частей запроса не соответствует порядку их написания. Обычно порядок выполнения запросов можно представить следующим образом:

1. FROM: Сначала определяется набор данных из указанных таблиц.
2. WHERE: Применяются условия фильтрации к строкам из набора данных.
3. GROUP BY: Строки группируются по указанным столбцам.
4. HAVING: Применяются условия фильтрации к сгруппированным данным.
5. SELECT: Выбираются указанные столбцы из оставшихся данных.
6. ORDER BY: Результаты сортируются по указанным столбцам.
7. LIMIT / OFFSET / FETCH: Ограничивается количество возвращаемых строк.

Этот порядок выполнения позволяет оптимизировать процесс обработки запросов и обеспечивает правильное извлечение данных в соответствии с заданными условиями и требованиями.

Пример таблицы в PostgreSQL и запросы SELECT

В качестве упражнения выполните следующие запросы устно и проверьте с использованием СУБД.

Создание таблицы

Рассмотрим простую таблицу employees, которая будет хранить информацию о сотрудниках.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC,
    age INTEGER
);

INSERT INTO employees (name, department, salary, age) VALUES
('Иванов И.И.', 'IT', 60000, 30),
('Петрова А.А.', 'HR', 55000, 28),
('Сидоров П.П.', 'IT', 70000, 35),
('Смирнова Е.Е.', 'Marketing', 55000, 32),
('Кузнецов В.В.', 'HR', 48000, 29);

Пример данных в таблице employees

Получим все записи в таблице.

SELECT *
FROM employees;

Примеры запросов SELECT

1. Запрос с подмножеством атрибутов (проекция)

   SELECT name, department
   FROM employees;
   

2. Запрос с использованием WHERE (ограничение)

   Получим сотрудников из отдела IT:

   SELECT *
   FROM employees
   WHERE department = 'IT';
   

   Символ * в SQL-запросе SELECT используется для выбора всех столбцов из указанной таблицы. Например:

   SELECT * FROM employees;
   

   Этот запрос вернет все строки и столбцы из таблицы employees. Использование * удобно для быстрого получения всех данных, но в случае больших таблиц лучше указывать только необходимые столбцы для повышения производительности.

3. Запрос с использованием GROUP BY

   Получим среднюю зарплату по каждому отделу:

   SELECT department, AVG(salary) AS average_salary
   FROM employees
   GROUP BY department;
   

   При группировке в блоке SELECT могут встречаться либо атрибуты, по которым происходит группировка, либо атрибуты, которые подаются на вход агрегирующим функциям. В SQL есть 5 стандартных агрегирующих функций. При выполнении запроса функции не учитывается специальное значение NULL, которым обозначается отсутствующее значение.

   • count() – количество записей с известным значением. Если необходимо подсчитать количество уникальных значений, можно использовать count(DISTINCT field_nm)
   • max() - наибольшее из всех выбранных значений поля
   • min() - наименьшее из всех выбранных значений поля
   • sum() - сумма всех выбранных значений поля
   • avg() - среднее всех выбранных значений поля

4. Запрос с использованием HAVING

   Получим отделы, где средняя зарплата превышает 50000:

   SELECT department, AVG(salary) AS average_salary
   FROM employees
   GROUP BY department
   HAVING AVG(salary) > 50000;
   

5. Запрос с использованием ORDER BY

   Получим всех сотрудников, отсортированных по зарплате в порядке убывания:

   SELECT *
   FROM employees
   ORDER BY salary DESC;
   

6. Запрос с использованием FETCH

   Получим трёх самых молодых сотрудников:

   SELECT * FROM employees 
   ORDER BY age ASC 
   FETCH FIRST 3 ROWS ONLY;
   

   Выполните следующий запрос. Сколько строк вернул этот запрос? Почему?

   SELECT * FROM employees 
   ORDER BY salary DESC 
   FETCH FIRST 3 ROWS WITH TIES;
   

7. Запрос с использованием LIMIT

   LIMIT - сокращение FETCH

   Получим только двух самых высокооплачиваемых сотрудников:

   SELECT *
   FROM employees
   ORDER BY salary DESC
   LIMIT 2;
   

8. Запрос с использованием OFFSET

   Получим всех сотрудников кроме двух самых высокооплачиваемых сотрудников:

   SELECT *
   FROM employees
   ORDER BY salary DESC
   OFFSET 2;
   

9. Запрос с использованием DISTINCT

   Получим самого высокооплачиваемого сотрудника в каждом отделе:

   SELECT DISTINCT ON (department) id, name, department, salary
   FROM employees
   ORDER BY department, salary DESC;
   

   Получим количество отделов:

   SELECT COUNT(DISTINCT department)
   FROM employees;
   

10. Запрос с использованием UNION и UNION ALL (объединение)

    Важно: при использовании теоретико-множественных операций типы данных соответствующих полей должны быть совместимыми.

    Чтобы продемонстрировать разницу между UNION и UNION ALL, приведем пример с использованием двух наборов данных.

    Предположим, у нас есть две таблицы, которые содержат информацию о сотрудниках:

    Создание временных таблиц

    CREATE TEMP TABLE employees_a (
        id SERIAL PRIMARY KEY,
        name VARCHAR(50),
        salary NUMERIC
    );
    
    INSERT INTO employees_a (name, salary) VALUES
    ('Иванов И.И.', 60000),
    ('Петрова А.А.', 50000),
    ('Сидоров П.П.', 70000);
    
    CREATE TEMP TABLE employees_b (
        id SERIAL PRIMARY KEY,
        name VARCHAR(50),
        salary NUMERIC
    );
    
    INSERT INTO employees_b (name, salary) VALUES
    ('Смирнова Е.Е.', 55000),
    ('Кузнецов В.В.', 60000),
    ('Иванов И.И.', 60000);
    

    Использование UNION

    Теперь мы можем использовать UNION, чтобы объединить данные из обеих таблиц и удалить дубликаты:

    SELECT name, salary FROM employees_a
    UNION
    SELECT name, salary FROM employees_b;
    

    Результат запроса с UNION:

    name	salary
    Иванов И.И.	60000
    Петрова А.А.	50000
    Сидоров П.П.	70000
    Смирнова Е.Е.	55000
    Кузнецов В.В.	60000

    Использование UNION ALL

    Теперь используем UNION ALL, чтобы объединить данные из обеих таблиц и сохранить все дубликаты:

    SELECT name, salary FROM employees_a
    UNION ALL
    SELECT name, salary FROM employees_b;
    

    Результат запроса с UNION ALL:

    name	salary
    Иванов И.И.	60000
    Петрова А.А.	50000
    Сидоров П.П.	70000
    Смирнова Е.Е.	55000
    Кузнецов В.В.	60000
    Иванов И.И.	60000

    Выводы

    • UNION: Удаляет дубликаты из результирующего набора, возвращая уникальные строки.
    • UNION ALL: Сохраняет все строки из обоих наборов данных, включая дубликаты.

    Таким образом, использование UNION или UNION ALL зависит от ваших требований к обработке дубликатов в результирующем наборе данных.

    UNION применяет к строкам результата DISTINCT, что требует дополнительных временных затрат.

11. Использование INTERSECT (пересечение)

    SELECT name, salary FROM employees_a
    INTERSECT
    SELECT name, salary FROM employees_b;
    

    Результат запроса с INTERSECT:

    name	salary
    Иванов И.И.	60000

12. Использование EXCEPT (разность)

    SELECT name, salary FROM employees_a
    EXCEPT
    SELECT name, salary FROM employees_b;
    

    Результат запроса с EXCEPT:

    name	salary
    Сидоров П.П.	70000
    Петрова А.А.	50000

Полезные функции

Функции GREATEST и LEAST

Функции GREATEST и LEAST позволяют находить максимальное и минимальное значение среди нескольких аргументов.

• GREATEST: Возвращает наибольшее значение из списка аргументов. Если все аргументы равны NULL, результат будет NULL.

SELECT GREATEST(column1, column2, column3) AS max_value FROM table_name;

• LEAST: Возвращает наименьшее значение из списка аргументов. Аналогично, если все аргументы равны NULL, результат будет NULL.

SELECT LEAST(column1, column2, column3) AS min_value FROM table_name;

Пример использования GREATEST и LEAST

Предположим, у нас есть таблица с оценками студентов:

CREATE TABLE grades (
    student_id INT,
    exam1 FLOAT,
    exam2 FLOAT,
    exam3 FLOAT
);

INSERT INTO grades (student_id, exam1, exam2, exam3) VALUES
(1, 85.0, NULL, 90.0),
(2, 78.0, 82.0, 88.0),
(3, NULL, NULL, 95.0);

Чтобы найти максимальную оценку для каждого студента:

SELECT student_id, GREATEST(exam1, exam2, exam3) AS max_grade
FROM grades;

Для нахождения минимальной оценки:

SELECT student_id, LEAST(exam1, exam2, exam3) AS min_grade
FROM grades;

Иногда бывает полезно использовать в запросе специальные функции:

• IN - принадлежность определенному набору значений: X IN (a1, a2, ..., an) ≣ X = a₁ or X = a₂ or … or X = a_n
• BETWEEN - принадлежность определенному интервалу значений: X BETWEEN A AND B ≣ (A <= X <= B) or (B <= X <= A)
• LIKE - удовлетворение текста паттерну: X LIKE '0%abc_0', где _ - ровно 1 символ, а % - любая последовательность символов (в том числе нулевой длины).
• IF ... THEN ... [ELSIF ... THEN ... ELSE ...] END IF - ветвления, пример:

SELECT
    IF number = 0 THEN
        'zero'
    ELSIF number > 0 THEN
        'positive'
    ELSIF number < 0 THEN
        'negative'
    ELSE
        'NULL'
    END IF AS number_class
FROM
    numbers

• CASE [...] WHEN ... THEN ... ELSE ... END CASE - еще один аналог ветвлений, пример:

SELECT
    CASE
        WHEN number = 0 THEN
            'zero'
        WHEN number > 0 THEN
            'positive'
        WHEN number < 0 THEN
            'negative'
        ELSE
            'NULL'
    END CASE AS number_class
FROM
    numbers

• DISTINCT ON - исключает строки, совпадающие по всем указанным выражениям, пример:

-- вывести кол-во уникальных отделов
SELECT
    count(DISTINCT ON department_nm)
FROM
    salary;

• Еще немного полезностей

Работа с NULL

В PostgreSQL значение NULL используется для обозначения отсутствия данных. Это специальное значение, которое указывает на то, что ячейка таблицы не содержит данных. При работе с NULL важно понимать, как он влияет на различные операции и запросы.

1. Создание таблицы с NULL: При создании таблицы можно указать, какие столбцы могут содержать NULL:

   CREATE TABLE COMPANY (
       ID INT PRIMARY KEY NOT NULL,
       NAME TEXT NOT NULL,
       AGE INT NOT NULL,
       ADDRESS CHAR(50),
       SALARY REAL
   );
   

   В этом примере столбцы ADDRESS и SALARY могут содержать NULL, так как они не имеют ограничения NOT NULL.

2. Вставка значений NULL: Чтобы вставить значение NULL, можно просто пропустить его или явно указать:

   INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('Kim', 22, NULL, NULL);
   

3. Обновление значений на NULL: Используйте оператор UPDATE, чтобы установить значение столбца в NULL:

   UPDATE COMPANY SET ADDRESS = NULL WHERE ID = 6;
   

4. Выборка значений с использованием IS NULL и IS NOT NULL: Для фильтрации записей по наличию или отсутствию значений NULL используются операторы:

   -- Выборка записей с ненулевыми зарплатами
   SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;
   
   -- Выборка записей с нулевыми зарплатами
   SELECT * FROM COMPANY WHERE SALARY IS NULL;
   

5. Сравнение с NULL: Операции сравнения с NULL возвращают результат NULL. Например:

   SELECT * FROM COMPANY WHERE SALARY = NULL; -- Не вернет никаких строк
   

   Вместо этого следует использовать IS NULL.

6. Использование функций для обработки NULL:

   • COALESCE: Возвращает первое ненулевое значение из списка.

     SELECT COALESCE(SALARY, 0) AS Salary FROM COMPANY;
     

   • NULLIF: Возвращает NULL, если два аргумента равны.

     SELECT SALARY / NULLIF(AGE, 0) AS SalaryPerYear FROM COMPANY;
     

7. Агрегационные функции и NULL: Агрегационные функции игнорируют значения NULL. Например, при использовании SUM или COUNT значения NULL не учитываются в расчете.

Работа с NaN

В PostgreSQL значение NaN (Not a Number) используется для обозначения неопределенных или некорректных результатов вычислений, особенно в контексте числовых типов данных. Вот основные аспекты работы с NaN:

1. Основные характеристики NaN

• Представление: NaN может храниться в столбцах типа numeric, real или double precision. При этом NaN считается неравным любому другому значению, включая самого себя.
• Сравнение: В PostgreSQL все значения NaN считаются равными друг другу, но больше любых других чисел, что позволяет использовать их в индексах и сортировках.

2. Операции с NaN

• Арифметические операции: Любая операция с NaN возвращает NaN. Например:

  select 'NaN' + 0.5;
  select 'NaN' * 0.5;
  select 'NaN' / 0.0;
  

• Возведение в степень: Есть исключение для возведения NaN в нулевую степень, которое возвращает 1:

  SELECT POWER('NaN', 0);  -- Результат будет 1
  

3. Запись NaN в SQL

При вставке значения NaN в таблицу его нужно заключать в апострофы:

INSERT INTO table_name (column_name) VALUES ('NaN'::numeric);

4. Примеры использования

• Создание таблицы:

  CREATE TABLE example (
      id SERIAL PRIMARY KEY,
      value NUMERIC
  );
  
  INSERT INTO example (value) VALUES ('NaN'::numeric);
  

• Выборка значений:

  SELECT * FROM example WHERE value = 'NaN'::numeric; -- Найдет записи с NaN
  

• Обработка значений: Чтобы избежать проблем с NaN, можно использовать функции для обработки:

  SELECT COALESCE(value, '0'::numeric) FROM example; -- Заменяет NaN на 0
  

Основные типы данных

PostgreSQL поддерживает широкий набор типов данных, которые можно условно разделить на несколько категорий. Вот основные типы данных в PostgreSQL:

1. Числовые типы

• smallint: 2-байтовое целое число, диапазон от -32,768 до +32,767.
• integer: 4-байтовое целое число, диапазон от -2,147,483,648 до +2,147,483,647.
• bigint: 8-байтовое целое число, диапазон от -9,223,372,036,854,775,808 до +9,223,372,036,854,775,807.
• decimal (p,s) / numeric (p,s): Числа с фиксированной точностью и масштабом. Максимальная точность до 131072 цифр.
• real: 4-байтовое число с плавающей запятой (float4).
• double precision: 8-байтовое число с плавающей запятой (float8).
• serial: Автоинкрементирующееся 4-байтовое целое число.
• bigserial: Автоинкрементирующееся 8-байтовое целое число.

2. Символьные типы

• char(n): Фиксированная длина строки (n символов).
• varchar(n): Переменная длина строки с ограничением (максимум n символов).
• text: Строка переменной длины без ограничений.

3. Логический тип

• boolean: Хранит значения TRUE, FALSE или NULL.

4. Дата и время

• date: Дата (год, месяц, день).
• time [ (p) ]: Время без часового пояса.
• timestamp [ (p) ]: Дата и время без часового пояса.
• timestamptz: Дата и время с часовым поясом.
• interval: Интервал времени.

5. Бинарные типы

• bytea: Двоичные данные переменной длины.

6. Геометрические типы

• point: Точка в двумерном пространстве.
• line: Прямая в двумерном пространстве.
• lseg: Отрезок в двумерном пространстве.
• polygon: Многоугольник.
• circle: Окружность.

7. JSON и XML

• json: Хранит данные в формате JSON.
• jsonb: Хранит данные в бинарном формате JSON для более эффективного хранения и поиска.
• xml: Хранит данные в формате XML.

8. UUID

• uuid: Универсальный уникальный идентификатор.

9. Массивы

PostgreSQL поддерживает массивы для любого типа данных.

10. Диапазоны

PostgreSQL предоставляет типы для представления диапазонов значений:

• Например, int4range, numrange, tsrange, и другие.

Эти типы данных позволяют эффективно хранить и обрабатывать разнообразные данные в PostgreSQL. Выбор правильного типа данных важен для обеспечения согласованности и производительности базы данных.

Пользовательские типы данных в PostgreSQL

PostgreSQL предоставляет возможность создания пользовательских типов данных.

Пользовательские типы данных могут быть созданы с помощью команд CREATE TYPE и CREATE DOMAIN.

Основные виды пользовательских типов

1. Составные типы (Composite Types): Составные типы позволяют объединять несколько полей разных типов в один. Например, можно создать тип, который будет содержать имя и номер телефона сотрудника:

   CREATE TYPE employee_data AS (
       employee_name VARCHAR,
       contact_no VARCHAR
   );
   

2. Доменные типы (Domain Types): Доменные типы представляют собой ограничения на существующие типы данных. Они позволяют добавлять дополнительные проверки, такие как NOT NULL или CHECK. Например:

   CREATE DOMAIN phone_number AS VARCHAR CHECK (VALUE ~ '^\+?[0-9]*$');
   

   Это создаст новый тип, который будет принимать только строки, соответствующие определенному шаблону.

Обработка пользовательских типов

Пользовательские типы должны иметь функции ввода и вывода, которые определяют, как они будут выглядеть в строковом виде при вводе и выводе.

Приведение типов (CAST) в PostgreSQL

Приведение типов (casting) в PostgreSQL — это процесс преобразования значения из одного типа данных в другой. Это важно для обеспечения совместимости типов данных при выполнении операций и вызове функций в SQL-запросах. PostgreSQL предоставляет два основных способа приведения типов: функцию CAST() и оператор ::.

Синтаксис

1. Использование функции CAST:

   CAST (выражение AS целевой_тип_данных);
   

   Здесь выражение может быть константой, именем столбца или любым выражением, которое возвращает значение. целевой_тип_данных — это тип данных, к которому нужно преобразовать значение.

2. Использование оператора ::

   выражение::целевой_тип_данных
   

   Этот оператор является более лаконичным способом записи и выполняет ту же задачу, что и функция CAST(). Однако оператор :: специфичен для PostgreSQL и не является частью стандарта SQL.

Примеры приведения типов

• Преобразование строки в число:

  SELECT CAST('069' AS INTEGER) AS salary_int;
  

  Преобразует строку '069' в целое число.

• Преобразование строки в дату:

  SELECT CAST('2015-01-01' AS DATE) AS date_value;
  

  Преобразует строковое представление даты в формат даты.

• Преобразование числа в строку:

  SELECT CAST(123 AS VARCHAR) AS string_value;
  

  Преобразует целое число в строковое представление.

• Использование оператора :: для приведения типов:

  SELECT '3.14'::NUMERIC AS numeric_value;
  SELECT 42::TEXT AS text_value;
  

Обработка ошибок

Если данные невозможно преобразовать к указанному типу, PostgreSQL выдаст ошибку. Например, попытка преобразовать строку, содержащую текст, в число вызовет ошибку:

SELECT CAST('abc' AS INTEGER);
-- Error: invalid input syntax for type integer: "abc"

Основные случаи использования приведения типов

1. Совместимость данных: Когда необходимо выполнять операции с данными разных типов (например, сложение чисел, хранящихся как строки).
2. Форматирование данных: Для приведения вывода к нужному формату (например, отображение дат или чисел как строки).
3. Сохранение целостности данных: Для обеспечения соответствия данных типам, определённым в таблицах базы данных.

Работа с датами

Есть 4 основных способа хранения значений времени в PostgreSQL:

Пример использования TIMESTAMP вместе с INTERVAL:

CREATE TABLE contests(
    contest_id          SERIAL      PRIMARY KEY,
    contest_title       TEXT        UNIQUE NOT NULL,
    contest_start       TIMESTAMP   NOT NULL,
    contest_length      INTERVAL
);

INSERT INTO contests(contest_title, contest_start, contest_length)
VALUES
('Contest 1', '2025-01-09 05:00', '1 day'),
('Contest 4', '2025-01-09 15:30', '01:30'),
('Contest 5', '2024-10-30 18:00', '14 days'),
('Contest 6', '2024-11-18 20:00', '1 day');

SELECT contest_start, contest_length
FROM game_battle.contest;

SELECT contest_start, contest_start + contest_length AS contest_end
FROM contests;

SELECT INTERVAL '1 year -5 days 1 hour';

SELECT NOW();
SELECT CURRENT_DATE;
SELECT LOCALTIME;

SELECT DATE_TRUNC('month', DATE '2023-04-10');
SELECT DATE_TRUNC('day', TIMESTAMP '2023-04-10 10:39:37');

SELECT TO_CHAR(DATE '2023-04-10', 'YY');

• Дату сегодня можно найти так: NOW() (timestamp), CURRENT_DATE (date), LOCALTIME (текущее время)
• DATE_TRUNC(‘[interval]’, time_column) - позволяет округлить до месяца, года, числа и т.д.
• TO_CHAR([date type], [pattern]) - позволяет форматировать дату в строку по шаблону

Подробнее про to_char и виды шаблонов

Геометрические типы

Учимся пользоваться документацией postgresql.

Геометрические типы: ru / en

Операции с геометрическими типами: ru / en

Упражнения

1. На операции реляционной алгебры

Создайте и заполните указанные таблицы, после чего напишите запрос для каждого пункта.

Задание в файле relalg-insert.sql.

Ответы в файле relalg-requests.sql.

2. На SELECT-запросы

Задание в файле select-insert.sql.

Ответы в файле select-requests.sql.

3. Другое (NULL, NaN, даты)

Задание в файле other-insert.sql.

Ответы в файле other-requests.sql.