How do you know when to use varchar and when to use text in sql?

It seems like a very arbitrary d开发者_运维问答ecision. Both can accomplish the same thing in most cases. By limiting the varchar length seems to me like you're shooting yourself in the foot cause you never know how long of a field you will need.

Is there any specific guideline for choosing VARCHAR or TEXT for your string fields?

I will be using postgresql with the sqlalchemy orm framework for python.

In PostgreSQL there is no technical difference between varchar and text

You can see a varchar(nnn) as a text column with a check constraint that prohibits storing larger values.

So each time you want to have a length constraint, use varchar(nnn).

If you don't want to restrict the length of the data use text

This sentence is wrong:

By limiting the varchar length seems to me like you're shooting yourself in the foot cause you never know how long of a field you will need.

If you are saving, for example, MD5 hashes you do know how large the field is your storing and your storage becomes more efficient. Other examples are:

• Usernames (64 max)
• Passwords (128 max)
• Zip codes
• Addresses
• Tags
• Many more!

In brief:

• Variable length fields save space, but because each field can have different length, it makes table operations slower
• Fixed length fields make table operations fast, although must be large enough for the maximum expected input, so can use more space

Think of an analogy to arrays and linked lists, where arrays are fixed length fields, and linked lists are like varchars. Which is better, arrays or linked lists? Lucky we have both, because they are both useful in different situations, so too here.

In the most cases you do know what the max length of a string in a field is. In case of a first of lastname you don't need more then 255 characters for example. So by design you choose wich type to use, if you always use text you're wasting resources

Check this article on PostgresOnline, it also links to two other usefull articles.

Most problems with TEXT in PostgreSQL occur when you're using tools, applications and drivers that treat TEXT very different from VARCHAR because other databases behave very different with these two datatypes.

Database designers almost always know how many characters a column needs to hold. US delivery addresses need to hold up to 64 characters. (The US Postal Service publishes addressing guidelines that say so.) US ZIP codes are 5 characters long.

A database designer will look at representative sample data from her clients when she's specifying columns. She'll ask herself, questions like "What's the longest product name?" And when the answer is "70 characters", she won't make the column 3000 characters wide.

VARCHAR has a limit of 8k in SQL Server (I think). Most applications don't require nearly that much storage for a single column.