开发手册 欢迎您!
软件开发者资料库

PostgreSQL - 数据类型

PostgreSQL数据类型 - 从简单和简单的步骤学习PostgreSQL从基本到高级概念,包括数据库编程,概述,环境设置,语法,数据类型,创建,选择,删除数据库,删除,创建表,架构,插入,选择,更新,删除查询,运算符,表达式,位置,类似,限制,排序依据,分组依据,使用,具有子句,AND和OR运算符,不同的关键字,约束,联接,联合子句,NULL值,别名语法,触发器,索引,更改表命令,截断表命令,视图,事务,锁,子查询,自动增量,权限,日期/时间函数和操作符,函数,有用函数,C / C ++,JAVA,PHP,Perl,Python接口。

在本章中,我们将讨论PostgreSQL中使用的数据类型.创建表时,对于每一列,您指定一种数据类型,即您希望在表字段中存储哪种数据.

这可以带来几个好处 :

  • 一致性 : 针对相同数据类型的列的操作提供一致的结果,通常是最快的.

  • 验证 : 正确使用数据类型意味着数据格式验证和数据类型范围之外的数据拒绝.

  • 紧凑性 : 由于列可以存储单一类型的值,因此它以紧凑的方式存储.

  • 性能 : 正确使用数据类型可以最有效地存储数据.存储的值可以快速处理,从而提高性能.

PostgreSQL支持多种数据类型.此外,用户可以使用 CREATE TYPE SQL命令创建自己的自定义数据类型. PostgreSQL中有不同类别的数据类型.它们将在下面讨论.

数值类型

数字类型由两个字节,四个字节和八个字节的整数组成,四个字节和8字节浮点数,以及可选精度小数.下表列出了可用的类型.

名称存储大小描述范围
smallint2个字节小范围整数-  32768到+32767
integer4个字节整数的典型选择-2147483648至+2147483647
bigint8个字节大范围整数-  9223372036854775808至9223372036854775807
decimalvariable用户指定的精度,精确小数点前最多131072位;小数点后最多16383位数
numeric变量用户指定的精度,完全小数点前最多131072位;小数点后最多16383位数
real4个字节变量精度,不精确6位小数精度
double precision8 bytes变精度,不精确15位小数精度
smallserial2个字节小自动递增整数1到32767
serial4 bytes自动递增整数1到2147483647
bigserial8个字节大型自动递增整数1到9223372036854775807

货币类型

money 类型存储货币金额固定分数精度. numeric,int和bigint 数据类型的值可以转换为 money .由于可能出现舍入错误,建议不要使用浮点数来处理货币.

名称存储大小描述范围
money8字节货币金额-92233720368547758.08至+92233720368547758.07

字符类型

下表给出了PostgreSQL中可用的通用字符类型.

S.不.姓名&描述
1

字符变化(n),varchar(n)

变量长度有限制

2

字符(n),字符(n)

固定长度,空白填充

3

text

变量无限长度

二进制数据类型

bytea 数据类型允许存储二进制字符串,如下表所示.

名称存储大小描述
bytea1或4个字节加上实际的二进制字符串可变长度的二进制字符串

日期/时间类型

PostgreSQL支持一整套SQL日期和时间类型,如下表所示.日期根据公历计算.此处,所有类型的分辨率 1微秒/14位日期类型,其分辨率为.

名称存储大小描述低值高值
timestamp [(p)] [without time zone ]8个字节日期和时间(无时区)4713 BC294276 AD
timestamp [(p) ] with time zone8 bytes日期和时间,时区4713 BC294276 AD
date4个字节日期(没有时间)4713 BC5874897 AD
time [ (p)] [ without time zone ]8个字节时间(无日期)00:00:0024:00:00
time [ (p)] with time zone12个字节仅限一天的时间,带时区00:00:00 + 145924:00:00-1459
interval [fields] [(p)]12个字节时间interval-  178000000年178000000年

布尔类型

PostgreSQL提供标准的SQL类型布尔值.布尔数据类型可以具有状态 true false ,以及第三个状态 unknown ,它由SQL null值表示.

名称存储大小描述
boolean1 byte状态为true或false

枚举类型

