Re: partition text/varchar check problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: jamcito <jamcito(at)poczta(dot)fm>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: partition text/varchar check problem
Date: 2006-12-16 15:48:48
Message-ID: 29283.1166284128@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

jamcito <jamcito(at)poczta(dot)fm> writes:
> I am trying to make partitions:

> CREATE TABLE data_a (CHECK (name LIKE varchar 'a%')
> ) INHERITS (data);
> --
> CREATE TABLE data_b (CHECK (name LIKE varchar 'b%')
> ) INHERITS (data);

That's not going to work because the planner is unable to prove anything
about the behavior of LIKE --- there is nothing in the system that
offers a relationship between the = operator and the LIKE operator.
You'll need something like

CHECK (name >= 'a' AND name < 'b')
CHECK (name >= 'b' AND name < 'c')

etc. (These work for a query like "WHERE name = 'foo'" because
the >= < and = operators are all members of the same btree opclass,
so the planner knows how to reason about them.)

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ron 2006-12-16 15:53:21 Re: New to PostgreSQL, performance considerations
Previous Message jamcito 2006-12-16 14:29:40 partition text/varchar check problem