Thread: text .vs. varchar
Hello all, I have a big database in which much information is stored in TEXT type columns (I did this initially because I did not want to limit the maximum size of the string to be stored)... but... .. let's say I choose an upper limit (p.ex. 200) for the string sizes and I start a fresh database with VARCHAR(200). What tradeoffs can I expect ? disk usage ? query execution times ? thx Joao
In response to Joao Ferreira <jmcferreira@critical-links.com>: > Hello all, > > I have a big database in which much information is stored in TEXT type > columns (I did this initially because I did not want to limit the > maximum size of the string to be stored)... but... > > .. let's say I choose an upper limit (p.ex. 200) for the string sizes > and I start a fresh database with VARCHAR(200). > > What tradeoffs can I expect ? disk usage ? query execution times ? See the "Tip" on this page, it answers your questions: http://www.postgresql.org/docs/8.3/static/datatype-character.html -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
With Postgres appears that TEXT is preferred over varchar(N)
http://archives.postgresql.org/pgsql-general/2006-03/msg01522.php
Any other DB (e.g. Oracle) would suggest varchar as column only stores the length of the variable (variable character length..) vs any of the fixed length datatype(s)
Anyone else?
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> Date: Wed, 13 Aug 2008 08:45:19 -0400
> From: wmoran@collaborativefusion.com
> To: jmcferreira@critical-links.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] text .vs. varchar
>
> In response to Joao Ferreira <jmcferreira@critical-links.com>:
>
> > Hello all,
> >
> > I have a big database in which much information is stored in TEXT type
> > columns (I did this initially because I did not want to limit the
> > maximum size of the string to be stored)... but...
> >
> > .. let's say I choose an upper limit (p.ex. 200) for the string sizes
> > and I start a fresh database with VARCHAR(200).
> >
> > What tradeoffs can I expect ? disk usage ? query execution times ?
>
> See the "Tip" on this page, it answers your questions:
> http://www.postgresql.org/docs/8.3/static/datatype-character.html
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran@collaborativefusion.com
> Phone: 412-422-3463x4023
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Get more from your digital life. Find out how.
http://archives.postgresql.org/pgsql-general/2006-03/msg01522.php
Any other DB (e.g. Oracle) would suggest varchar as column only stores the length of the variable (variable character length..) vs any of the fixed length datatype(s)
Anyone else?
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> Date: Wed, 13 Aug 2008 08:45:19 -0400
> From: wmoran@collaborativefusion.com
> To: jmcferreira@critical-links.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] text .vs. varchar
>
> In response to Joao Ferreira <jmcferreira@critical-links.com>:
>
> > Hello all,
> >
> > I have a big database in which much information is stored in TEXT type
> > columns (I did this initially because I did not want to limit the
> > maximum size of the string to be stored)... but...
> >
> > .. let's say I choose an upper limit (p.ex. 200) for the string sizes
> > and I start a fresh database with VARCHAR(200).
> >
> > What tradeoffs can I expect ? disk usage ? query execution times ?
>
> See the "Tip" on this page, it answers your questions:
> http://www.postgresql.org/docs/8.3/static/datatype-character.html
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran@collaborativefusion.com
> Phone: 412-422-3463x4023
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Get more from your digital life. Find out how.
Martin Gainty wrote: > With Postgres appears that TEXT is preferred over varchar(N) > http://archives.postgresql.org/pgsql-general/2006-03/msg01522.php Implementation-wise, they are exactly the same, modulo length checking. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.