Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
postgresql example hierarchy with addresses
CREATE TABLE Address ( address_id int NOT NULL CONSTRAINT taxPkey PRIMARY KEY, value VARCHAR(255), parent_id int ); /* insert into Address values (1, 'Europe',0), (2, 'Ukraine',1), (4, 'Kyiv region',2), (5, 'Kyiv city',4), (7, 'Radyscheva',5), (10, 'Cherkassy region',2) , (3, 'Poland',1), (6, 'Krakow region',3), (8, 'Krakow city',6), (9, 'Vrozlav region',3) ; */ insert into Address values (1, 'Europe',0), (7, 'Radyscheva',5), (3, 'Poland',1), (2, 'Ukraine',1), (6, 'Krakow region',3), (8, 'Krakow city',6), (4, 'Kyiv region',2), (5, 'Kyiv city',4), (10, 'Cherkassy region',2) , (9, 'Vrozlav region',3) ; select * from address; WITH RECURSIVE cte AS ( SELECT ARRAY[address_id] AS hierarchy_order, address_id, value, 0 AS level, parent_id as parent_node FROM Address WHERE parent_id = 0 UNION ALL SELECT c.hierarchy_order || t.address_id as hierarchy_order, t.address_id, t.value as v , c.level + 1 as level, parent_id as parent_node FROM cte c JOIN Address t ON t.parent_id = c.address_id ) SELECT address_id, --lpad('> '||value,level+2+length(value),'-') as val, lpad(value,level+length(value)) as value, parent_node, level FROM cte ORDER BY hierarchy_order;
run
|
edit
|
history
|
help
0
book suggestion
testo
E2
Ecommerce Website Analysis by SQL
1258. Find the Start and End Number of Continuous Ranges
book suggestion
Attributvariation 2
E-commerce Website SQL Analysis
Query integer in text field - Seemingly doesn't work as expected
test