您现在的位置是：首页 >技术交流 >PostgreSQL类型系统——Data Types网站首页技术交流

PostgreSQL类型系统——Data Types

肥叔菌 2024-06-11 12:00:02
简介PostgreSQL类型系统——Data Types

PostgreSQL Data Types

PostgreSQL has a rich set of native data types available to users. Users can add new types to PostgreSQL using the CREATE TYPE command. PostgreSQL有一组丰富的本地数据类型可供用户使用。用户可以使用CREATE TYPE命令向PostgreSQL添加新类型。Each data type has an external representation determined by its input and output functions. Many of the built-in types have obvious external formats. However, several types are either unique to PostgreSQL, such as geometric paths, or have several possible formats, such as the date and time types. Some of the input and output functions are not invertible, i.e., the result of an output function might lose accuracy when compared to the original input. 每种数据类型都有一个由其输入和输出函数确定的外部表示。许多内置类型都有明显的外部格式。然而，有几种类型要么是PostgreSQL独有的，例如几何路径，要么有几种可能的格式，例如日期和时间类型。一些输入和输出函数是不可逆的，即，与原始输入相比，输出函数的结果可能会失去准确性。
Table 8.1 shows all the built-in general-purpose data types. Most of the alternative names listed in the “Aliases” column are the names used internally by PostgreSQL for historical reasons. In addition, some internally used or deprecated types are available, but are not listed here. 表8.1显示了所有内置的通用数据类型。“别名”列中列出的大多数备选名称都是PostgreSQL内部出于历史原因使用的名称。此外，一些内部使用或不推荐使用的类型也可用，但此处未列出。
在这里插入图片描述

Type syntax

The following types (or spellings thereof) are specified by SQL: bigint, bit, bit varying, boolean, char, character varying, character, varchar, date, double precision, integer, interval, numeric, decimal, real, smallint, time (with or without time zone), timestamp (with or without time zone), xml. SQL指定以下类型（或其拼写）：bigint、bit、bit variation、boolean、char、character variation、character、varchar、date、双精度、integer、interval、numeric、decimal、real、smallint、time（有或没有时区）、timestamp（有或不带时区）、xml。表8.1中除了SQL指定之外的通用数据类型为:bigserial,box,bytea,cidr,circle,inet,json,jsonb,line,lseg,macaddr,macaddr8,money,path,pg_lsn,pg_snapshot,point,polygon,smallserial,serial,text,tsquery,tsvector,txid_snapshot,uuid。

SQL引入了大量特定于类型的语法。定义单独的子句来处理这些事例，并使用泛型事例来处理规则类型的可扩展Postgres语法。SQL introduces a large amount of type-specific syntax. Define individual clauses to handle these cases, and use the generic case to handle regular type-extensible Postgres syntax.

Typename:	SimpleTypename opt_array_bounds
				{ $$ = $1; $$->arrayBounds = $2; }
			| SETOF SimpleTypename opt_array_bounds
				{ $$ = $2; $$->arrayBounds = $3; $$->setof = true; }
				
			/* SQL standard syntax, currently only one-dimensional */
			| SimpleTypename ARRAY '[' Iconst ']'
				{ $$ = $1; $$->arrayBounds = list_make1(makeInteger($4)); }
			| SETOF SimpleTypename ARRAY '[' Iconst ']'
				{ $$ = $2; $$->arrayBounds = list_make1(makeInteger($5)); $$->setof = true; }
				
			| SimpleTypename ARRAY
				{ $$ = $1; $$->arrayBounds = list_make1(makeInteger(-1)); }
			| SETOF SimpleTypename ARRAY
				{ $$ = $2; $$->arrayBounds = list_make1(makeInteger(-1)); $$->setof = true; }
SimpleTypename:
			GenericType								{ $$ = $1; }  <- 使用泛型事例来处理规则类型的可扩展Postgres语法
			| Numeric								{ $$ = $1; }
			| Bit									{ $$ = $1; }
			| Character								{ $$ = $1; }
			| ConstDatetime							{ $$ = $1; }
			| ConstInterval opt_interval            { $$ = $1; $$->typmods = $2; }
			| ConstInterval '(' Iconst ')'          { $$ = $1; $$->typmods = list_make2(makeIntConst(INTERVAL_FULL_RANGE, -1), makeIntConst($3, @3)); }

SQL Data Types

Numeric规则为SQL numeric类型，包含如下类型：integer【int4】、smallint【int2】、bigint【int8】、real【float4】、double precision【float8】、decimal【numeric】、numeric【numeric】、boolean【bool】。

