Data Types - PostgreSQL: Up and Running - page 81

x
----
1
14
27
40
As shown in Example 5-1 , you can pass in an optional step argument that defines how
many steps to skip for each successive element. Leaving out the step will default it to 1 .
Also note that the end value will never exceed our prescribed range, so although our
range ends at 51, our last number is 40 because adding another 13 to our 40 exceeds the
upper bound.
Characters and Strings
There are three primitive character types in PostgreSQL: character (aka char ), char
acter varying (aka varchar ), and text . varchar and text are useful for fields that can
have very different sizes in different rows. The actual storage assigned to the field for
each row reflects just what the field needs for that row. The two fields are stored the
same way and have equivalent performance.
Use char only where the values stored are a fixed length, such as zip codes, phone
numbers, and Social Security numbers. char is right-padded with spaces out to the
specified size for both storage and display; this is more costly in terms of storage. You'll
find no other performance difference between varchar and char in PostgreSQL.
The difference between varchar with no size modifier and text is subtle. You can sort
on a text column regardless of how many characters it contains. Database drivers such
as ODBC might treat the two types differently. Both varchar and text have a cap of
around 1 GB. Behind the scenes, any data larger than what can fit in a record page gets
pushed to TOAST .
In versions prior to 9.2, if you try to expand the size of an existing
varchar field for a table with many rows, PostgreSQL will recreate
the table. The process could take a while and locks the table. As a
result, people often used text with a length constraint instead.
People have different opinions as to whether you should abandon varchar and always
use text . Rather than waste space arguing about it here, read the debate at In Defense
of VarcharX .
Often, for cross-system compatibility, you want to remove case sensitivity from your
character types. To do this, you need to override comparison operators that take case
into consideration. Overriding operators is easier for varchar than it is for text . We