Re: [SQL] Full-Text-Indexing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alexander Schneider <alex(at)alexander-schneider(dot)de>
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] Full-Text-Indexing
Date: 1999-06-12 17:00:41
Message-ID: 15517.929206841@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Alexander Schneider <alex(at)alexander-schneider(dot)de> writes:
> 1) Why should I use "varchar(n)" instead of "text" though the space for a
> varchar-field is larger than text (varchar: 4byte+n, text: 2byte+n)? Is
> it more efficient for queries?

Actually, both text and varchar have 4-byte overhead. There's no
difference at all in the representation, and none to speak of in
efficiency.

The reason you'd use varchar() is if you have an application-defined
restriction that the contents of the field may not exceed some
particular length. In that case you say varchar(N), which both makes
the intent visible and allows Postgres to enforce the requirement for
you.

If you don't have any particular upper bound in mind for the string
length, use text type.

Just for completeness: char(N) also has a 4-byte overhead ... rather
unnecessarily, but that's how it's done at the moment. But there is
a marginal (very marginal) efficiency gain in access, since the system
knows that the field is always the same length --- that makes it
easier to determine the locations of following fields in the same
tuple. I think you'd pretty much only use char(N) when you have
an application semantics restriction that the string length must be
exactly N characters. For example, in US postal addresses the state
abbreviation would be char(2).

regards, tom lane

Browse pgsql-sql by date

  From Date Subject
Next Message Oleg Bartunov 1999-06-12 20:14:10 Re: [SQL] Select like when searching for whole word and optimizing it
Previous Message Alexander Schneider 1999-06-12 08:11:31 Full-Text-Indexing