Odd behavior with LIKE?

From: "Tim Barnard" <tbarnard(at)povn(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Odd behavior with LIKE?
Date: 2001-06-11 18:00:36
Message-ID: 01eb01c0f2a0$6b2bdde0$a519af3f@hartcomm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've noticed that if I don't preceed an underscore character ( _ )
with a double backslash ( \\ ), then a select using LIKE
ignores the underscore. For example, I have a couple of indexes
that end with "_ts" and a few tables that end in "ts":

From within psql:

select relname from pg_class;

pg_type
pg_attribute
.
.
.
raw_ts
accts
adjacents
areacodes
.
.
.
and so on.

Now if I change the select to omit all tables/indexes
that begin with "pg_" or end with "_pkey":

select relname from pg_class where relname not like 'pg_%' and relname not like '%_pkey';

the "pg_" and "_pkey" files are correctly ommitted.
However, if I also want to omit all tables/indexes
ending with "_ts":

select relname from pg_class where relname not like 'pg_%' and relname not like '%_pkey' and relname not like '_ts';

then not only is "raw_ts" omitted (which is what I want),
but so are the tables "accts" and "adjacents". I can
correct this with:

select relname from pg_class where relname not like 'pg_%' and relname not like '%_pkey' and relname not like '\\_ts';

and probably whould do so for all conditions:

select relname from pg_class where relname not like 'pg\\_%' and relname not like '%\\_pkey' and relname not like '\\_ts';

Question is: Why must the underscore character
be prefixed with a double-backslash?

Tim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jonathan Bartlett 2001-06-11 18:02:50 Re: Variables in Postgresql
Previous Message Alex Pilosov 2001-06-11 17:48:44 RE: RE: VARCHAR to CIDR type cast