1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>8.3.Character Types</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="datatype.html" title="Chapter8.Data Types">
<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">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="datatype-character"></a>8.3.Character Types</h2></div></div></div>
<a name="id585554"></a><a name="id585568"></a><a name="id585577"></a><a name="id585588"></a><a name="id585598"></a><a name="id585609"></a><a name="id585620"></a><div class="table">
<a name="datatype-character-table"></a><p class="title"><b>Table8.4.Character Types</b></p>
<div class="table-contents"><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 href="datatype-character.html#datatype-character-table" title="Table8.4.Character Types">Table8.4, “Character Types”</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 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>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> 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>
</div>
<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, the padding spaces are
treated as semantically insignificant. Trailing spaces are
disregarded when comparing two values of type <code class="type">character</code>,
and they will be 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.
</p>
<p> The storage requirement for data of these types is 4 bytes plus the
actual string, and in case of <code class="type">character</code> 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 <em class="replaceable"><code>n</code></em> 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
<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" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Tip</h3>
<p> There are no performance differences between these three types,
apart from the increased storage size when using the blank-padded
type. While <code class="type">character(<em class="replaceable"><code>n</code></em>)</code> has performance
advantages in some other database systems, it has no such advantages in
<span class="productname">PostgreSQL</span>. 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 href="sql-syntax.html#sql-syntax-strings" title="4.1.2.1.String Constants">Section4.1.2.1, “String Constants”</a> for information about
the syntax of string literals, and to <a href="functions.html" title="Chapter9.Functions and Operators">Chapter9, <i>Functions and Operators</i></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" title="21.2.Character Set Support">Section21.2, “Character Set Support”</a>.
</p>
<div class="example">
<a name="id586027"></a><p class="title"><b>Example8.1.Using the character types</b></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; -- <a name="co.datatype-char"></a>(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">
<a href="#co.datatype-char">(1)</a> </td>
<td valign="top" align="left"><p> The <code class="function">char_length</code> function is discussed in
<a href="functions-string.html" title="9.4.String Functions and Operators">Section9.4, “String Functions and Operators”</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 href="datatype-character.html#datatype-character-special-table" title="Table8.5.Special Character Types">Table8.5, “Special Character Types”</a>. The <code class="type">name</code>
type exists <span class="emphasis"><em>only</em></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
<code class="symbol">NAMEDATALEN</code>. 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 <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 poor-man's enumeration type.
</p>
<div class="table">
<a name="datatype-character-special-table"></a><p class="title"><b>Table8.5.Special Character Types</b></p>
<div class="table-contents"><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-character 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></body>
</html>
|
