pgsql处理文档类型数据_PostgreSQL数据类型

http://blog.csdn.net/neo_liu0000/article/category/797059

第六章  数据类型

6.1概述

PostgreSQL 提供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型被分为四种，分别是基本数据类型、复合数据类型、域和伪类型。

基本数据类型是数据库内置的数据类型，包括integer、char、varchar等数据类型。表6-1列出了PostgreSQL提供的所有基本数据类型。复合数据类型是用户自己定义的，使用CREATE TYPE命令就能创建一个复合数据类型。域是一种特殊的基本数据类型，它由基本数据类型加上一个约束条件构成，使用CREATE DOMAIN命令就能创建一个域，关于域的详细信息，参考《SQL命令手册》对CREATE DOMAIN命令的解释。伪类型是具有特殊作用的数据类型，这些数据类型不能作为表的列的数据类型，只能作为函数的参数或返回值的数据类型。

下面的小节将会详细介绍基本数据类型、复合数据类型和伪类型。

表 6-1. 基本数据类型

名字

描述

bigint

有符号 8 字节整数

bigserial

自增八字节整数

bit [ (n) ]

定长位串

bit varying [ (n) ]

变长位串

boolean

逻辑布尔量 (真/假)

box

平面中的长方形

bytea

二进制数据("字节数组")

character varying [ (n) ]

变长字符串

character [ (n) ]

定长字符串

cidr

IPv4 或者 IPv6 网络地址

circle

平面中的圆

date

日历日期(年，月，日)

double precision

双精度浮点数字

inet

IPv4 或者 IPv6 网络地址

integer

四字节长有符号整数

interval [ (p) ]

时间间隔

line

平面中的无限长直线

lseg

平面中的线段

macaddr

MAC 地址

numeric [ (p, s) ]

可选精度的准确数字

path

平面中的几何路径

point

平面中的点

polygon

平面中的封闭几何路径

real

单精度浮点数

smallint

有符号两字节整数

serial

自增四字节整数

text

变长字符串

time [ (p) ] [ without time zone ]

一天里的时间

time [ (p) ] with time zone

一天里的时间，包括时区

timestamp [ (p) ] [ without time zone ]

日期和时间

timestamp [ (p) ] with time zone

日期和时间

tsquery

全文检索查询

tsvector

全文检索文档

txid_snapshot

用户级别事务ID快照

uuid

通用唯一标识符

xml

XML数据

兼容性:下列类型是在SQL标准中定义的： bit，bit varying，boolean， char，character，character varying，varchar，date， double precision，integer， interval，numeric，decimal， real，smallint，time (包括有时区和无时区的)， timestamp (包括有时区和无时区的)。

PostgreSQL的词法分析器在解析用户发出的SQL命令时，首先将其中的单词分成五类：整数、非整数数字、字符串、标识符和关键字。大部分的非数值常量首先被认为是字符串。

SQL语言提供了明确地指定字符串的类型的机制。例如：

SELECT 'Origin':: text AS "label", '(0,0)':: point AS "value";

label  | value

--------+-------

Origin | (0,0)

(1 row)

在上面的例子中，用户指定'Origin' 的类型是text，'(0,0)'的类型是 point。如果用户没有明确地指定和'Origin'和'(0,0)'的数据类型，系统先把它们的类型设为unknown，以后再确定它们的具体数据类型。

6.2 数值类型

数值类型包括2、4或8字节的整数，4或8字节的浮点数和可以定义精度的十进制数。 表6-2 列出了所有数值类型。

表6-2. 数值类型

名字

存储空间

描述

取值区间

smallint

2 字节

小整数

-32768 到 +32767

integer

4 字节

常用的整数

-2147483648 到 +2147483647

bigint

8 字节

大整数

-9223372036854775808 到 9223372036854775807

decimal

变长

用户定义精度，可以精确地表示小数

无限制

numeric

变长

用户定义精度，可以精确地表示小数

无限制

real

4 字节

精度可变，不能精确地表示小数

精度是6个十进制位

double precision

8 字节

精度可变，不能精确地表示小数

精度是15个十进制位

serial

4 字节

小范围的自增整数

大范围的自增整数

bigserial

8 字节

大范围的自增整数

1 到 9223372036854775807

数值类型常量的语法在第1.4.4节里描述。 数值类型有一套完整的数学运算符和函数。相关信息请参考第7章。下面将详细描述这些类型。

6.2.1 整数类型

类型 smallint、integer和 bigint 只能保存整数，也就是没有小数部分的数字。如果试图在一个整数类型中保存一个超过它能够表示的值范围的整数，数据库将会报错。

常用的类型是integer，因为它提供了在表示范围、存储空间和性能之间的最佳平衡。只有在磁盘空间紧张的情况下才使用 smallint。只有在 integer太小的时候才使用 bigint，因为在进行数学运算时，interger类型的数据bigint类型的数据要快。