Numeric:	INT_P { $$ = SystemTypeName("int4"); $$->location = @1; }
			| INTEGER { $$ = SystemTypeName("int4"); $$->location = @1; }
			| SMALLINT { $$ = SystemTypeName("int2"); $$->location = @1; }
			| BIGINT { $$ = SystemTypeName("int8"); $$->location = @1; }
			| REAL { $$ = SystemTypeName("float4"); $$->location = @1; }
			| FLOAT_P opt_float { $$ = $2; $$->location = @1; }
			| DOUBLE_P PRECISION { $$ = SystemTypeName("float8"); $$->location = @1; }
			| DECIMAL_P opt_type_modifiers { $$ = SystemTypeName("numeric"); $$->typmods = $2; $$->location = @1; }
			| DEC opt_type_modifiers { $$ = SystemTypeName("numeric"); $$->typmods = $2; $$->location = @1; }
			| NUMERIC opt_type_modifiers { $$ = SystemTypeName("numeric"); $$->typmods = $2; $$->location = @1; }
			| BOOLEAN_P { $$ = SystemTypeName("bool"); $$->location = @1; }

SQL bit-field指定以下类型：bit【bit】、bit variation【varbit】。

/* SQL bit-field data types The following implements BIT() and BIT VARYING(). */
Bit:		BitWithLength { $$ = $1; }
			| BitWithoutLength { $$ = $1; }
BitWithLength:
			BIT opt_varying '(' expr_list ')'
				{
					char *typname = $2 ? "varbit" : "bit";
					$$ = SystemTypeName(typname); $$->typmods = $4; $$->location = @1;
				}
BitWithoutLength:
			BIT opt_varying
				{   /* bit defaults to bit(1), varbit to no limit */
					if ($2){
						$$ = SystemTypeName("varbit");
					}else{
						$$ = SystemTypeName("bit");
						$$->typmods = list_make1(makeIntConst(1, -1));
					}
					$$->location = @1;
				}

SQL character data types指定以下类型：character【varchar、bpchar】、char【varchar、bpchar】、varchar【varchar】、character variation

Character:  CharacterWithLength { $$ = $1; }
			| CharacterWithoutLength { $$ = $1; }
CharacterWithLength:  character '(' Iconst ')'
				{ $$ = SystemTypeName($1); $$->typmods = list_make1(makeIntConst($3, @3)); $$->location = @1; }
CharacterWithoutLength:	 character
				{ $$ = SystemTypeName($1);
					/* char defaults to char(1), varchar to no limit */
					if (strcmp($1, "bpchar") == 0)
						$$->typmods = list_make1(makeIntConst(1, -1));
					$$->location = @1;
				}	
character:	CHARACTER opt_varying { $$ = $2 ? "varchar": "bpchar"; }
			| CHAR_P opt_varying  { $$ = $2 ? "varchar": "bpchar"; }
			| VARCHAR             { $$ = "varchar"; }
			| NATIONAL CHARACTER opt_varying { $$ = $3 ? "varchar": "bpchar"; }
			| NATIONAL CHAR_P opt_varying    { $$ = $3 ? "varchar": "bpchar"; }
			| NCHAR opt_varying              { $$ = $2 ? "varchar": "bpchar"; }

SQL date/time types指定以下类型：time（有或没有时区）【timez、time】、timestamp（有或不带时区）【timestamptz、timestamp】

ConstDatetime:
			TIMESTAMP '(' Iconst ')' opt_timezone
				{
					if ($5) $$ = SystemTypeName("timestamptz");
					else $$ = SystemTypeName("timestamp");
					$$->typmods = list_make1(makeIntConst($3, @3));
					$$->location = @1;
				}
			| TIMESTAMP opt_timezone
				{
					if ($2) $$ = SystemTypeName("timestamptz");
					else $$ = SystemTypeName("timestamp");
					$$->location = @1;
				}
			| TIME '(' Iconst ')' opt_timezone
				{
					if ($5) $$ = SystemTypeName("timetz");
					else $$ = SystemTypeName("time");
					$$->typmods = list_make1(makeIntConst($3, @3));
					$$->location = @1;
				}
			| TIME opt_timezone
				{
					if ($2) $$ = SystemTypeName("timetz");
					else $$ = SystemTypeName("time");
					$$->location = @1;
				}

SQL Interval指定以下类型：interval

ConstInterval:
			INTERVAL
				{ $$ = SystemTypeName("interval"); $$->location = @1; }

SQL指定以下类型（或其拼写）：date、xml

GenericType

