I have a "materialized path" tree table like this (simplified): CREATE TABLE product ( id SERIAL PRIMARY KEY, parents INT[] NOT NULL, name TEXT NOT NULL, UNIQUE (parents, name) ); CREATE INDEX name ON product(name);
Previously I use TEXT column for parents, but arrays look interesting and convenient so I'm considering migrating to arrays. However, how do I rewrite this using arrays? SELECT * FROM product WHERE parents LIKE '0001/0010/%'; In other words, testing against the first N elements in an array. Regards, Dave