pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtext vs varchar
6+ messages / 5 participants
[nested] [flat]
* text vs varchar
@ 2002-06-18 22:07 Wei Weng <wweng@kencast.com>
2002-06-18 22:59 ` Re: text vs varchar Josh Berkus <josh@agliodbs.com>
2002-06-18 23:31 ` Re: text vs varchar Michelle Murrain <tech@murrain.net>
0 siblings, 2 replies; 6+ messages in thread
From: Wei Weng @ 2002-06-18 22:07 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: text vs varchar
2002-06-18 22:07 text vs varchar Wei Weng <wweng@kencast.com>
@ 2002-06-18 22:59 ` Josh Berkus <josh@agliodbs.com>
2002-06-18 23:49 ` Re: text vs varchar Wei Weng <wweng@kencast.com>
2002-06-19 01:28 ` Re: text vs varchar Josh Goldberg <josh@4dmatrix.com>
2002-06-19 07:44 ` Re: text vs varchar Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 3 replies; 6+ messages in thread
From: Josh Berkus @ 2002-06-18 22:59 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: text vs varchar
2002-06-18 22:07 text vs varchar Wei Weng <wweng@kencast.com>
2002-06-18 22:59 ` Re: text vs varchar Josh Berkus <josh@agliodbs.com>
@ 2002-06-18 23:49 ` Wei Weng <wweng@kencast.com>
2 siblings, 0 replies; 6+ messages in thread
From: Wei Weng @ 2002-06-18 23:49 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: text vs varchar
2002-06-18 22:07 text vs varchar Wei Weng <wweng@kencast.com>
2002-06-18 22:59 ` Re: text vs varchar Josh Berkus <josh@agliodbs.com>
@ 2002-06-19 01:28 ` Josh Goldberg <josh@4dmatrix.com>
2 siblings, 0 replies; 6+ messages in thread
From: Josh Goldberg @ 2002-06-19 01:28 UTC (permalink / raw)
To: josh@agliodbs.com; Wei Weng <wweng@kencast.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: text vs varchar
2002-06-18 22:07 text vs varchar Wei Weng <wweng@kencast.com>
2002-06-18 22:59 ` Re: text vs varchar Josh Berkus <josh@agliodbs.com>
@ 2002-06-19 07:44 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 0 replies; 6+ messages in thread
From: Achilleus Mantzios @ 2002-06-19 07:44 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Wei Weng <wweng@kencast.com>; pgsql-sql
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@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: text vs varchar
2002-06-18 22:07 text vs varchar Wei Weng <wweng@kencast.com>
@ 2002-06-18 23:31 ` Michelle Murrain <tech@murrain.net>
1 sibling, 0 replies; 6+ messages in thread
From: Michelle Murrain @ 2002-06-18 23:31 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-06-19 07:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-18 22:07 text vs varchar Wei Weng <wweng@kencast.com>
2002-06-18 22:59 ` Josh Berkus <josh@agliodbs.com>
2002-06-18 23:49 ` Wei Weng <wweng@kencast.com>
2002-06-19 01:28 ` Josh Goldberg <josh@4dmatrix.com>
2002-06-19 07:44 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-06-18 23:31 ` Michelle Murrain <tech@murrain.net>
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