PostgreSQL类型系统——Data Types

news/2024/7/9 22:29:55 标签: postgresql, 数据库

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


http://www.niftyadmin.cn/n/299831.html

相关文章

《2023全球汽车行业网络安全报告》解读

《2023全球汽车行业网络安全报告》解读

1.前言 Upstream的研究人员分析了关键的汽车风险和漏洞&#xff0c;并发布了一份安全报告《2023全球汽车行业网络安全报告》 Upstream’s 2023 Global Automotive Cybersecurity Report 阿里卡梅伦是一名内容营销人员&#xff0c;专注于网络安全和B2B SaaS领域。除了为Tripwir…
阅读更多...
Java 面试必问的线程池原理总结

Java 面试必问的线程池原理总结

本文首发自「慕课网」&#xff0c;想了解更多IT干货内容&#xff0c;程序员圈内热闻&#xff0c;欢迎关注"慕课网"&#xff01; 作者&#xff1a;大能老师 | 慕课网讲师 Java 线程池原理总结 &#xff08;一&#xff09;什么是线程池 线程池做的工作主要是控制运行…
阅读更多...
FM33A048B LPUART

FM33A048B LPUART

概述 LPUART 是一个低功耗UART 接口&#xff0c;其工作仅需32768Hz 时钟&#xff0c;可以支持到最高9600 波特率的数据接收。LPUART 功耗极低&#xff0c;可以在Sleep/DeepSleep 模式下工作。 特点&#xff1a; ⚫ 异步数据收发 ⚫ 标准UART帧格式 ◼ 1bit起始位 ◼ 7或8bit数据…
阅读更多...
Java学习之Swing图形界面

Java学习之Swing图形界面

Java提供的Swing组件众多&#xff0c;下面列举其中的几种&#xff0c;本章主要讲解顶层容器&#xff0c;其余容器在下面几章会做讲解。 1、顶层容器 1&#xff09;顶层容器就是不包含在其他容器中的容器&#xff0c;Swing中常见的顶层容器有JFrame&#xff0c;JFrame被称为窗口…
阅读更多...
Java序列化引发的血案

Java序列化引发的血案

1、引言 阿里巴巴Java开发手册在第一章节&#xff0c;编程规约中OOP规约的第15条提到&#xff1a; **【强制】**序列化类新增属性时&#xff0c;请不要修改serialVersionUID字段&#xff0c;避免反序列失败&#xff1b;如果完全不兼容升级&#xff0c;避免反序列化混乱&#x…
阅读更多...
ASN.1入门介绍

ASN.1入门介绍

ASN.1简介 ASN.1(Abstract Syntax Notation dotone),抽象语法标记1。是定义抽象数据类型形式的标准&#xff0c;描绘了与任何表示数据的编码技术无关的通用数据结构。抽象语法使得人们能够定义数据类型&#xff0c;并指明这些类型的值。抽象语法只描述数据的结构形式&#xff…
阅读更多...
Python 中的字典顺序

Python 中的字典顺序

文章目录 Python 中的字典顺序在 Python 中将数字列表按词典顺序排序 我们将介绍 Python 中的字典顺序。 我们还将通过示例讨论实现词典顺序的不同方法。 Python 中的字典顺序 在数学中&#xff0c;词典顺序或词典顺序是对按字母顺序排列的元素列表或元素数组进行排序的过程。…
阅读更多...
国产安全芯片，请关注他们！

国产安全芯片，请关注他们！

一、华大电子 http://www.hed.com.cn/ 1.1 公司简介 北京中电华大电子设计有限责任公司 &#xff08;简称“华大电子”&#xff09;成立于2002年6月&#xff0c;是国家认定的高新技术企业&#xff0c;是国内最早的集成电路设计企业之一&#xff0c;是中国安全芯片产业的核心…
阅读更多...
最新文章

Copyright @ 2022~2023