Difference between text and varchar (character varying)

by

There is no difference, under the hood it’s all varlena (variable length array).

Check this article from Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

A couple of highlights:

To sum it all up:

  • char(n) – takes too much space when dealing with values shorter than n (pads them to n), and can lead to subtle errors because of adding trailing
    spaces, plus it is problematic to change the limit
  • varchar(n) – it’s problematic to change the limit in live environment (requires exclusive lock while altering table)
  • varchar – just like text
  • text – for me a winner – over (n) data types because it lacks their problems, and over varchar – because it has distinct name

The article does detailed testing to show that the performance of inserts and selects for all 4 data types are similar. It also takes a detailed look at alternate ways on constraining the length when needed. Function based constraints or domains provide the advantage of instant increase of the length constraint, and on the basis that decreasing a string length constraint is rare, depesz concludes that one of them is usually the best choice for a length limit.

More Related Contents:

  1. Split large string
  2. Change PostgreSQL columns used in views
  3. String literals and escape characters in postgresql
  4. How to search a string in multiple files and return the names of files in Powershell?
  5. Algorithm to find articles with similar text
  6. Scala String vs java.lang.String – type inference
  7. Replace empty strings with null values
  8. How to compare strings in Bash
  9. MySQL: Large VARCHAR vs. TEXT?
  10. SQL Server Text type vs. varchar data type [closed]
  11. Get string after character [duplicate]
  12. How can I convert a string to upper- or lower-case with XSLT?
  13. Load R package from character string
  14. Repeat string to certain length
  15. Multi-line string with extra space (preserved indentation)
  16. Replace whole line containing a string using Sed
  17. How can I compare a string to multiple correct values in Bash?
  18. Count word occurrences in R
  19. converting datetime string to POSIXct date/time format in R
  20. Python: most idiomatic way to convert None to empty string?
  21. Extract numeric part of strings of mixed numbers and characters in R
  22. How to add text to an image in java?
  23. Use character string as function argument
  24. Integer PadLeft function in T-SQL
  25. Convert specified column in a multi-line string into single comma-separated line
  26. Convert time.Time to string
  27. Excel VBA like operator [closed]
  28. The History Behind the Definition of a ‘String’ [closed]
  29. How can I replace newline characters using JSP and JSTL?
  30. Remove all line breaks at the beginning of a string in Swift

Leave a Comment