GenericType covers all type names that don’t have special syntax mandated by the standard, including qualified names. We also allow type modifiers. To avoid parsing conflicts against function invocations, the modifiers have to be shown as expr_list here, but parse analysis will only accept constants for them. GenericType涵盖了所有没有标准规定的特殊语法的类型名称，包括限定名称。我们还允许使用类型修饰符。为了避免解析与函数调用的冲突，这里的修饰符必须显示为expr_list，但解析分析只接受它们的常量。
SQL指定之外的通用数据类型为:bigserial,box,bytea,cidr,circle,inet,json,jsonb,line,lseg,macaddr,macaddr8,money,path,pg_lsn,pg_snapshot,point,polygon,smallserial,serial,text,tsquery,tsvector,txid_snapshot,uuid。

GenericType:
			type_function_name opt_type_modifiers
				{ $$ = makeTypeName($1); $$->typmods = $2; $$->location = @1; }
			| type_function_name attrs opt_type_modifiers
				{ $$ = makeTypeNameFromNameList(lcons(makeString($1), $2)); $$->typmods = $3; $$->location = @1; }
/* Type/function identifier --- names that can be type or function names. */
type_function_name:	IDENT							{ $$ = $1; }
			| unreserved_keyword					{ $$ = pstrdup($1); }
			| type_func_name_keyword				{ $$ = pstrdup($1); }

