在PostgreSQL中,ltree數(shù)據(jù)類型用于處理樹形結(jié)構(gòu)數(shù)據(jù)
CREATE TABLE your_table_name (
id SERIAL PRIMARY KEY,
path ltree
);
INSERT INTO your_table_name (path) VALUES ('1');
INSERT INTO your_table_name (path) VALUES ('1.2');
INSERT INTO your_table_name (path) VALUES ('1.2.3');
INSERT INTO your_table_name (path) VALUES ('1.3');
INSERT INTO your_table_name (path) VALUES ('1.4');
SELECT * FROM your_table_name WHERE path ~ '^1\.2\..*';
SELECT * FROM your_table_name WHERE path ~ '^1\..*';
SELECT * FROM your_table_name WHERE path ~ '^1\.2\.\d+$';
SELECT * FROM your_table_name WHERE path ~ '^1\.\d+\.\d+$';
SELECT * FROM your_table_name WHERE path ~ '^1\.\d+\.\d+$';
SELECT * FROM your_table_name WHERE path ~ '^' || '1.2.3' || '\.\d+$';
SELECT * FROM your_table_name WHERE path ~ '^' || '1.2.3' || '\.\d+$';
注意:在這些示例中,我們使用了~
運(yùn)算符來(lái)匹配路徑列中的字符串模式。這是PostgreSQL中LIKE運(yùn)算符的擴(kuò)展版本,允許使用正則表達(dá)式。