1. Defining text columns
Text-based columns provide some of the greatest flexibility for data values that can be stored in a database. This flexibility derives from both the variety of values that can be stored as well as the variable length allowed for these values. In this lesson, we will explore different representations of text data available in PostgreSQL.
2. Using text in PostgreSQL
Imagine that you work for a book publisher that is maintaining a database of books in the publisher's catalog.
Some of the information that you might be interested in including with every book record might include the book's author, the full text of the book, and a 13-digit ISBN number which uniquely identifies the book. This example demonstrates the use of each of the text types available in PostgreSQL.
The text-based types available in PostgreSQL include TEXT, VARCHAR, and CHAR. Let's discuss each in more detail.
3. The TEXT data type
The most flexible and straightforward text-based data type available in PostgreSQL is named TEXT. This type can represent a sequence of characters, called strings.
These strings can be of a variable length. Therefore, a value in a TEXT column could consist of 0 characters or 1 million characters. Both values could be valid in the same PostgreSQL TEXT column.
TEXT columns can also store strings of unlimited length. However, there are constraints such as disk-space that exist outside of PostgreSQL introducing limits on TEXT length.
The TEXT data type is a good choice for text data of an unknown length. Examples of such data include written feedback provided on a customer feedback form or the text content of a novel as displayed in the previous example.
4. The VARCHAR data type
The VARCHAR data type can store strings of variable and unlimited length.
This may sound similar to the TEXT data type just discussed.
However, the VARCHAR type has an additional feature which allows restrictions to be placed on these data values. This restriction can be introduced by following the VARCHAR type specification with a number, N, enclosed in parenthesis.
Such a declaration limits the column to only store strings of a maximum length of N characters.
The VARCHAR type does allow strings that are less than N characters to be stored in the column.
However, attempting to insert a string with a length greater than N will result in an error.
Practically speaking, a column declared as a VARCHAR column without specifying the character maximum N is equivalent to specifying a TEXT column.
VARCHAR-open-parenthesis-50-closed-parenthesis was used for an author's first and last name in the introductory example because we want to limit the length of the character sequences in these columns while also allowing the length to vary within these limits.
5. The CHAR data type
The CHAR data type is used to represent a sequence of characters.
It differs from VARCHAR in that values stored in a CHAR column do not vary in length.
If a sequence is stored that is less than the fixed length, N, spaces are added to the end to ensure the string is of the expected length.
Specifying a CHAR column without N defaults to a column that can only store a single character. This is the equivalent of CHAR-open-parenthesis-one-close-parenthesis.
In the book publisher example given previously, the ISBN number is specified as a CHAR-open-parenthesis-13-closed-parenthesis column because current ISBN numbers are 13-digits in length.
6. Let's practice!
Now that we have covered the different text types in PostgreSQL, let's use this knowledge to practice using the different text data types.