Re: text vs varchar

Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR
datatype with a maximum length, especially when I do searches on them?

Thanks

--
Wei Weng
Network Software Engineer
KenCast Inc.

Wei,

> Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR
> datatype with a maximum length, especially when I do searches on them?

Yes. You can't index TEXT because it's of potentially unlimited length.

--
-Josh Berkus

On Tue, 2002-06-18 at 18:07, Wei Weng wrote:
> Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR
> datatype with a maximum length, especially when I do searches on them?

There is one big disadvantage that I've found, but might be utterly
unimportant to you. Some applications (particularly Crystal Reports) do
not like unbounded text fields, and make it quite difficult to do any
manipulations of them. This might be an odbc issue, rather than an
application issue, but it can be a big deal sometimes.

--
.Michelle
------------------------------
Michelle Murrain, Technology Consulting
tech(at)murrain(dot)net http://www.murrain.net

413-253-2874
413-222-6350 cell
413-825-0288 fax

On Tue, 2002-06-18 at 18:59, Josh Berkus wrote:
> Wei,
>
> > Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR
> > datatype with a maximum length, especially when I do searches on them?
>
> Yes. You can't index TEXT because it's of potentially unlimited length.
>
> --
> -Josh Berkus
>
>
I noticed that it is a characteristics of MS SQL Server, but I did
successfully create unique index based on a TEXT field in Postgresql
7.2, while that failed in MS SQL Server (7).

Or do they behave the same in this aspect?

Thanks

--
Wei Weng
Network Software Engineer
KenCast Inc.

I have no trouble indexing TEXT datatype in postgres-7.x

----- Original Message -----
From: "Josh Berkus" <josh(at)agliodbs(dot)com>
> Yes. You can't index TEXT because it's of potentially unlimited length.

On Tue, 18 Jun 2002, Josh Berkus wrote:

> Wei,
>
> > Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR
> > datatype with a maximum length, especially when I do searches on them?
>
> Yes. You can't index TEXT because it's of potentially unlimited length.

Well indexing text works fine for me.

Table "repdat"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
vslid | integer |
vslname | character varying(15) |
orderno | integer | not null
date_in | timestamp without time zone |
port_landed | character varying(15) |
subject | text |
catid | integer |

dynacom=# EXPLAIN ANALYZE SELECT subject from repdat where subject='UTM
FOR SPECIAL SURVEY JUNE2000';
NOTICE: QUERY PLAN:

Index Scan using repdat_subject_idx on repdat (cost=0.00..7.40 rows=1
width=28) (actual time=0.05..0.06 rows=1 loops=1)
Total runtime: 0.10 msec

EXPLAIN
dynacom=#
dynacom=# SET enable_indexscan = off;
SET VARIABLE
dynacom=# EXPLAIN ANALYZE SELECT subject from repdat where subject='UTM
FOR SPECIAL SURVEY JUNE2000';
NOTICE: QUERY PLAN:

Seq Scan on repdat (cost=0.00..388.59 rows=1 width=28) (actual
time=0.03..8.14
rows=1 loops=1)
Total runtime: 8.19 msec

EXPLAIN
dynacom=#
>
>

--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr