Postgres Pro
Downloads
Home   >   mailing lists

Thread: text vs varchar

text vs varchar

From
Wei Weng
Date:
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.

Re: text vs varchar

From
Josh Berkus
Date:
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

Re: text vs varchar

From
Michelle Murrain
Date:
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@murrain.net                 http://www.murrain.net

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

Re: text vs varchar

From
Wei Weng
Date:
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.

Re: text vs varchar

From
"Josh Goldberg"
Date:
I have no trouble indexing TEXT datatype in postgres-7.x

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

Re: text vs varchar

From
Achilleus Mantzios
Date:
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 nulldate_in         | timestamp
withouttime 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@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr