character

fixed-length character string

当前阅读 PG 18·选择有来源记录的版本

此版本暂无所选语言的定义，以下显示原始英文内容。

Catalog name: pg_catalog.bpchar
Type OID: 1042
Type kind: Base type
Declared length: Variable length (varlena)
Storage strategy: extended
Input function: bpcharin
Output function: bpcharout
Documented declaration: character( n ) , char( n ) , bpchar( n )
Manual description: fixed-length, blank-padded
Documented declaration: bpchar
Manual description: variable unlimited length, blank-trimmed
aliases: bpchar, char [ ( n ) ]
coverage: source inventory; exact declared input types for operator classes
manual documentation: dedicated family chapter
manual path: datatype-character.html
ranges: 未知
signature: character [ ( n ) ]

版本定义 PG 18

8.3. Character Types

Table 8.4. Character Types

Name	Description
`character varying(n)`, `varchar(n)`	variable-length with limit
`character(n)`, `char(n)`, `bpchar(n)`	fixed-length, blank-padded
`bpchar`	variable unlimited length, blank-trimmed
`text`	variable unlimited length

Table 8.4 shows the general-purpose character types available in PostgreSQL.

SQL defines two primary character types: character varying(n) and character(n), where n is a positive integer. Both of these types can store strings up to n 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 SQL standard.) However, if one explicitly casts a value to character varying(n) or character(n), then an over-length value will be truncated to n characters without raising an error. (This too is required by the SQL standard.) If the string to be stored is shorter than the declared length, values of type character will be space-padded; values of type character varying will simply store the shorter string.

In addition, PostgreSQL provides the text type, which stores strings of any length. Although the text type is not in the SQL standard, several other SQL database management systems have it as well. text is PostgreSQL's native string data type, in that most built-in functions operating on strings are declared to take or return text not character varying. For many purposes, character varying acts as though it were a domain over text.

The type name varchar is an alias for character varying, while bpchar (with length specifier) and char are aliases for character. The varchar and char aliases are defined in the SQL standard; bpchar is a PostgreSQL extension.

If specified, the length n must be greater than zero and cannot exceed 10,485,760. If character varying (or varchar) is used without length specifier, the type accepts strings of any length. If bpchar lacks a length specifier, it also accepts strings of any length, but trailing spaces are semantically insignificant. If character (or char) lacks a specifier, it is equivalent to character(1).

Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type character. In collations where whitespace is significant, this behavior can produce unexpected results; for example SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) returns true, even though C locale would consider a space to be greater than a newline. Trailing spaces are removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values, and when using pattern matching, that is LIKE and regular expressions.

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 Section 23.3.

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 character. 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 n 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 text or character varying without a length specifier, rather than making up an arbitrary length limit.)

Tip

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 character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

Refer to Section 4.1.2.1 for information about the syntax of string literals, and to Chapter 9 for information about available operators and functions.

Example 8.1. Using the Character Types

CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)

  a   | char_length
------+-------------
 ok   |           2


CREATE TABLE test2 (b varchar(5));
INSERT INTO test2 VALUES ('ok');
INSERT INTO test2 VALUES ('good      ');
INSERT INTO test2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
SELECT b, char_length(b) FROM test2;

   b   | char_length
-------+-------------
 ok    |           2
 good  |           5
 too l |           5

(1)	The `char_length` function is discussed in Section 9.4.

There are two other fixed-length character types in PostgreSQL, shown in Table 8.5. These are not intended for general-purpose use, only for use in the internal system catalogs. The name 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 NAMEDATALEN in C 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 "char" (note the quotes) is different from char(1) 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.

Table 8.5. Special Character Types

Name	Storage Size	Description
`"char"`	1 byte	single-byte internal type
`name`	64 bytes	internal type for object names

比较版本

完整来源事实

casts

map[castcontext:i castfunc:0 castmethod:b castsource:text casttarget:bpchar], map[castcontext:i castfunc:text(bpchar) castmethod:f castsource:bpchar casttarget:text], map[castcontext:i castfunc:text(bpchar) castmethod:f castsource:bpchar casttarget:varchar], map[castcontext:i castfunc:0 castmethod:b castsource:varchar casttarget:bpchar], map[castcontext:a castfunc:bpchar(char) castmethod:f castsource:char casttarget:bpchar], map[castcontext:a castfunc:bpchar(name) castmethod:f castsource:name casttarget:bpchar], map[castcontext:a castfunc:char(text) castmethod:f castsource:bpchar casttarget:char], map[castcontext:i castfunc:name(bpchar) castmethod:f castsource:bpchar casttarget:name], map[castcontext:a castfunc:text(inet) castmethod:f castsource:cidr casttarget:bpchar], map[castcontext:a castfunc:text(inet) castmethod:f castsource:inet casttarget:bpchar], map[castcontext:a castfunc:text(bool) castmethod:f castsource:bool casttarget:bpchar], map[castcontext:a castfunc:0 castmethod:b castsource:xml casttarget:bpchar], map[castcontext:e castfunc:xml castmethod:f castsource:bpchar casttarget:xml], map[castcontext:i castfunc:bpchar(bpchar,int4,bool) castmethod:f castsource:bpchar casttarget:bpchar]

catalog

{"array_type_name":"_bpchar","array_type_oid":"1014","descr":"'char(length)' blank-padded string, fixed storage length","oid":"1042","typacl":"_null_","typalign":"i","typanalyze":"-","typarray":"0","typbasetype":"0","typbyval":"f","typcategory":"S","typcollation":"default","typdefault":"_null_","typdefaultbin":"_null_","typdelim":"','","typelem":"0","typinput":"bpcharin","typisdefined":"t","typispreferred":"f","typlen":"-1","typmodin":"bpchartypmodin","typmodout":"bpchartypmodout","typname":"bpchar","typnamespace":"pg_catalog","typndims":"0","typnotnull":"f","typoutput":"bpcharout","typowner":"POSTGRES","typreceive":"bpcharrecv","typrelid":"0","typsend":"bpcharsend","typstorage":"x","typsubscript":"-","typtype":"b","typtypmod":"-1"}

operator classes

map[opcdefault:t opcfamily:btree/bpchar_ops opcintype:bpchar opckeytype:0 opcmethod:btree opcname:bpchar_ops opcnamespace:pg_catalog opcowner:POSTGRES], map[opcdefault:t opcfamily:hash/bpchar_ops opcintype:bpchar opckeytype:0 opcmethod:hash opcname:bpchar_ops opcnamespace:pg_catalog opcowner:POSTGRES], map[oid:4219 oid_symbol:BPCHAR_BTREE_PATTERN_OPS_OID opcdefault:f opcfamily:btree/bpchar_pattern_ops opcintype:bpchar opckeytype:0 opcmethod:btree opcname:bpchar_pattern_ops opcnamespace:pg_catalog opcowner:POSTGRES], map[opcdefault:f opcfamily:hash/bpchar_pattern_ops opcintype:bpchar opckeytype:0 opcmethod:hash opcname:bpchar_pattern_ops opcnamespace:pg_catalog opcowner:POSTGRES], map[opcdefault:t opcfamily:brin/bpchar_minmax_ops opcintype:bpchar opckeytype:bpchar opcmethod:brin opcname:bpchar_minmax_ops opcnamespace:pg_catalog opcowner:POSTGRES], map[opcdefault:f opcfamily:brin/bpchar_bloom_ops opcintype:bpchar opckeytype:bpchar opcmethod:brin opcname:bpchar_bloom_ops opcnamespace:pg_catalog opcowner:POSTGRES]

operators

