From: Jessica Richard <rjessil@yahoo.com>
To: Peter Koczan <pjkoczan@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: "_" in a serach pattern
Date: Mon, 23 Jul 2007 10:11:54 -0700 (PDT)
Message-ID: <829317.804.qm@web56414.mail.re3.yahoo.com> (raw)
In-Reply-To: <46A2F0FC.4070501@gmail.com>
Peter,
thanks a lot for your reply.
Could you please tell me more....
What is the difference between varchar and text? what is the benefit of each one? and is text alway better than varchar ?--- when it comes to a string column...
thanks
Peter Koczan <pjkoczan@gmail.com> wrote: Hi, Jessica,
> 1. How do I get rid of the nonstandard warning, but still using the
> index search?
You have two options.
- Turn off the warnings in the postgresql.conf file. Use this with
caution (or don't use it at all) as it does pose a potential threat for
SQL injections if other options aren't properly set. Read up at
http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html
for more detail.
- Use escape-string formatting. This is the best practice since it is
standards-conforming and more secure. You can do it, for instance, as
select name from table where name like 'A!_B%' escape '!';
You can escape with most characters, and it's mostly a matter of
personal preference.
>
> 2. How do I search with a wild card % in the middle of the pattern?
> Would varchar(80) or char(80) make a difference about the wild card
> search (% in the middle)?
I think that postgres is seeing the trailing whitespace on the end of
the char type and not accounting for it in the search. In this case,
varchar or text types would make a difference since they don't do
whitespace padding (unless you force it in). You should remember that if
you plan on converting the data type, trim the extraneous whitespace
first. However, I would switch to varchar/text so it saves some space
and saves you these headaches, unless there's an absolute need for
fixed-length char fields. I use text almost exclusively for string data
since it's arbitrary-length, I almost never have to worry about overflow
or later administration.
If you don't want to convert data, you can use the rtrim() function
(i.e. "select rtrim(name) from ...").
Peter
> ------------------------------------------------------------------------
> Need a vacation? Get great deals to amazing places
> on
> Yahoo! Travel.
---------------------------------
Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rjessil@yahoo.com, pjkoczan@gmail.com
Subject: Re: "_" in a serach pattern
In-Reply-To: <829317.804.qm@web56414.mail.re3.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox