• Аудит
  • Консалтинг
  • Миграция
    • Миграция с Oracle на PostgreSQL
    • Миграция 1С на PostgreSQL
  • Поддержка
  • Статьи и инструкции
Меню
  • Аудит
  • Консалтинг
  • Миграция
    • Миграция с Oracle на PostgreSQL
    • Миграция 1С на PostgreSQL
  • Поддержка
  • Статьи и инструкции

Использование расширения ltree в PostgreSQL

Установка

ltree является дополнением PostgreSQL и входит в пакет contrib, поэтому изначально оно не включено в стандартный пакет для *nix систем. Для установки выполняем команду в клиента psql в нужной базе данных:

CREATE EXTENSION ltree;

Исходная документация находится по адресу:

https://www.postgresql.org/docs/current/ltree.html

Я же рассмотрю отдельные моменты, которые не описаны в «родной» документации.

Исходные данные

Создаем простую таблицу без излишеств:

CREATE TABLE "public"."mp_tree" (
  "id"          INTEGER             DEFAULT nextval('mpath_id_seq'::regclass) NOT NULL, 
  "name"        TEXT, 
  "mpath"       "public"."ltree"    NOT NULL, 
  CONSTRAINT "mpath_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;

CREATE UNIQUE INDEX "mp_tree_idx" ON "public"."mp_tree"
  USING btree ("mpath" "public"."ltree_ops");

CREATE INDEX "mp_tree_idx1" ON "public"."mp_tree"
  USING gist ("mpath" "public"."gist_ltree_ops");

В качестве элементов материализованного пути будет id.

Родительские узлы

Добавление в результат выборки id и mpath родителя:

SELECT 
    m.*,
    subpath(mpath, -2, -1) AS pid,
    subpath(mpath, 0, -1) AS pmpath
FROM mp_tree AS m;

Если в качестве элементов материализованного пути используются не уникальные значения, то родителя лучше искать по mpath:

SELECT m.*
FROM mp_tree AS m
WHERE m.mpath = subpath([mpath текущего узла], 0, -1);

Хотя можно и так:

SELECT m.*
    FROM mp_tree AS m
    WHERE m.mpath <@ [mpath текущего узла] AND m.mpath <> [mpath текущего узла]
    ORDER BY m.mpath DESC
    LIMIT 1;

правда при этом стоит не забывать, что «<@» верно и для текущего узла. Соответственно, убрав LIMIT мы получим всю родительскую ветку от текущего узла.

Подчиненные узлы

Все потомки:

SELECT m.*
    FROM mp_tree AS m
    WHERE m.mpath <@ [mpath текущего узла]
    ORDER BY m.mpath;

Но в результат попадет и текущий узел, поэтому либо его исключать дополнительным условием, либо потомков выбирать так:

SELECT m.*
    FROM mp_tree AS m
    WHERE m.mpath ~ '[mpath текущего узла].*'
    ORDER BY m.mpath;

Прямые потомки:

SELECT m.*
    FROM mp_tree AS m
    WHERE m.mpath ~ '[mpath текущего узла].*{1}'
    ORDER BY m.mpath;

Собственно, выражением .*{n} можно регулировать глубину уровней вложенности для выборки.

Как видно, с помощью ltree работать с материализованными путями одно удовольствие.

Рубрики
  • Базы данных
    • PostgreSQL
    • Деревья
  • Без рубрики
  • Веб
    • Nginx
  • Операционные системы
    • Linux
Метки
ACL Adjacency List Contrib DevOps Extensions GRANT Linux ltree Materialized Path Nested Sets nginx ngx_http_dav_module ngx_http_image_filter_module ngx_http_map_module ngx_http_proxy_module ngx_http_random_index_module ngx_http_secure_link_module pg_hba.conf PostgreSQL privileges REVOKE Trees Ubuntu Web ZFS Деревья Индекс Миграция Оптимизация запросов триггеры

КОНСАЛТИНГ 
ПО POSTGRESQL

Консалтинг — 
это когда мы сами выполняем работу

ПОДРОБНЕЕ

ЕЩЕ СТАТЬИ ПО ДАННОЙ ТЕМЕ

PostgreSQL. Миграция. Как начать

Вот проснулись вы как-то утром и решили начать новую жизнь вместе с базой данных PostgreSQL оставив другие за бортом. Но с чего начать? Как «подкатить» и не быть отвергнутым? А ведь вы уже не молоды и «с прицепом»: с данными, нагрузкой и бизнес-логикой.
Так как в данный момент тема миграций в тренде, не могу пройти мимо и не поделится своим опытом подобных мероприятий. Данная задача не самая простая, так как требует подмены одной из фундаментальных технологий проекта — хранилище данных.

Читать далее »

PostgreSQL. Хочешь похудеть? Cпроси меня как.

Недавно попросили поднять копию одного проекта для тестов, ну и соответственно потянуть базу данных как есть из боя (pg_basebackup). Выяснилось, что физический размер базы составил 505 GB, что само по себе слегка удивило, не то, чтобы данных там было мало. Много, но не настолько.

Читать далее »

PostgreSQL ACL Object

В предыдущей статье мы рассматривали вопросы подключения и глобальных привилегий пользователей сервера PostgreSQL. Будем считать что головную боль админов в виде pg_hba.conf мы прошли, переходим к следующей.
DBA против Разработчиков, DML против DDL. Сразу хочу сказать, что мое мнение: привилегии DDL разработчику не нужны, несмотря на то, что я сам разработчик.

Читать далее »

Поддержка

Поддержка — это когда у нас возникает техническая
проблема с существующей системой,
и вам необходимо некоторое руководство

ПОДРОБНЕЕ

Postgres.men

  • Аудит
  • Консалтинг
  • Поддержка
  • Статьи и инструкции

Миграция

  • Миграция с Oracle на PostgreSQL
  • Миграция 1С на PostgreSQL

Мы в соцсетях

  • Вконтакте

© Postgres.Men by Taktive Ltd, 2022. Не является публичной офертой.