<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Character Types</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Data Types" HREF="datatype.html"><LINK REL="PREVIOUS" TITLE="Monetary Types" HREF="datatype-money.html"><LINK REL="NEXT" TITLE="Binary Data Types" HREF="datatype-binary.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="datatype-money.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="datatype.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 8. Data Types</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="datatype.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="datatype-binary.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="DATATYPE-CHARACTER" >8.3. Character Types</A ></H1 ><A NAME="AEN3892" ></A ><A NAME="AEN3895" ></A ><A NAME="AEN3898" ></A ><A NAME="AEN3900" ></A ><A NAME="AEN3902" ></A ><A NAME="AEN3904" ></A ><A NAME="AEN3906" ></A ><DIV CLASS="TABLE" ><A NAME="DATATYPE-CHARACTER-TABLE" ></A ><P ><B >Table 8-4. Character Types</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><THEAD ><TR ><TH >Name</TH ><TH >Description</TH ></TR ></THEAD ><TBODY ><TR ><TD ><TT CLASS="TYPE" >character varying(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT >, <TT CLASS="TYPE" >varchar(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT ></TD ><TD >variable-length with limit</TD ></TR ><TR ><TD ><TT CLASS="TYPE" >character(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT >, <TT CLASS="TYPE" >char(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT ></TD ><TD >fixed-length, blank padded</TD ></TR ><TR ><TD ><TT CLASS="TYPE" >text</TT ></TD ><TD >variable unlimited length</TD ></TR ></TBODY ></TABLE ></DIV ><P > <A HREF="datatype-character.html#DATATYPE-CHARACTER-TABLE" >Table 8-4</A > shows the general-purpose character types available in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >. </P ><P > <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > defines two primary character types: <TT CLASS="TYPE" >character varying(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT > and <TT CLASS="TYPE" >character(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT >, where <TT CLASS="REPLACEABLE" ><I >n</I ></TT > is a positive integer. Both of these types can store strings up to <TT CLASS="REPLACEABLE" ><I >n</I ></TT > characters in length. An attempt to store a longer string into a column of these types will result in an error, unless the excess characters are all spaces, in which case the string will be truncated to the maximum length. (This somewhat bizarre exception is required by the <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > standard.) If the string to be stored is shorter than the declared length, values of type <TT CLASS="TYPE" >character</TT > will be space-padded; values of type <TT CLASS="TYPE" >character varying</TT > will simply store the shorter string. </P ><P > If one explicitly casts a value to <TT CLASS="TYPE" >character varying(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT > or <TT CLASS="TYPE" >character(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT >, then an over-length value will be truncated to <TT CLASS="REPLACEABLE" ><I >n</I ></TT > characters without raising an error. (This too is required by the <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > standard.) </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > Prior to <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > 7.2, strings that were too long were always truncated without raising an error, in either explicit or implicit casting contexts. </P ></BLOCKQUOTE ></DIV ><P > The notations <TT CLASS="TYPE" >varchar(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT > and <TT CLASS="TYPE" >char(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT > are aliases for <TT CLASS="TYPE" >character varying(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT > and <TT CLASS="TYPE" >character(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT >, respectively. <TT CLASS="TYPE" >character</TT > without length specifier is equivalent to <TT CLASS="TYPE" >character(1)</TT >. If <TT CLASS="TYPE" >character varying</TT > is used without length specifier, the type accepts strings of any size. The latter is a <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > extension. </P ><P > In addition, <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > provides the <TT CLASS="TYPE" >text</TT > type, which stores strings of any length. Although the type <TT CLASS="TYPE" >text</TT > is not in the <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > standard, several other SQL database management systems have it as well. </P ><P > Values of type <TT CLASS="TYPE" >character</TT > are physically padded with spaces to the specified width <TT CLASS="REPLACEABLE" ><I >n</I ></TT >, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type <TT CLASS="TYPE" >character</TT >, and they will be removed when converting a <TT CLASS="TYPE" >character</TT > value to one of the other string types. Note that trailing spaces <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >are</I ></SPAN > semantically significant in <TT CLASS="TYPE" >character varying</TT > and <TT CLASS="TYPE" >text</TT > values. </P ><P > The storage requirement for data of these types is 4 bytes plus the actual string, and in case of <TT CLASS="TYPE" >character</TT > plus the padding. Long strings are compressed by the system automatically, so the physical requirement on disk may be less. Long values are also stored in background tables so they do not interfere with rapid access to the shorter column values. In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed for <TT CLASS="REPLACEABLE" ><I >n</I ></TT > in the data type declaration is less than that. It wouldn't be very useful to change this because with multibyte character encodings the number of characters and bytes can be quite different anyway. If you desire to store long strings with no specific upper limit, use <TT CLASS="TYPE" >text</TT > or <TT CLASS="TYPE" >character varying</TT > without a length specifier, rather than making up an arbitrary length limit.) </P ><DIV CLASS="TIP" ><BLOCKQUOTE CLASS="TIP" ><P ><B >Tip: </B > There are no performance differences between these three types, apart from the increased storage size when using the blank-padded type. While <TT CLASS="TYPE" >character(<TT CLASS="REPLACEABLE" ><I >n</I ></TT >)</TT > has performance advantages in some other database systems, it has no such advantages in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >. In most situations <TT CLASS="TYPE" >text</TT > or <TT CLASS="TYPE" >character varying</TT > should be used instead. </P ></BLOCKQUOTE ></DIV ><P > Refer to <A HREF="sql-syntax.html#SQL-SYNTAX-STRINGS" >Section 4.1.2.1</A > for information about the syntax of string literals, and to <A HREF="functions.html" >Chapter 9</A > for information about available operators and functions. The database character set determines the character set used to store textual values; for more information on character set support, refer to <A HREF="multibyte.html" >Section 20.2</A >. </P ><DIV CLASS="EXAMPLE" ><A NAME="AEN4000" ></A ><P ><B >Example 8-1. Using the character types</B ></P ><PRE CLASS="PROGRAMLISTING" >CREATE TABLE test1 (a character(4)); INSERT INTO test1 VALUES ('ok'); SELECT a, char_length(a) FROM test1; -- <A NAME="CO.DATATYPE-CHAR" ><B >(1)</B ></A > <SAMP CLASS="COMPUTEROUTPUT" > a | char_length ------+------------- ok | 2</SAMP > CREATE TABLE test2 (b varchar(5)); INSERT INTO test2 VALUES ('ok'); INSERT INTO test2 VALUES ('good '); INSERT INTO test2 VALUES ('too long'); <SAMP CLASS="COMPUTEROUTPUT" >ERROR: value too long for type character varying(5)</SAMP > INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation SELECT b, char_length(b) FROM test2; <SAMP CLASS="COMPUTEROUTPUT" > b | char_length -------+------------- ok | 2 good | 5 too l | 5</SAMP ></PRE ><DIV CLASS="CALLOUTLIST" ><DL COMPACT="COMPACT" ><DT ><A HREF="datatype-character.html#CO.DATATYPE-CHAR" ><B >(1)</B ></A ></DT ><DD > The <CODE CLASS="FUNCTION" >char_length</CODE > function is discussed in <A HREF="functions-string.html" >Section 9.4</A >. </DD ></DL ></DIV ></DIV ><P > There are two other fixed-length character types in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >, shown in <A HREF="datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE" >Table 8-5</A >. The <TT CLASS="TYPE" >name</TT > type exists <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >only</I ></SPAN > for storage of identifiers in the internal system catalogs and is not intended for use by the general user. Its length is currently defined as 64 bytes (63 usable characters plus terminator) but should be referenced using the constant <TT CLASS="SYMBOL" >NAMEDATALEN</TT >. The length is set at compile time (and is therefore adjustable for special uses); the default maximum length may change in a future release. The type <TT CLASS="TYPE" >"char"</TT > (note the quotes) is different from <TT CLASS="TYPE" >char(1)</TT > in that it only uses one byte of storage. It is internally used in the system catalogs as a poor-man's enumeration type. </P ><DIV CLASS="TABLE" ><A NAME="DATATYPE-CHARACTER-SPECIAL-TABLE" ></A ><P ><B >Table 8-5. Special Character Types</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><THEAD ><TR ><TH >Name</TH ><TH >Storage Size</TH ><TH >Description</TH ></TR ></THEAD ><TBODY ><TR ><TD ><TT CLASS="TYPE" >"char"</TT ></TD ><TD >1 byte</TD ><TD >single-character internal type</TD ></TR ><TR ><TD ><TT CLASS="TYPE" >name</TT ></TD ><TD >64 bytes</TD ><TD >internal type for object names</TD ></TR ></TBODY ></TABLE ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="datatype-money.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="datatype-binary.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Monetary Types</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="datatype.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Binary Data Types</TD ></TR ></TABLE ></DIV ></BODY ></HTML >