LTREE Extension - Hierarchical Tree

Giriş

Açıklaması şöyle

What’s ltree?

ltree is a Postgres extension for representing and querying data stored in a hierarchical tree-like structure.
...

ltree enables powerful search functionality that can be used to model, query and validate hierarchical and arbitrarily nested data structures. 

Kurulum

Şöyle yaparız

CREATE EXTENSION IF NOT EXISTS LTREE;

LTREE Sütun Tipi

LTREE Sütun Tipi kullanılır

Index Yaratma

GIST veya BTREE tipinden index yaratılır

Örnek

Şöyle yaparız. Sütun ismi path, tablo ismi test

CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);

Örnek

Şöyle yaparız

CREATE INDEX tree_path_idx ON tree USING GIST(path);

Tablo Yaratma

Örnek

Şöyle yaparız

CREATE TABLE test (path LTREE);
INSERT INTO test
VALUES
    ('Top'),
    ('Top.Science'),
    ('Top.Science.Astronomy'),
    ('Top.Science.Astronomy.Astrophysics'),
    ('Top.Science.Astronomy.Cosmology'),
    ('Top.Hobbies'),
    ('Top.Hobbies.Amateurs_Astronomy'),
    ('Top.Collections'),
    ('Top.Collections.Pictures'),
    ('Top.Collections.Pictures.Astronomy'),
    ('Top.Collections.Pictures.Astronomy.Stars'),
    ('Top.Collections.Pictures.Astronomy.Galaxies'),
    ('Top.Collections.Pictures.Astronomy.Astronauts');
-- Optionally, create indexes to speed up certain operations
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);

Örnek

Şöyle yaparız

CREATE TABLE tree(
  id SERIAL PRIMARY KEY,
  letter CHARACTER,
  path LTREE
);
CREATE INDEX tree_path_idx ON tree USING GIST(path);

LTREE operators

Bazıları şöyle

_eq

_gt 

_is_null

_ancestor

_descendant 

__matches 

_matches_fulltext 

_any