/*

  • Backwards compatibility for ancient random spellings of pg_type OID macros.
  • Don’t use these names in new code.
    */
    #define CASHOID MONEYOID
    #define LSNOID PG_LSNOID

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
#define INT2VECTOROID 22
#define INT4OID 23
#define REGPROCOID 24
#define TEXTOID 25
#define OIDOID 26
#define TIDOID 27
#define XIDOID 28
#define CIDOID 29
#define OIDVECTOROID 30
#define JSONOID 114
#define XMLOID 142
#define PG_NODE_TREEOID 194
#define PG_NDISTINCTOID 3361
#define PG_DEPENDENCIESOID 3402
#define PG_MCV_LISTOID 5017
#define PG_DDL_COMMANDOID 32
#define XID8OID 5069
#define POINTOID 600
#define LSEGOID 601
#define PATHOID 602
#define BOXOID 603
#define POLYGONOID 604
#define LINEOID 628
#define FLOAT4OID 700
#define FLOAT8OID 701
#define UNKNOWNOID 705
#define CIRCLEOID 718
#define MONEYOID 790
#define MACADDROID 829
#define INETOID 869
#define CIDROID 650
#define MACADDR8OID 774
#define ACLITEMOID 1033
#define BPCHAROID 1042
#define VARCHAROID 1043
#define DATEOID 1082
#define TIMEOID 1083
#define TIMESTAMPOID 1114
#define TIMESTAMPTZOID 1184
#define INTERVALOID 1186
#define TIMETZOID 1266
#define BITOID 1560
#define VARBITOID 1562
#define NUMERICOID 1700
#define REFCURSOROID 1790
#define REGPROCEDUREOID 2202
#define REGOPEROID 2203
#define REGOPERATOROID 2204
#define REGCLASSOID 2205
#define REGCOLLATIONOID 4191
#define REGTYPEOID 2206
#define REGROLEOID 4096
#define REGNAMESPACEOID 4089
#define UUIDOID 2950
#define PG_LSNOID 3220
#define TSVECTOROID 3614
#define GTSVECTOROID 3642
#define TSQUERYOID 3615
#define REGCONFIGOID 3734
#define REGDICTIONARYOID 3769
#define JSONBOID 3802
#define JSONPATHOID 4072
#define TXID_SNAPSHOTOID 2970
#define PG_SNAPSHOTOID 5038
#define INT4RANGEOID 3904
#define NUMRANGEOID 3906
#define TSRANGEOID 3908
#define TSTZRANGEOID 3910
#define DATERANGEOID 3912
#define INT8RANGEOID 3926
#define INT4MULTIRANGEOID 4451
#define NUMMULTIRANGEOID 4532
#define TSMULTIRANGEOID 4533
#define TSTZMULTIRANGEOID 4534
#define DATEMULTIRANGEOID 4535
#define INT8MULTIRANGEOID 4536
#define RECORDOID 2249
#define RECORDARRAYOID 2287
#define CSTRINGOID 2275
#define ANYOID 2276
#define ANYARRAYOID 2277
#define VOIDOID 2278
#define TRIGGEROID 2279
#define EVENT_TRIGGEROID 3838
#define LANGUAGE_HANDLEROID 2280
#define INTERNALOID 2281
#define ANYELEMENTOID 2283
#define ANYNONARRAYOID 2776
#define ANYENUMOID 3500
#define FDW_HANDLEROID 3115
#define INDEX_AM_HANDLEROID 325
#define TSM_HANDLEROID 3310
#define TABLE_AM_HANDLEROID 269
#define ANYRANGEOID 3831
#define ANYCOMPATIBLEOID 5077
#define ANYCOMPATIBLEARRAYOID 5078
#define ANYCOMPATIBLENONARRAYOID 5079
#define ANYCOMPATIBLERANGEOID 5080
#define ANYMULTIRANGEOID 4537
#define ANYCOMPATIBLEMULTIRANGEOID 4538
#define PG_BRIN_BLOOM_SUMMARYOID 4600
#define PG_BRIN_MINMAX_MULTI_SUMMARYOID 4601
#define BOOLARRAYOID 1000
#define BYTEAARRAYOID 1001
#define CHARARRAYOID 1002
#define NAMEARRAYOID 1003
#define INT8ARRAYOID 1016
#define INT2ARRAYOID 1005
#define INT2VECTORARRAYOID 1006
#define INT4ARRAYOID 1007
#define REGPROCARRAYOID 1008
#define TEXTARRAYOID 1009
#define OIDARRAYOID 1028
#define TIDARRAYOID 1010
#define XIDARRAYOID 1011
#define CIDARRAYOID 1012
#define OIDVECTORARRAYOID 1013
#define PG_TYPEARRAYOID 210
#define PG_ATTRIBUTEARRAYOID 270
#define PG_PROCARRAYOID 272
#define PG_CLASSARRAYOID 273
#define JSONARRAYOID 199
#define XMLARRAYOID 143
#define XID8ARRAYOID 271
#define POINTARRAYOID 1017
#define LSEGARRAYOID 1018
#define PATHARRAYOID 1019
#define BOXARRAYOID 1020
#define POLYGONARRAYOID 1027
#define LINEARRAYOID 629
#define FLOAT4ARRAYOID 1021
#define FLOAT8ARRAYOID 1022
#define CIRCLEARRAYOID 719
#define MONEYARRAYOID 791
#define MACADDRARRAYOID 1040
#define INETARRAYOID 1041
#define CIDRARRAYOID 651
#define MACADDR8ARRAYOID 775
#define ACLITEMARRAYOID 1034
#define BPCHARARRAYOID 1014
#define VARCHARARRAYOID 1015
#define DATEARRAYOID 1182
#define TIMEARRAYOID 1183
#define TIMESTAMPARRAYOID 1115
#define TIMESTAMPTZARRAYOID 1185
#define INTERVALARRAYOID 1187
#define TIMETZARRAYOID 1270
#define BITARRAYOID 1561
#define VARBITARRAYOID 1563
#define NUMERICARRAYOID 1231
#define REFCURSORARRAYOID 2201
#define REGPROCEDUREARRAYOID 2207
#define REGOPERARRAYOID 2208
#define REGOPERATORARRAYOID 2209
#define REGCLASSARRAYOID 2210
#define REGCOLLATIONARRAYOID 4192
#define REGTYPEARRAYOID 2211
#define REGROLEARRAYOID 4097
#define REGNAMESPACEARRAYOID 4090
#define UUIDARRAYOID 2951
#define PG_LSNARRAYOID 3221
#define TSVECTORARRAYOID 3643
#define GTSVECTORARRAYOID 3644
#define TSQUERYARRAYOID 3645
#define REGCONFIGARRAYOID 3735
#define REGDICTIONARYARRAYOID 3770
#define JSONBARRAYOID 3807
#define JSONPATHARRAYOID 4073
#define TXID_SNAPSHOTARRAYOID 2949
#define PG_SNAPSHOTARRAYOID 5039
#define INT4RANGEARRAYOID 3905
#define NUMRANGEARRAYOID 3907
#define TSRANGEARRAYOID 3909
#define TSTZRANGEARRAYOID 3911
#define DATERANGEARRAYOID 3913
#define INT8RANGEARRAYOID 3927
#define INT4MULTIRANGEARRAYOID 6150
#define NUMMULTIRANGEARRAYOID 6151
#define TSMULTIRANGEARRAYOID 6152
#define TSTZMULTIRANGEARRAYOID 6153
#define DATEMULTIRANGEARRAYOID 6155
#define INT8MULTIRANGEARRAYOID 6157
#define CSTRINGARRAYOID 1263

风语者！平时喜欢研究各种技术，目前在从事后端开发工作，热爱生活、热爱工作。

站长推荐