枚举(枚举)类型是包含静态有序值集的数据类型.它们等同于许多编程语言支持的枚举类型.

与其他类型不同,需要使用CREATE TYPE命令创建枚举类型.此类型用于存储静态的有序值集.例如指南针方向,即NORTH,SOUTH,EAST和WEST或星期几,如下所示 :

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

枚举一旦创建,就可以像任何其他类型一样使用.

几何类型

几何数据类型表示二维空间对象.最基本的类型,即点,形成所有其他类型的基础.

名称存储大小表示描述
point16字节飞机上的点(x,y)
line32个字节无限行(未完全实现)((x1,y1),(x2,y2))
lseg32字节有限线段((x1,y1),(x2,y2))
box32 bytes矩形框((x1,y1),(x2,y2))
path16 + 16n bytes封闭路径(类似于多边形)((x1,y1),...)
path16 + 16n bytes打开路径[(x1,y1),...]
polygon40 + 16n多边形(类似于封闭路径)((x1,y1),...)
circle24字节Circle<(x,y),r> (中心点和半径)

网络地址类型

PostgreSQL提供数据用于存储IPv4,IPv6和MAC地址的类型.最好使用这些类型而不是纯文本类型来存储网络地址,因为这些类型提供输入错误检查和专门的操作符和函数.

名称存储大小描述
cidr7或19字节IPv4和IPv6网络
inet7或19字节IPv4和IPv6主机和网络
macaddr6个字节MAC地址

位字符串类型

位使用字符串类型存储位掩码.它们是0或1.有两种SQL位类型:位(n)位变化(n),其中n是正整数.

文本搜索类型

此类型支持全文搜索,这是搜索自然语言文档集合以查找与查询最匹配的文本的活动.这个有两种数据类型;

S.不.姓名&描述
1

tsvector

这是已对其进行规范化以合并同一单词的不同变体的不同单词的排序列表,称为"lexemes".

2

tsquery

这会存储要搜索的词位,并将它们组合在一起以表达布尔值运营商& (AND),| (或),和! (不).括号可用于强制对运算符进行分组.

UUID类型

UUID(通用唯一标识符)被写为一系列小写十六进制数字,由连字符分隔的多个组,特别是一组八位数字,后跟三组四位数字,后跟一组12位数,总共32位,代表128位.

UUID的一个例子是 :  550e8400-e29b-41d4-a716-446655440000

XML类型

XML数据类型可用于存储XML数据.对于存储XML数据,首先必须使用函数xmlparse创建XML值,如下所示 :

XMLPARSE (DOCUMENT 'PostgreSQL Tutorial    ...')XMLPARSE (CONTENT 'xyzbarfoo')

JSON类型

json 数据类型可用于存储JSON(JavaScript Object Notation)数据.此类数据也可以存储为 text ,但 json 数据类型的优点是检查每个存储的值是否为有效的JSON值.还有相关的支持函数,可以直接用于处理JSON数据类型,如下所示.

示例示例结果
array_to_json('{{1,5},{99,100}}':: int [])[[1,5],[99,100]]
row_to_json(row(1,' foo')){"f1":1,"f2":"foo"}

数组类型

PostgreSQL提供了将表的列定义为可变长度多维数组的机会.可以创建任何内置或用户定义的基类型,枚举类型或复合类型的数组.

数组声明

数组类型可以声明为

CREATE TABLE monthly_savings (   name text,   saving_per_quarter integer[],   scheme text[][]);

或使用关键字"ARRAY"作为

CREATE TABLE monthly_savings (   name text,   saving_per_quarter integer ARRAY[4],   scheme text[][]);

插入值

可以将数组值插入为文字常量,将元素值括在花括号内并分离他们用逗号.示例如下所示 :

INSERT INTO monthly_savings VALUES (‘Manisha’, ‘{20000, 14600, 23500, 13250}’, ‘{{"FD", "MF"}, {"FD", "Property"}}’);

访问数组

访问数组的示例如下所示.下面给出的命令将选择第二季度比第四季度节省更多的人.

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

修改数组

修改数组的示例如下所示.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'WHERE name = 'Manisha';

或使用ARRAY表达式语法 :

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]WHERE name = 'Manisha';

