Sign up ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Is varchar just a remnant from before text came around, or are there use cases where you would want to use a varchar? (Or char for that matter..)

(I use Postgres and MySQL (MyISAM) daily, so those are what I'm most interested in, but answers for other databases are of course welcome. ^_-)

share|improve this question
At least for SQL Server, text is deprecated. There are also considerations of usage that are related to where the data is stored and how it therefore gets accessed. – Oded Jul 9 '12 at 19:08
On some DBMSs you may not be able to use a text column in a sort or where clause. I'm not familiar with Postgres but check your documentation. – james Jul 10 '12 at 0:12

3 Answers 3

up vote 13 down vote accepted

In General

text columns are non standard and implementation specific. In many cases, depending on the database they may have a combination of one or more of the following restrictions: not indexable, not searchable and not sortable.

In Postgres

All the types map to the same implementation details under the hood.


The text column is a specialized version of BLOB and has restrictions on indexing.

share|improve this answer
Accepted for non standard and implementation specific and not indexable, not searchable and not sortable, which I didn't realize. I was under the impression text was standardized. – Izkata Aug 8 '12 at 2:48
do you mean the ASCII text standard or the UNICODE text standard :-) or one of the other half dozen text encoding standards? – Jarrod Roberson Aug 8 '12 at 3:19
Nono, I really mean as a database datatype - one that was, perhaps, added after char and varchar =P – Izkata Aug 8 '12 at 3:26
if you go digging through the SQL standards documents I don't think you will find anything about text as a character type. I haven't seen anything, some vendors call it long char and the like, it is basically a BLOB with an encoding attached to it. – Jarrod Roberson Aug 8 '12 at 15:21

text, varchar and char are all used for different reasons. There are of course implementation differences (how much size they occupy .. etc), but also there are usage and intent considerations. What type you use also tells you something about the kind of data that will be stored in it (or we'd all use text for everything). If something has a fixed length, we use char. If it has variable length with a well defined upper limit then use varchar. If it's a big chunk of text that you have little control over then text would be probably your best bet.

share|improve this answer
Sooooooo, the only real difference is to duplicate the bounds-checking that should probably be in the program code anyway? – Izkata Jul 10 '12 at 15:10
@Izkata - There are implementation differences as well. It isn't about bounds checking, its about data type. A (US) zip code is always a 5 digit code, so using something like 'char' becomes part of the definition of this piece of data. If it was only stuff like bound checking we could all just use one data type for everything and do our checking and casting code side. – System Down Jul 10 '12 at 15:57
@SystemDown As far as I know, char, varchar, and text are all designed for storing the same type of data. So both answers here are about bounds checking. If there are efficiency differences, what are they? Why would I use varchar over text? – Izkata Jul 10 '12 at 16:23
float and double are also used for the same type of data, yet they have differences and are used differently. As for implementation differences, I'm not acquainted enough with Postgres to answer that I'm afraid. – System Down Jul 10 '12 at 16:51
@SystemDown Although storing postal codes as a char(5) may bite you if you start internationalizing. UK post codes vary in length and 5 characters is almost never enough. I don't know if the space in a UK post code is relevant for the parsing, though. – Vatine Aug 5 '12 at 13:05

Databases are intensely concerned with performance--speed and minimizing storage. In most other parts of the computer world, you are not going to be bothered about how many characters are in your character string; it could be one, it could be the entire contents of an encyclopedia; it's all just a string. In fact, a lot of languages don't even bother you about whether it's a string or a number.

But as computers get faster and gain more memory, people put more data into their databases and do fancier queries. For a database CPU and memory are just as limiting today as they were in the days of 64Kb main memory and 10Mb hard drives (on mainframe computers).

A fixed number of bytes is a lot easier to deal with than a variable length number. 10 bytes is a lot easier to deal with than 1,000,000. So your database wants you to give it a clue so it can give you a gigabyte of results from terrabytes of data in microseconds. If you're not using your database that hard, you won't need the speed it's offering and will be annoyed at the needless questions. But if you do need the performance, you'll be happy to give it some hints.

As noted in the other answers, use char if it always uses a certain number of characters, varchar if the length can vary but it doesn't get too large (my guess is most DB's treat it as a char or text depending on size), and text if it could be any length. If your SQL tries to use a text column, it might be best to summarize it somehow and put it in a char or small varchar column also, then do where's and order by's on that. Of course, that's only if performance matters to you.

share|improve this answer

protected by gnat Jul 18 at 14:24

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.