В этом посте я расскажу вам о нескольких возможностях PostgreSQL, о которых знают далеко не все пользователи этой системы управления базами данных (СУБД).

JSONB

JSONB — это расширение для работы с JSON, которое позволяет индексировать документы JSON и выполнять операции поиска и обновления в них. Например, вы можете искать и обновлять части документа JSON без необходимости знать его структуру заранее.

-- Создание таблицы с полями типа jsonb
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

-- Запись документа JSON в таблицу
INSERT INTO documents (data) VALUES ('{"name": "John", "age": 30}');

-- Поиск документов, где age больше 20
SELECT * FROM documents WHERE data->>'age' > 20;

-- Обновление имени пользователя в документе
UPDATE documents SET data = data || '{"new_field": true}' WHERE id = 1;

TRIGGERS

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

-- Создание триггера перед удалением записи
CREATE TRIGGER before_delete BEFORE DELETE ON documents
FOR EACH ROW EXECUTE FUNCTION log_deletion();

-- Функция, которая будет вызываться при удалении записи
CREATE OR REPLACE FUNCTION log_deletion() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (action, table_name, row_id) VALUES ('DELETE', TG_TABLE_NAME, OLD.id);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

VIEWS

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

-- Создание представления для отображения данных о пользователях
CREATE VIEW users_view AS
SELECT id, name, age FROM documents WHERE type = 'user';

-- Выборка данных из представления
SELECT * FROM users_view;

INHERITANCE

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

-- Создание родительской таблицы
CREATE TABLE category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Создание дочерней таблицы, наследующей от родительской
CREATE TABLE subcategory (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES category(id),
    name VARCHAR(255) NOT NULL
) INHERITS (category);

-- Запись данных в родительскую и дочернюю таблицы
INSERT INTO category (name) VALUES ('Top Level Category');
INSERT INTO subcategory (parent_id, name) VALUES (1, 'Subcategory 1');

-- Выборка данных из обеих таблиц
SELECT * FROM category;
SELECT * FROM subcategory;

PostgreSQL предлагает множество других возможностей, которые делают его одной из самых мощных и гибких СУБД на рынке. Надеюсь, этот пост помог вам узнать немного больше о том, что может делать PostgreSQL.