搜索数组

搜索数组的示例如下所示.

  SELECT * FROM monthly_savings WHERE saving_per_quarter [1] = 10000 OR  saving_per_quarter [2] = 10000 OR  saving_per_quarter [3] = 10000 OR  saving_per_quarter [4] = 10000;

如果已知数组的大小,则可以使用上面给出的搜索方法.另外,以下示例显示了如何搜索大小未知的情况.

  SELECT * FROM monthly_savings WHERE 10000 = ANY(saving_per_quarter);

复合类型

此类型表示字段名称及其数据类型的列表,即行的结构或者记录表.

复合类型声明

以下示例说明如何声明复合类型

  CREATE TYPE inventory_item AS( name text, supplier_id integer, price numeric );

此数据类型可在创建表中使用,如下所示;

  CREATE TABLE on_hand( item inventory_item, count integer );

复合值输入

复合值可以作为文字常量插入,将字段值括在括号内并分隔他们用逗号.示例如下所示 :

  INSERT INTO on_hand VALUES(ROW('fuzzy dice',42,1.99),1000);

这适用于上面定义的 inventory_item .只要表达式中有多个字段,ROW关键字实际上是可选的.

访问复合类型

访问字段复合列,使用点后跟字段名称,就像从表名中选择一个字段一样.例如,要从on_hand示例表中选择一些子字段,查询将如下所示 :

  SELECT(item).name FROM on_hand WHERE(item).price> 9.99;

您甚至可以使用表名(例如在多表查询中),例如这个 :

  SELECT(on_hand.item).name FROM on_hand WHERE(on_hand.item).price> 9.99;

范围类型

范围类型表示使用一系列数据的数据类型.范围类型可以是离散范围(例如,所有整数值1到10)或连续范围(例如,上午10:00到11:00之间的任何时间点).

可用范围类型包括以下范围 :

  • int4range : 整数范围

  • int8range :  bigint范围

  • numrange : 数字范围

  • tsrange : 没有时区的时间戳范围

  • tstzrange : 带时区的时间戳范围

  • daterange : 日期范围

可以创建自定义范围类型以使新类型的范围可用,例如使用inet类型的IP地址范围使用float数据类型作为基数的base或float范围.

范围类型分别使用[]和()字符支持包含和排除范围边界.例如,'[4,9)'表示从4开始包括但不包括9的所有整数.

对象标识符类型

PostgreSQL在内部使用对象标识符(OID)作为各种系统表的主键.如果指定了 WITH OIDS 或启用了 default_with_oids 配置变量,则只有这样,在这种情况下才会将OID添加到用户创建的表中.下表列出了几种别名类型.除了专门的输入和输出例程之外,OID别名类型没有自己的操作.

名称参考文献描述值示例
oidany数字对象标识符564182
regprocpg_proc函数名称sum
regprocedurepg_proc参数类型sum(int4)
regoperpg_operator运营商名称+
regoperatorpg_operator运算符,参数类型*(整数,整数)或 - ( NONE,整数)
regclasspg_class关系名称pg_type
regtypepg_type数据类型名称整数
regconfigpg_ts_config文本搜索配置英语
regdictionarypg_ts_dict文本搜索词典简单

伪类型

PostgreSQL类型系统包含许多专用条目统称为伪类型.伪类型不能用作列数据类型,但它可用于声明函数的参数或结果类型.

下面给出的表列出了现有的伪类型./p>

S.不.姓名&描述
1

any

表示函数接受任何输入数据类型.

2

anyelement

表示函数接受任何数据类型.

3

anyarray

表示函数接受任何数组数据类型.

4

anynonarray

表示函数接受任何非数组数据类型.

5

anyenum

表示函数接受任何枚举数据类型.

6

anyrange

表示函数接受任何范围数据类型.

7

cstring

表示函数接受或返回以null结尾的C string.

8

internal

表示函数接受或返回服务器内部数据类型.

9

language_handler

声明一个过程语言调用处理程序返回language_handler.

10

fdw_handler

声明外部数据包装器处理程序返回fdw_handler.

11

record

标识返回未指定行类型的函数.

12

trigger

声明触发器函数返回触发器.

13

void

表示函数返回no价值.