Array: comparing first N elements?

From: David Garamond <davidgaramond(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Array: comparing first N elements?
Date: 2009-05-12 08:05:28
Message-ID: 7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Pavel Stehule 2009-05-12 08:28:57 Re: Array: comparing first N elements?
Previous Message John Lister 2009-05-11 15:21:17 Re: Query planning question