Re: [SQL] text vs varchar
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: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] text vs varchar
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 [EMAIL PROTECTED] http://www.murrain.net 413-253-2874 413-222-6350 cell 413-825-0288 fax ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] text vs varchar
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] text vs varchar
I have no trouble indexing TEXT datatype in postgres-7.x - Original Message - From: Josh Berkus [EMAIL PROTECTED] Yes. You can't index TEXT because it's of potentially unlimited length. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
