pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Koczan <pjkoczan@gmail.com>
To: Jessica Richard <rjessil@yahoo.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: "_" in a serach  pattern
Date: Mon, 23 Jul 2007 19:35:46 -0500
Message-ID: <46A54962.30109@gmail.com> (raw)
In-Reply-To: <829317.804.qm@web56414.mail.re3.yahoo.com>
References: <829317.804.qm@web56414.mail.re3.yahoo.com>

The only difference is that varchar can have a length limit, text is 
always unlimited length. There's no significant performance difference 
between either. For most intents and purposes, they're equal.

varchar is better than text in that limits are already built in. You can 
always impose limits on text types using rules and domains, but at a 
slight performance hit and having to create and maintain your own rules 
and domains. If you need limits, you might consider using varchar over 
text as everything is there.

I use text since I don't want to worry about overflow. Personally, 
specifying a varchar(500) column to be used as a "big string" field 
makes little sense to me. But, if you need a limit for a display or a 
program, you probably should use varchar. Besides, if you ever need to 
convert data types, any of the postgres 8.* releases make it easy.

Peter

P.S. You might want to file a bug report about your initial problem. 
According to 
http://www.postgresql.org/docs/8.2/static/datatype-character.html

Values of type character are physically padded with spaces to the 
specified width /n/, and are stored and displayed that way. However, the 
padding spaces are treated as semantically insignificant. Trailing 
spaces are disregarded when comparing two values of type character, and 
they will be removed when converting a character value to one of the 
other string types. Note that trailing spaces /are/ semantically 
significant in character varying and text values.

It should have ignored the spaces in the char field and didn't.

Peter

Jessica Richard wrote:
> 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 
> <http://us.rd.yahoo.com/evt=48252/*http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC;, 
> not web links. 




view thread (7+ messages)  latest in thread

Message-ID: <46A54962.30109@gmail.com>
Permalink:  ../46A54962.30109@gmail.com/
Also on:    postgresql.org/message-id/46A54962.30109@gmail.com

 · 

reply

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: pjkoczan@gmail.com, rjessil@yahoo.com
  Subject: Re: "_" in a serach  pattern
  In-Reply-To: <46A54962.30109@gmail.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