diff options
Diffstat (limited to 'doc/src/sgml/html/datatype-character.html')
| -rw-r--r-- | doc/src/sgml/html/datatype-character.html | 142 |
1 files changed, 142 insertions, 0 deletions
diff --git a/doc/src/sgml/html/datatype-character.html b/doc/src/sgml/html/datatype-character.html new file mode 100644 index 0000000..4e127d0 --- /dev/null +++ b/doc/src/sgml/html/datatype-character.html @@ -0,0 +1,142 @@ +<?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 id="docContent" class="container-fluid col-10"><div 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 15.5 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 /></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. + If specified, the length must be greater than zero and cannot exceed + 10485760. + <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 characters that can be stored in any of these data types are + determined by the database character set, which is selected when + the database is created. Regardless of the specific character set, + the character with code zero (sometimes called NUL) cannot be stored. + For more information refer to <a class="xref" href="multibyte.html" title="24.3. Character Set Support">Section 24.3</a>. + </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. + </p><div class="example" id="id-1.5.7.11.20"><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>. + These are not intended for general-purpose use, only for use + in the internal system catalogs. + The <code class="type">name</code> type is used to store identifiers. 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, and therefore can store only a single + ASCII character. It is 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" title="8.2. Monetary Types">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype-binary.html" title="8.4. Binary Data Types">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" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.4. Binary Data Types</td></tr></table></div></body></html>
\ No newline at end of file |