map[descr:equal oid:1054 oid_symbol:BpcharEqualOperator oprcanhash:t oprcanmerge:t oprcode:bpchareq oprcom:=(bpchar,bpchar) oprjoin:eqjoinsel oprkind:b oprleft:bpchar oprname:= oprnamespace:pg_catalog oprnegate:<>(bpchar,bpchar) oprowner:POSTGRES oprrest:eqsel oprresult:bool oprright:bpchar], map[descr:matches regular expression, case-sensitive oid:1055 oid_symbol:OID_BPCHAR_REGEXEQ_OP oprcanhash:f oprcanmerge:f oprcode:bpcharregexeq oprcom:0 oprjoin:regexeqjoinsel oprkind:b oprleft:bpchar oprname:~ oprnamespace:pg_catalog oprnegate:!~(bpchar,text) oprowner:POSTGRES oprrest:regexeqsel oprresult:bool oprright:text], map[descr:does not match regular expression, case-sensitive oid:1056 oprcanhash:f oprcanmerge:f oprcode:bpcharregexne oprcom:0 oprjoin:regexnejoinsel oprkind:b oprleft:bpchar oprname:!~ oprnamespace:pg_catalog oprnegate:~(bpchar,text) oprowner:POSTGRES oprrest:regexnesel oprresult:bool oprright:text], map[descr:not equal oid:1057 oprcanhash:f oprcanmerge:f oprcode:bpcharne oprcom:<>(bpchar,bpchar) oprjoin:neqjoinsel oprkind:b oprleft:bpchar oprname:<> oprnamespace:pg_catalog oprnegate:=(bpchar,bpchar) oprowner:POSTGRES oprrest:neqsel oprresult:bool oprright:bpchar], map[descr:less than oid:1058 oid_symbol:BpcharLessOperator oprcanhash:f oprcanmerge:f oprcode:bpcharlt oprcom:>(bpchar,bpchar) oprjoin:scalarltjoinsel oprkind:b oprleft:bpchar oprname:< oprnamespace:pg_catalog oprnegate:>=(bpchar,bpchar) oprowner:POSTGRES oprrest:scalarltsel oprresult:bool oprright:bpchar], map[descr:less than or equal oid:1059 oprcanhash:f oprcanmerge:f oprcode:bpcharle oprcom:>=(bpchar,bpchar) oprjoin:scalarlejoinsel oprkind:b oprleft:bpchar oprname:<= oprnamespace:pg_catalog oprnegate:>(bpchar,bpchar) oprowner:POSTGRES oprrest:scalarlesel oprresult:bool oprright:bpchar], map[descr:greater than oid:1060 oprcanhash:f oprcanmerge:f oprcode:bpchargt oprcom:<(bpchar,bpchar) oprjoin:scalargtjoinsel oprkind:b oprleft:bpchar oprname:> oprnamespace:pg_catalog oprnegate:<=(bpchar,bpchar) oprowner:POSTGRES oprrest:scalargtsel oprresult:bool oprright:bpchar], map[descr:greater than or equal oid:1061 oid_symbol:BpcharGreaterEqualOperator oprcanhash:f oprcanmerge:f oprcode:bpcharge oprcom:<=(bpchar,bpchar) oprjoin:scalargejoinsel oprkind:b oprleft:bpchar oprname:>= oprnamespace:pg_catalog oprnegate:<(bpchar,bpchar) oprowner:POSTGRES oprrest:scalargesel oprresult:bool oprright:bpchar], map[descr:matches LIKE expression oid:1211 oid_symbol:OID_BPCHAR_LIKE_OP oprcanhash:f oprcanmerge:f oprcode:bpcharlike oprcom:0 oprjoin:likejoinsel oprkind:b oprleft:bpchar oprname:~~ oprnamespace:pg_catalog oprnegate:!~~(bpchar,text) oprowner:POSTGRES oprrest:likesel oprresult:bool oprright:text], map[descr:does not match LIKE expression oid:1212 oprcanhash:f oprcanmerge:f oprcode:bpcharnlike oprcom:0 oprjoin:nlikejoinsel oprkind:b oprleft:bpchar oprname:!~~ oprnamespace:pg_catalog oprnegate:~~(bpchar,text) oprowner:POSTGRES oprrest:nlikesel oprresult:bool oprright:text], map[descr:matches regular expression, case-insensitive oid:1234 oid_symbol:OID_BPCHAR_ICREGEXEQ_OP oprcanhash:f oprcanmerge:f oprcode:bpcharicregexeq oprcom:0 oprjoin:icregexeqjoinsel oprkind:b oprleft:bpchar oprname:~* oprnamespace:pg_catalog oprnegate:!~*(bpchar,text) oprowner:POSTGRES oprrest:icregexeqsel oprresult:bool oprright:text], map[descr:does not match regular expression, case-insensitive oid:1235 oprcanhash:f oprcanmerge:f oprcode:bpcharicregexne oprcom:0 oprjoin:icregexnejoinsel oprkind:b oprleft:bpchar oprname:!~* oprnamespace:pg_catalog oprnegate:~*(bpchar,text) oprowner:POSTGRES oprrest:icregexnesel oprresult:bool oprright:text], map[descr:matches LIKE expression, case-insensitive oid:1629 oid_symbol:OID_BPCHAR_ICLIKE_OP oprcanhash:f oprcanmerge:f oprcode:bpchariclike oprcom:0 oprjoin:iclikejoinsel oprkind:b oprleft:bpchar oprname:~~* oprnamespace:pg_catalog oprnegate:!~~*(bpchar,text) oprowner:POSTGRES oprrest:iclikesel oprresult:bool oprright:text], map[descr:does not match LIKE expression, case-insensitive oid:1630 oprcanhash:f oprcanmerge:f oprcode:bpcharicnlike oprcom:0 oprjoin:icnlikejoinsel oprkind:b oprleft:bpchar oprname:!~~* oprnamespace:pg_catalog oprnegate:~~*(bpchar,text) oprowner:POSTGRES oprrest:icnlikesel oprresult:bool oprright:text], map[descr:less than oid:2326 oid_symbol:BpcharPatternLessOperator oprcanhash:f oprcanmerge:f oprcode:bpchar_pattern_lt oprcom:~>~(bpchar,bpchar) oprjoin:scalarltjoinsel oprkind:b oprleft:bpchar oprname:~<~ oprnamespace:pg_catalog oprnegate:~>=~(bpchar,bpchar) oprowner:POSTGRES oprrest:scalarltsel oprresult:bool oprright:bpchar], map[descr:less than or equal oid:2327 oprcanhash:f oprcanmerge:f oprcode:bpchar_pattern_le oprcom:~>=~(bpchar,bpchar) oprjoin:scalarlejoinsel oprkind:b oprleft:bpchar oprname:~<=~ oprnamespace:pg_catalog oprnegate:~>~(bpchar,bpchar) oprowner:POSTGRES oprrest:scalarlesel oprresult:bool oprright:bpchar], map[descr:greater than or equal oid:2329 oid_symbol:BpcharPatternGreaterEqualOperator oprcanhash:f oprcanmerge:f oprcode:bpchar_pattern_ge oprcom:~<=~(bpchar,bpchar) oprjoin:scalargejoinsel oprkind:b oprleft:bpchar oprname:~>=~ oprnamespace:pg_catalog oprnegate:~<~(bpchar,bpchar) oprowner:POSTGRES oprrest:scalargesel oprresult:bool oprright:bpchar], map[descr:greater than oid:2330 oprcanhash:f oprcanmerge:f oprcode:bpchar_pattern_gt oprcom:~<~(bpchar,bpchar) oprjoin:scalargtjoinsel oprkind:b oprleft:bpchar oprname:~>~ oprnamespace:pg_catalog oprnegate:~<=~(bpchar,bpchar) oprowner:POSTGRES oprrest:scalargtsel oprresult:bool oprright:bpchar]

来源引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · 555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f

正文语言: en · 555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f