SQL标准只定义了整数类型 integer(或int)、smallint和bigint。

6.2.2 任意精度数值

numeric类型最多能存储有1000个数字位的数字并且能进行准确的数值计算。它主要用于需要准确地表示数字的场合，如货币金额。不过，对numeric 类型进行算术运算比整数类型和浮点类型要慢很多。

numeric类型有两个术语，分别是标度和精度。numeric类型的标度(scale)是到小数点右边所有小数位的个数， numeric 的精度(precision)是所有数字位的个数，因例如， 23.5141 的精度是6而标度为4。可以认为整数的标度是零。

numeric 类型的最大精度和最大标度都是可以配置的。可以用下面的语法定义一个numeric类型：

(1)NUMERIC(precision, scale)

(2)NUMERIC(precision)

(3)NUMERIC

精度必须为正数，标度可以为零或者正数。在上面的第二种语法中没有指定标度，则系统会将标度设为0，所以NUMERIC(precision,0)和NUMERIC(precision)是等价的。第三种类型的语法没有指定精度和标度，则这种类型的列可以接受任意精度和标度的numeric数据(在系统能表示的最大精度范围内)，而不会对输入的数据进行精度或标度的变换。如果一个列被定义成numeric类型而且指定了标度，那么输入的数据将被强制转换成这个标度(如果它的标度比定义列的numeric的标度大)，进行标度转换时的规则是四舍五入。如果输入的数据进行标度转换后得到的数据在小数点左边的数据位的个数超过了列的类型的精度减去标度的差，系统将会报告类似下面的错误：

错误:  numeric类型数据溢出。

细节:  precision为3, scale为3的数必须被四舍五入成小于1的数。

下面是一个实例：

create table test ( col1 numeric(3,3));

insert into test values(0.5678);

insert into test values(0.5671);

insert into test values ( 1.4);

错误:  numeric类型数据溢出。

细节:  precision为3, scale为3的数必须被四舍五入成小于1的数。

=>select * from test;

col1

-------

0.568

0.567

(2 rows)

numeric 类型接受一个特殊的值 “NaN”，它的意思是“不是一个数字"。任何在 NaN 上面的操作都生成另外一个 NaN。 如果在 SQL 命令里把这些值当作一个常量写，必须把它用单引号引起来，比如 UPDATE table SET x = 'NaN'。在输入时，”NaN”的大小写无关紧要。

注意：在其它的数据库中，NaN和任何的数值数据都不相等，两个NaN也是不相等的，在postgresSQL中，为了索引实现的方便，NaN被看成大于或等于所有非NaN的数值。

类型 decimal和numeric是等价的，两种类型都是SQL标准定义的，SQL标准要求numeric的默认精度应该是0，PostgreSQL没有执行这个规则，为了增强程序的移植性，最好同时指定numeric的精度和标度。

6.2.3 浮点类型

数据类型 real 和 double precision 表示不准确的变精度的数字。这些类型实现了IEEE 标准754二进制浮点数算术(分别对应单精度和双精度)。

不准确的意思是一些数值不能准确地用real和double precision表示，存储在数据库里的只是它们的近似值。如果要求准确地保存某些数值(比如计算货币金额)，应使用 numeric 类型。另外，比较两个浮点数是否相等时，可能会得到意想不到的结果。

通常，real 类型的表示的数值范围是至少-1E+37到+1E+37，精度至少是6位小数。double precision 类型表示的范围通常是-1E+308到+1E+308 ，精度是至少15位小数。太大或者太小的数值都会导致错误。如果输入数据的精度太高，会被约成可以被接受的精度。太接近零的数字，如果和0的内部表示形式一样，会产生下溢(underflow)的错误。

浮点类型还有几个特殊值：

Infinity

-Infinity

NaN

这些值分别表示 IEEE 754标准中的特殊值"正无穷大"，"负无穷大"， 以及"不是一个数字"。如果在 SQL 命令里把这些数值当作常量写，必须在它们用单引号引起来，例如UPDATE table SET x = 'Infinity'。 输入时，这些值的大小写无关紧要。

注意：IEEE 754标准要求NaN和任何的数值数据都不相等，两个NaN也是不相等的，在postgresSQL中，为了索引实现的方便，NaN被看成大于或等于所有非NaN的数值。

PostgreSQL 还支持 SQL 标准中定义的类型float和float(p)。p 定义以二进制位表示的最低可以接受的精度，p的取值在1到53之间。实际上，如果p的取值在1到24之间，float(p)被看成是real类型，如果p的取值在25到53之间，float(p)被看成是double precision类型。不带精度的float被看成是double precision类型。

6.2.4 序列号类型(Serial)

serial 和 bigserial 并不是真正的数据类型，只是为了可以给表中的数据行设置一个唯一的标识。它类似其它一些数据库中的 AUTO_INCREMENT 属性。使用它们的方法如下：

