Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group