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

Reply via email to