CREATE TABLE tablename (

colname SERIAL

);

上面的命令实际上上等价于下面的两条命令：

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename(

colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL

);

上面的命令在表中创建了一个类型为无符号整数的列，该列与一个序列对象相关联，这个序列对象的初始值是1， 表中每插入一条新的记录，该序列的值会自动加一，在向表中插入数据时，INSERT命令不要为该列指定数据，或者指定它的值为DEFAULT。

下面是一个实例：

create table test3 ( product_id serial, name char(5));

insert into test3(name)  values('pen');

insert into test3(name)  values('car');

insert into test3  values(DEFAULT, 'bike');

=>select * from  test3;

product_id | name

------------+-------

1 | pen

2 | car

3 | bike

(3 rows)

注意：insert命令中一定不要为serial或bigserial类型的列指定一个不是DEFAULT的值，因为对于这样的命令系统是不会报错的，会导致serial或bigserial类型的列上的数值出现混乱。

6.3 字符类型

表 6-3. 字符类型

名字

描述

character varying(n), varchar(n)

变长，最大长度有限制

character(n), char(n)

定长, 不足补空白

text

变长，最大长度没有限制

表6-3列出了PostgresSQL中可以使用的字符类型。

SQL标准定义了两种基本的字符类型：character varying(n) 和 character(n)，这里的n是一个正整数。两种类型最多可以存储n个字符。试图存储更长的字串到这些类型的列里，系统会报错，除非所有超出长度n的字符都是空格(这种情况下该字符串将被截断成长度为n的字符串)。如果要存储的字符串的长度比n小，类型为 character 的列将自动用空格填充该字符串，使它的长度达到n，而类型为 character varying 的列直接保存该字符串，不会对它进行任何处理。

如果明确将把一个数值转换成character(n)或者 character varying(n)类型，如果转换以后的字符串的长度超过n，那么它将被自动截断成长度为n的字符串，系统不会报错(这也是SQL标准要求的)。

char(n) 和 varchar(n) 分别是 character(n)和character varying(n)的别名。没有定义长度的character 等同于 character(1)。没有定义长度的character varying类型接受任意长度的字符串，这是PostgreSQL的扩展特性。

另外，PostgreSQL 提供了text类型，它可以存储任意长度的字符串，而且长度没有最大限制。尽管SQL标准中没有定义text类型，但许多其它 SQL 数据库系统中有这个类型。

character(n)类型的数据在存储时长度不足n的字符串会用空格填充，在显示数据时也会把填充的空格显示出来，但是在比较两个character类型的值的时候，字符串的所有结尾空格符号将自动被忽略，在转换成其它字符串类型的时候，character类型的值里面结尾的空格字符都会被删除。请注意，对于 character varying 和 text类型的值，结尾的空格在处理时是不会被忽略的。

对于character(n) 和 character varying(n)类型，允许存储的最长字符串所占的存储空间大概1GB。如果想存储长度没有上限的长字串，那么使用 text类型或者没有指定长度的character varying。

提示:这三种数据类型之间没有性能差别，不过character(n)比character varying(n)类型多使用了物理存储空间。 虽然在某些其它的数据库系统里，character(n) 比character varying(n)快一些， 但在 PostgreSQL 里没有这种情况。在大多数情况下，应该使用text或者character varying。

请参考第1.4.1节和1.4.2节得到字符串常量的的语法信息，参考第7.4节得到处理字符串的运算符和函数的信息。数据库的字符集决定用于存储文本值的字符集，有关字符集的详细信息，请参考《数据库管理员指南》第5章。

下面是一个使用字符串的实例：

CREATE TABLE test1 (a character(4));

INSERT INTO test1 VALUES ('ok');

INSERT INTO test1 VALUES ('ok ');  --ok后面跟了一个空格

SELECT a, char_length(a) FROM test1; --函数char_length在第7.4节中有详细介绍.

a   | char_length

------+-------------

ok   |           2

ok   |           2

(2 rows)

CREATE TABLE test2 (b varchar(5));

INSERT INTO test2 VALUES ('ok');

INSERT INTO test2 VALUES ('good      ');

INSERT INTO test2 VALUES ('too long');

错误:输入的字符串对于类型character varying(5)来说过长。

INSERT INTO test2 VALUES ('too long'::varchar(5)); -- 截断字符串

SELECT b, char_length(b) FROM test2;

b   | char_length

-------+-------------

ok    |           2

good  |           5

too l |           5

在 PostgreSQL 还有另外两种定长字符串类型，在表6-4 里显示。这两种类型是供系统内部使用的，应用程序不应该使用这两种类型。name类型长度当前定为 64 字节(63 可用字符加上结束符)。类型"char"(注意引号)和char(1)是不一样的，它只占一个字节的存储空