~ubuntu-branches/ubuntu/trusty/postgresql-9.3/trusty-updates : contents of doc/src/sgml/html/datatype-character.html at revision 27

~ubuntu-branches/ubuntu/trusty/postgresql-9.3/trusty-updates : (revision 27)
<!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 9.3.13 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="2016-05-09T21:13:26"></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"
><A
HREF="index.html"
>PostgreSQL 9.3.13 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Monetary Types"
HREF="datatype-money.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 8. Data Types</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Binary Data Types"
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
><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 (not bytes) 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
><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, and
    when using pattern matching, e.g. <TT
CLASS="LITERAL"
>LIKE</TT
>,
    regular expressions.
   </P
><P
>    The storage requirement for a short string (up to 126 bytes) is 1 byte
    plus the actual string, which includes the space padding in the case of
    <TT
CLASS="TYPE"
>character</TT
>.  Longer strings have 4 bytes of overhead instead
    of 1.  Long strings are compressed by the system automatically, so
    the physical requirement on disk might be less. Very long values are also
    stored in background tables so that they do not interfere with rapid
    access to 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 useful to
    change this because with multibyte character encodings the number of
    characters and bytes can be quite different. 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 is no performance difference among these three types,
     apart from increased storage space when using the blank-padded
     type, and a few extra CPU cycles to check the length when storing into
     a length-constrained column.  While
     <TT
CLASS="TYPE"
>character(<TT
CLASS="REPLACEABLE"
><I
>n</I
></TT
>)</TT
> has performance
     advantages in some other database systems, there is no such advantage in
     <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>; in fact
     <TT
CLASS="TYPE"
>character(<TT
CLASS="REPLACEABLE"
><I
>n</I
></TT
>)</TT
> is usually the slowest of
     the three because of its additional storage costs.  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-lexical.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 22.3</A
>.
   </P
><DIV
CLASS="EXAMPLE"
><A
NAME="AEN5298"
></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 the 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
> in <TT
CLASS="LITERAL"
>C</TT
> source code.
    The length is set at compile time (and
    is therefore adjustable for special uses); the default maximum
    length might 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 simplistic 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-byte 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
>