11<!--
2- $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.53 2001/05/12 22:51:34 petere Exp $
2+ $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.54 2001/05/21 16:54:45 petere Exp $
33-->
44
55 <chapter id="datatype">
@@ -550,65 +550,129 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
550550 <see>character strings</see>
551551 </indexterm>
552552
553- <para>
554- <acronym>SQL</acronym> defines two primary character types:
555- <type>character</type> and <type>character varying</type>.
556- <productname>Postgres</productname> supports these types, in
557- addition to the more general <type>text</type> type,
558- which unlike <type>character varying</type>
559- does not require an explicit declared upper
560- limit on the size of the field.
561- </para>
562-
563- <para>
564- Refer to <xref linkend="sql-syntax-strings"> for information about
565- the syntax of string literals, and to <xref linkend="functions">
566- for information about available operators and functions.
567- </para>
568-
569- <para>
570553 <table tocentry="1">
571554 <title>Character Types</title>
572- <tgroup cols="4 ">
555+ <tgroup cols="3 ">
573556 <thead>
574557 <row>
575558<entry>Type Name</entry>
576559<entry>Storage</entry>
577- <entry>Recommendation</entry>
578560<entry>Description</entry>
579561 </row>
580562 </thead>
581563 <tbody>
582564 <row>
583565<entry>character(n), char(n)</entry>
584566<entry>(4+n) bytes</entry>
585- <entry><acronym>SQL</acronym>-compatible</entry>
586567<entry>Fixed-length blank padded</entry>
587568 </row>
588569 <row>
589570<entry>character varying(n), varchar(n)</entry>
590571<entry>(4+n) bytes</entry>
591- <entry><acronym>SQL</acronym>-compatible</entry>
592572<entry>Variable-length with limit</entry>
593573 </row>
594574 <row>
595575<entry>text</entry>
596576<entry>(4+n) bytes</entry>
597- <entry>Most flexible</entry>
598577<entry>Variable unlimited length</entry>
599578 </row>
600579 </tbody>
601580 </tgroup>
602581 </table>
603582
604- <note>
605- <para>
606- Although the type <type>text</type> is not SQL-compliant, many
607- other RDBMS packages have it as well.
608- </para>
609- </note>
583+ <para>
584+ <acronym>SQL</acronym> defines two primary character types:
585+ <type>character(<replaceable>n</>)</type> and <type>character
586+ varying(<replaceable>n</>)</type>, where <replaceable>n</> is a
587+ positive integer. Both of these types can store strings up to
588+ <replaceable>n</> characters in length. An attempt to store a
589+ longer string into a column of these types will result in an
590+ error, unless the excess characters are all spaces, in which case
591+ the string will be truncated to the maximum length. (This
592+ somewhat bizarre exception is required by the SQL standard.) If
593+ the string to be stored is shorter than the declared length,
594+ values of type <type>character</type> will be space-padded; values
595+ of type <type>character varying</type> will simply store the
596+ shorter string.
597+ </para>
598+
599+ <note>
600+ <para>
601+ Prior to PostgreSQL 7.2, strings that were too long were silently
602+ truncated, no error was raised.
603+ </para>
604+ </note>
605+
606+ <para>
607+ The notations <type>char(<replaceable>n</>)</type> and
608+ <type>varchar(<replaceable>n</>)</type> are aliases for
609+ <type>character(<replaceable>n</>)</type> and <type>character
610+ varying(<replaceable>n</>)</type>,
611+ respectively. <type>character</type> without length specifier is
612+ equivalent to <type>character(1)</type>; if <type>character
613+ varying</type> is used without length specifier, the type accepts
614+ strings of any size. The latter is a PostgreSQL extension.
610615 </para>
611616
617+ <para>
618+ In addition, <productname>PostgreSQL</productname> supports the
619+ more general <type>text</type> type, which stores strings of any
620+ length. Unlike <type>character varying</type>, <type>text</type>
621+ does not require an explicit declared upper limit on the size of
622+ the string. Although the type <type>text</type> is not in the SQL
623+ standard, many other RDBMS packages have it as well.
624+ </para>
625+
626+ <para>
627+ Refer to <xref linkend="sql-syntax-strings"> for information about
628+ the syntax of string literals, and to <xref linkend="functions">
629+ for information about available operators and functions.
630+ </para>
631+
632+ <tip>
633+ <para>
634+ There are no performance differences between these three types,
635+ apart from the increased storage size when using the blank-padded
636+ type.
637+ </para>
638+ </tip>
639+
640+ <example>
641+ <title>Using the character types</title>
642+
643+ <programlisting>
644+ CREATE TABLE test1 (a character(4));
645+ INSERT INTO test1 VALUES ('ok');
646+ SELECT a, char_length(a) FROM test1; -- <co id="co.datatype-char">
647+ <computeroutput>
648+ a | char_length
649+ ------+-------------
650+ ok | 4
651+ </computeroutput>
652+
653+ CREATE TABLE test2 (b varchar(5));
654+ INSERT INTO test2 VALUES ('ok');
655+ INSERT INTO test2 VALUES ('good ');
656+ INSERT INTO test2 VALUES ('too long');
657+ <computeroutput>ERROR: value too long for type character varying(5)</computeroutput>
658+ SELECT b, char_length(b) FROM test2;
659+ <computeroutput>
660+ b | char_length
661+ -------+-------------
662+ ok | 2
663+ good | 5
664+ </computeroutput>
665+ </programlisting>
666+ <calloutlist>
667+ <callout arearefs="co.datatype-char">
668+ <para>
669+ The <function>char_length</function> function is discussed in
670+ <xref linkend="functions-string">.
671+ </para>
672+ </callout>
673+ </calloutlist>
674+ </example>
675+
612676 <para>
613677 There are two other fixed-length character types in
614678 <productname>Postgres</productname>. The <type>name</type> type
@@ -625,7 +689,6 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
625689 enumeration type.
626690 </para>
627691
628- <para>
629692 <table tocentry="1">
630693 <title>Specialty Character Type</title>
631694 <tgroup cols="3">
@@ -650,7 +713,6 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
650713 </tbody>
651714 </tgroup>
652715 </table>
653- </para>
654716
655717 </sect1>
656718