<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>8.3. Character Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="datatype-money.html" title="8.2. Monetary Types" /><link rel="next" href="datatype-binary.html" title="8.4. Binary Data Types" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">8.3. Character Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="datatype-money.html" title="8.2. Monetary Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><th width="60%" align="center">Chapter 8. Data Types</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="datatype-binary.html" title="8.4. Binary Data Types">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="DATATYPE-CHARACTER"><div class="titlepage"><div><div><h2 class="title" style="clear: both">8.3. Character Types</h2></div></div></div><a id="id-1.5.7.11.2" class="indexterm"></a><a id="id-1.5.7.11.3" class="indexterm"></a><a id="id-1.5.7.11.4" class="indexterm"></a><a id="id-1.5.7.11.5" class="indexterm"></a><a id="id-1.5.7.11.6" class="indexterm"></a><a id="id-1.5.7.11.7" class="indexterm"></a><a id="id-1.5.7.11.8" class="indexterm"></a><div class="table" id="DATATYPE-CHARACTER-TABLE"><p class="title"><strong>Table 8.4. Character Types</strong></p><div class="table-contents"><table class="table" summary="Character Types" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Name</th><th>Description</th></tr></thead><tbody><tr><td><code class="type">character varying(<em class="replaceable"><code>n</code></em>)</code>, <code class="type">varchar(<em class="replaceable"><code>n</code></em>)</code></td><td>variable-length with limit</td></tr><tr><td><code class="type">character(<em class="replaceable"><code>n</code></em>)</code>, <code class="type">char(<em class="replaceable"><code>n</code></em>)</code></td><td>fixed-length, blank padded</td></tr><tr><td><code class="type">text</code></td><td>variable unlimited length</td></tr></tbody></table></div></div><br class="table-break" /><p> <a class="xref" href="datatype-character.html#DATATYPE-CHARACTER-TABLE" title="Table 8.4. Character Types">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: <code class="type">character varying(<em class="replaceable"><code>n</code></em>)</code> and <code class="type">character(<em class="replaceable"><code>n</code></em>)</code>, where <em class="replaceable"><code>n</code></em> is a positive integer. Both of these types can store strings up to <em class="replaceable"><code>n</code></em> 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 <code class="type">character</code> will be space-padded; values of type <code class="type">character varying</code> will simply store the shorter string. </p><p> If one explicitly casts a value to <code class="type">character varying(<em class="replaceable"><code>n</code></em>)</code> or <code class="type">character(<em class="replaceable"><code>n</code></em>)</code>, then an over-length value will be truncated to <em class="replaceable"><code>n</code></em> characters without raising an error. (This too is required by the <acronym class="acronym">SQL</acronym> standard.) </p><p> The notations <code class="type">varchar(<em class="replaceable"><code>n</code></em>)</code> and <code class="type">char(<em class="replaceable"><code>n</code></em>)</code> are aliases for <code class="type">character varying(<em class="replaceable"><code>n</code></em>)</code> and <code class="type">character(<em class="replaceable"><code>n</code></em>)</code>, respectively. <code class="type">character</code> without length specifier is equivalent to <code class="type">character(1)</code>. If <code class="type">character varying</code> 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 <code class="type">text</code> type, which stores strings of any length. Although the type <code class="type">text</code> 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 <code class="type">character</code> are physically padded with spaces to the specified width <em class="replaceable"><code>n</code></em>, and are stored and displayed that way. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type <code class="type">character</code>. In collations where whitespace is significant, this behavior can produce unexpected results; for example <code class="command">SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)</code> returns true, even though <code class="literal">C</code> locale would consider a space to be greater than a newline. Trailing spaces are removed when converting a <code class="type">character</code> value to one of the other string types. Note that trailing spaces <span class="emphasis"><em>are</em></span> semantically significant in <code class="type">character varying</code> and <code class="type">text</code> values, and when using pattern matching, that is <code class="literal">LIKE</code> and 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 <code class="type">character</code>. 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 <em class="replaceable"><code>n</code></em> 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 <code class="type">text</code> or <code class="type">character varying</code> without a length specifier, rather than making up an arbitrary length limit.) </p><div class="tip"><h3 class="title">Tip</h3><p> 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 <code class="type">character(<em class="replaceable"><code>n</code></em>)</code> has performance advantages in some other database systems, there is no such advantage in <span class="productname">PostgreSQL</span>; in fact <code class="type">character(<em class="replaceable"><code>n</code></em>)</code> is usually the slowest of the three because of its additional storage costs. In most situations <code class="type">text</code> or <code class="type">character varying</code> should be used instead. </p></div><p> Refer to <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a> for information about the syntax of string literals, and to <a class="xref" href="functions.html" title="Chapter 9. Functions and Operators">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 class="xref" href="multibyte.html" title="23.3. Character Set Support">Section 23.3</a>. </p><div class="example" id="id-1.5.7.11.19"><p class="title"><strong>Example 8.1. Using the Character Types</strong></p><div class="example-contents"><pre class="programlisting"> CREATE TABLE test1 (a character(4)); INSERT INTO test1 VALUES ('ok'); SELECT a, char_length(a) FROM test1; -- <span id="co.datatype-char"></span>(1) <code class="computeroutput"> a | char_length ------+------------- ok | 2 </code> CREATE TABLE test2 (b varchar(5)); INSERT INTO test2 VALUES ('ok'); INSERT INTO test2 VALUES ('good '); INSERT INTO test2 VALUES ('too long'); <code class="computeroutput">ERROR: value too long for type character varying(5)</code> INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation SELECT b, char_length(b) FROM test2; <code class="computeroutput"> b | char_length -------+------------- ok | 2 good | 5 too l | 5 </code> </pre><div class="calloutlist"><table border="0" summary="Callout list"><tr><td width="5%" valign="top" align="left"><p><a href="#co.datatype-char">(1)</a> </p></td><td valign="top" align="left"><p> The <code class="function">char_length</code> function is discussed in <a class="xref" href="functions-string.html" title="9.4. String Functions and Operators">Section 9.4</a>. </p></td></tr></table></div></div></div><br class="example-break" /><p> There are two other fixed-length character types in <span class="productname">PostgreSQL</span>, shown in <a class="xref" href="datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE" title="Table 8.5. Special Character Types">Table 8.5</a>. The <code class="type">name</code> type exists <span class="emphasis"><em>only</em></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 <code class="symbol">NAMEDATALEN</code> in <code class="literal">C</code> 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 <code class="type">"char"</code> (note the quotes) is different from <code class="type">char(1)</code> 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" id="DATATYPE-CHARACTER-SPECIAL-TABLE"><p class="title"><strong>Table 8.5. Special Character Types</strong></p><div class="table-contents"><table class="table" summary="Special Character Types" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Storage Size</th><th>Description</th></tr></thead><tbody><tr><td><code class="type">"char"</code></td><td>1 byte</td><td>single-byte internal type</td></tr><tr><td><code class="type">name</code></td><td>64 bytes</td><td>internal type for object names</td></tr></tbody></table></div></div><br class="table-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="datatype-money.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype-binary.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.2. Monetary Types </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 8.4. Binary Data Types</td></tr></table></div></body></html>