postgresql中的数据类型

发表于 | 分类于 |

类型分类

  1. 布尔类型 boolean
  2. 数值类型 smallint,int,bigint,numeric,real,double
  3. 字符类型 varchar,char,text postgresql中的varchar最大可以存储1G，text相当于mysql中的longtext
  4. 二进制类型 bytea
  5. 位串类型 bit,bit varying
  6. 日期类型 date,time,timestamp
  7. 枚举类型
  8. 几何类型 point，line,lseg,path,polygon,cycle
  9. 网络地址类型 cidr,inet,macaddr
  10. 数组类型
  11. 复合类型
  12. xml类型
  13. json类型
  14. range类型
  15. 对象标识符
  16. 伪类型
  17. 其他类型 如uuid，pg_lsn

类型转换

简单类型的可以直接输入：

1
2
3
4
5
6
select 1,1.142,'hello world';

 ?column? | ?column? |  ?column?   
----------+----------+-------------
        1 |    1.142 | hello world
(1 row)

对于其他复杂类型，可以使用类型名 加单引号的方式进行输入:

1
2
3
4
5
select bit '111000';
  bit   
--------
 111000
(1 row)

postgresql 支持使用标准sql转换 CAST进行类型转换：

1
2
3
4
5
select CAST('5' as int);
 int4 
------
    5
(1 row)

此外 postgresql还有另外一种简洁地类型转换方式即双冒号的方式进行转换:

1
2
3
4
5
select '5'::int;
 int4 
------
    5
(1 row)

布尔类型

布尔类型的状态要么是True要么是False，在postgresql中有多种表达方式，可以使用不带引号的TRUE或FALSE，也可以使用众多表示真假的字符表示，如’true’,’false’,’yes’,’no’等

1
2
3
4
5
6
7
8
insert into student (id,name,age,city) values (2,'李晨',12,'true');
INSERT 0 1
pgdemo=# select * from student;
 id | name | age | city 
----+------+-----+------
  1 | 张三 |   3 | 
  2 | 李四 |   5 | 
  2 | 李晨 |  12 | t

布尔类型的操作有AND、OR和NULL，判断可以使用IS，如：
expression IS TRUE

数值类型

整数类型

整数类型有三种：smallint,int和bigint。
int提供了范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的情况下使用smallint,在超过interger数值的时候才用bigint, 因为int要比bigint快得多。

smallint = int2
int = int4
bigint = int8

1
2
3
4
pgdemo=# insert into student (id,name,age,city,t_int2) values (3,'李晨',12,'true',32767);
INSERT 0 1
pgdemo=# insert into student (id,name,age,city,t_int2) values (3,'李晨',12,'true',32768);
ERROR:  smallint out of range

精确的小数

精确的小数类型可以使用numeric，numeric(m,n),numeric(m)表示。 numeric和decimal等效，都是SQL标准类型，可以存储最多1000位的小数，并且可以精确计算。适用于货币类型或者对精度要求高的场景，但运算速度低于整形或浮点型。

NUMERIC(precision,scale)

精度必须为正数，标度可以为零或正数。

在标准SQL和mysql中，语法DECIMAL等价于DECIMAL(M,0)，M在mysql中默认为10，但是postgresql觉得这样没有用，就改成了任意的精度和数值。如果你关心可移植性，最好总是声明精度和标度。

1
2
3
4
5
6
7
8
9
pgdemo=# create table t1 (id1 numeric(3),id2 numeric(3,0),id3 numeric(3,2),id4 numeric) ;
CREATE TABLE
pgdemo=# insert into t1 (id1,id2,id3,id4) values (3.1,3.5,3.123,3.123);
INSERT 0 1
pgdemo=# select * from t1;
 id1 | id2 | id3  |  id4  
-----+-----+------+-------
   3 |   4 | 3.12 | 3.123
(1 row)

浮点类型

数据类型real和double precision都是不精确、变精度的类型。

浮点数的使用需要注意：

  1. 要求精确计算，使用numeric类型
  2. 除了数值外，还有Infinity、-Infinity和NaN 分别代表，正无穷、负无穷和不是一个数字

序列类型

在序列类型中，serail和bigserial与mysql中的自增字段是一个意思。Postgresql实际是通过序列来实现的。Oracle与Postgresql都有序列，而myql没有。

1
2
3
4
5
6
create table t2 (
    id serial,
    name varchar
)

insert into t2 (name) values ('zhangsan');

货币类型

货币类型可以存储固定小数的货币数目，与浮点数不同，它是完全保留其精度的，其输出格式与lc_monetray设置有关。不同国家的输出格式不一样：

1
2
3
4
5
pgdemo=# select '123.12'::money;
  money  
---------
 $123.12
(1 row)

修改lc_monetary:

1
set lc_monetary='zh_CN.UFT-8'

没有语言包的时候需要安装。

字符串类型

Postgresql 中的字符串类型有varchar,char,text

  1. varchar: 变长，最大1G，与Mysql中最大64KB不同，Oracle中最多4000字节
  2. char: 定长，不足补空白，最大1G
  3. text: 变长，无长度限制。

二进制数据

二进制数据类型

PostgreSQL只有一种二进制类型：bytea。此数据类型允许存储二进制字符串，对
MySQL和Oracle中的blob类型。Oracle的raw类型也可以使用这个类型取代。

二进制字符串是一个字节序列。它和普通字符串的区别有两个：首先，二进制字符串完 全可以存储字节零值，以及其他“不可打印”的字节（定义在32到 126范围之外的字节)。普 通字符串不允许存储字节零值，并且也不允许存储那些不符合选定的字符集编码的非法字节 值或字节序列。第二，对二进制字符串的处理实际上就是处理字节，而对字符串的处理，则 取决于区域设置。简单地说，二进制字符串适用于存储那些程序员认为是“原始字节”的数 据，比如图片内容，而字符串则适合存储文本。

二进制数据的转义表示

由于二进制字符串中的部分字符为不可打印字符，那么如何在一个SQL语句的文本串里
面输入bytea数值呢？答案是使用转义。要转义一个字节值，通常需要把它的数值转换成对应的三位八进制数，并且加两个前导反斜杠。有些八进制数值可以加一个反斜杠直接转义，比如单引号和反斜杠本身：

十进制数值 描述 例子
39 单引号 osdba=# SELECT E’\‘’ ::bytea,E’\‘’::text;
92 反斜杠 SELECT E’\\\\‘ : :bytea
0 到 31及 127到 255 “不可打印”字节 SELECT E ‘\\001\\002’ ::bytea;

二进制数据类型的函数

二进制类型的函数见表

位串类型

位串类型定义

位串就是一串1和 0 的字符串。在 PostgreSQL中可以直观显式地操作二进制位。下面是两种SQL位类型：

  1. bit(n)
  2. bit varying(n)

其中的n是一个正整数。

bit(n)类型的数据必须准确匹配长度n，试图存储短些或者长一些的数据都是错误的。
bit varying(n)类型的数据是最长n 的变长类型，更长的串会被拒绝。
写一个没有长度的 bit等效于bit(1)，没有长度的bit varying表示没有长度限制。

如果明确地把一个位串值转换成bit(n)，那么它的右边将被截断，或者在右边补齐零到刚 好为n 位，而不会抛出任何错误。类似地，如果明确地把一个位串数值转换成bit varying(n), 若其超过了n位，那么它的右边也将被截断。

位串的使用

下面介绍位串类型的使用方法，见下例。

首先建一个测试表： 

1
CREATE TABLE test (a BIT(3), b BIT VARYING(5));

插入一条数据：

1
2
pgdemo=# insert into test values (B'101',B'00');
INSERT 0 1

对于bit(n)字段，如果插人的数据超过了匹配的长度n, 将报错，长了也会报错:

1
2
3
ERROR: bit string length 2 does not match type bit(3) 
osdba=# INSERT INTO test VALUES (B'11110、 B'1011); 
ERROR: bit string length 5 does not match type bit(3)

对于bitvarying(n)，如果插入的数据超过了匹配的长度n，同样会报错: 

1
2
osdba=# INSERT INTO test VALUES (B'110', B'1111011'); 
ERROR: bit string too long for type bit varying(5)

位串的操作符及函数

位串除了常用的比较操作符之外，主要支持一些位运算的操作符.

下面的SQL标准函数除了可以用于字符串之外，也可以用于位串:

1
2
3
4
5
6
length 
bit_length 
octet_length 
position 
substring 
overlay

下面的函数除支持二进制字符串，也可能用于位串:

1
2
get_bit
set_bit

当用于位串时，这些函数的位数将以串（最左边）的第一位作为〇位 。 另外，可以在整数和bit之间来回转换。示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
pgdemo=# select 66::bit(10)
;
    bit     
------------
 0001000010
(1 row)

pgdemo=# select 66::bit(3);
 bit 
-----
 010
(1 row)

pgdemo=# select 66::bit(5);
  bit  
-------
 00010
(1 row)

pgdemo=# select cast(-66 as bit(12));
     bit      
--------------
 111110111110
(1 row)

十进制、十六进制、二进制之间的转换示例。 十进制转二进制的示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
pgdemo=# select 88::bit(8);
   bit    
----------
 01011000
(1 row)

pgdemo=# select 'xff'::bit(8);
   bit    
----------
 11111111
(1 row)

pgdemo=# select 'xff'::bit(8)::int;
 int4 
------
  255
(1 row)

pgdemo=# select to_hex(255);
 to_hex 
--------
 ff
(1 row)

时间和日期类型

Postgresql中的时间和日期见下表：

名字 存储空间 描述 最低值 最高值 分辨率
timestamp [ (p) ] [ without time zone ] 8字节 时间和日期 4713BC 294276 AD 1微秒/14位
timestamp [ (p) ] [ with time zone ] 8字节 时间和日期，带时区 4713BC 294276 AD 1微秒/14位
interval [ (p)] 16字节 时间间隔 -178000000年 178000000年 1微秒/14位
date 4字节 只用于日期 4713BC 5874897AD 1天
time [ (p) ] [ without time zone] 8字节 只用于1日内的时间 0:00:00 24:00:00 1微秒/14位
time [ (p) ] [ with time zone] 12字节 只用于1日内的时间，带时区 0:00:00+1459 24:00:00-1459 1微秒/14位

需要注意的是，PostgreSQL的时间类型可以精确到秒以下，而MySQL的时间类型只能精确到秒。
time、timestamp、interval接受一个可选的精度值p以指明秒域中小数部分的位数。如果没有明确的默认精度，对于timestamp和interval类型，p 的范围是0〜6 。

8.4版本以前，timestamp数值是以双精度浮点数的方式存储的，timestamp值是以2000-01-01午夜之前 或之后的秒数存储的，可以想象，在 2000-01-01前后几年的日期中精度是可以达到微秒的， 而在更远一些的日子，精度可能达不到微秒，但达到毫秒是没有问题的。
9.0版本起，timestamp默认以八字节整数存储，那么微秒的精度就可以在数值的全 部范围内得到保证，不过这样之后八位整数的时间戳范围就缩小到了 4713 BC 到 294276 AD 之间。同时这个编译选项也决定了 tim e和 interval值是保存成浮点数还是八字节整数。同样， 在以浮点数存储的时候，随着时间间隔的增加，interval数值的精度也会降低。

8.4版本以后，internal由12字节加长为16字节。

POSTGRESQL修炼之道从小工到专家 这本书的蓝本应该是8.4以前的版本，请注意9和10、11以后的变动，且原书中的时间精度为毫秒，而官方文档的所有版本均为微秒，官方文档中文版本中也有毫秒的翻译，这应该是一个翻译上的错误，请以英文官方文档为准。

日期的输入

在 SQL中，任何日期或时间的文本输入需要由“日期/时间”类型加单引号包围的字符 串组成，语法如下：

type [ (p) ] 'value1'

日期和时间的输人几乎可以是任何合理的格式，包括ISO-8601格式、SQL-兼容格式、 传统POSTGRES格式，以及其他形式。对于某些格式，日期输入里的月和日可能会让人迷惑，因此系统支持自定义这些字段的顺序。如果DateStyle参数默认为”MDY”，则表示按 “月-日-年”解析；如果参数设置为DMY，则按照“日 -月 -年 ”解析；设置为YM D 表 示按照“年 -月 -日 ”解析。

示例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table t(co1 date);
insert into t values (date '12-13-2018');
select * from t;

co1     
------------
 2018-12-13
(1 row)

pgdemo=# show datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

# 设置格式为年月日
pgdemo=# set datestyle=YMD;
pgdemo=# insert into t values('2018-12-31');
INSERT 0 1

更多示例参见下表：
3

时间的输入

时间输入时，需要注意时区的输入。time被认为是time without time zone的类型，这样即 使字符串中有时区，也会被忽略，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
pgdemo=# select time '12:01:05';
   time   
----------
 12:01:05
(1 row)

pgdemo=# select time '12:01:05 PST';
   time   
----------
 12:01:05
(1 row)

pgdemo=# select time with time zone  '12:01:05 PST';
   timetz    
-------------
 12:01:05-08
(1 row)

时间字符串可以使用冒号作为分隔符，即输人格式为“hh:mm:ss”，如 “10:23:45”，也可 以不用分隔符，如 “102345”表示10点 23分 45秒。

注意，最好不要用时区缩写来表示时区，因为这样有可能给阅读者带来困扰，例如CST 时间有可能表示为:

  1. Central Standard Time (USA) UT-6:00，即美国标准时间。
  2. Central Standard Time (Australia) UT+9:30，即澳大利亚标准时间。
  3. China Standard Time UT+8:00，即中国标准时间。
  4. Cuba Standard Time UT-4:00，即古巴标准时间。

CST在 PostgreSQL中代表Central Standard Time(USA)UT-6:00， 缩写可以查询视图pg_timezone_abbrevs， 如下:

1
2
3
4
osdba=# select * from pg_timezone_abbrevs where abbrev='CST';
abbrev | utc offset | is dst 
---------------------------
CST | -06:00:00 | f (1 row)

在输入的时间后加“ ATTIME ZONE”可以转换时区或指定时区:

1
2
3
4
osdba=# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '+08:00'; 
timezone 
-----------
2001-02-16 17:38:40 (1 row)

时间转换

时间转换有三种格式：

  1. timestamp without time zone AT TIME ZONE zone:
    返回值：timestamp with time zone
  2. timestamp with time zone AT TIME ZONE zone:
    返回值：timestamp without time zone
  3. time with time zone AT TIME ZONE zone
    返回值：time with time zone

例如：

1
2
3
4
5
pgdemo=# select timestamp with time zone '2018-12-13 0:00:00 utc' at time zone 'prc';
      timezone       
---------------------
 2018-12-13 08:00:00
(1 row)

但是需要注意一点：

1
2
3
4
5
pgdemo=# select timestamp with time zone '2018-12-13 0:00:00 utc' at time zone '+08:00';
      timezone       
---------------------
 2018-12-12 16:00:00
(1 row)

+08:00 并不等于’prc’.

特殊值

为方便起见，PostgreSQL用一些特殊字符串输人值表示特别的意义：

4

另外 关于时间操作还有很多的函数和操作符，这里省略。

时间函数

PostgreSQL提供了许多返回当前日期和时间的函数。下面的函数都按照当前事务的开始时刻返回结果：

  1. CURRENT_DATE
  2. CURRENT_TIME
  3. CURRENT_TIMESTAMP
  4. CURRENT_TIME(precision)
  5. CURRENT_TIME(precision)
  6. LOCALTIME
  7. LOCALTIMESTAMP
  8. LOCALTIME(precision)
  9. LOCALTIMESTAMP(precision)
  10. now()
  11. transaction_timestamp()

其中，CURRENT_TIME和 CURRENT_TIMESTAMP返回带有时区的值; 和 LOCALTIMESTAMP返回不带时区的值

CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、 LOCALTIMESTAMP 可以 有选择地给予一个精度参数，该精度会导致结果的秒数域四舍五人到指定的小数位。如果没 有精度参数，将给予所能得到的全部精度。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
pgdemo=# begin;
BEGIN
pgdemo=# select localtimestamp;
         timestamp          
----------------------------
 2018-12-14 15:17:39.370972
(1 row)

pgdemo=# select current_time;
       timetz       
--------------------
 15:17:39.370972+08
(1 row)

pgdemo=# select current_date;
    date    
------------
 2018-12-14
(1 row)

pgdemo=# select localtime;
      time       
-----------------
 15:17:39.370972
(1 row)

因为这些函数全部都是按照当前事务的开始时刻返回结果的，所以它们的值在事务运行 的整个期间内都不会改变。PostgreSQL这样做的目的是为了允许一个事务在“当前时间”上 有连贯的概念，这样在同一个事务里的多个修改就可以保持同样的时间戳了。

PostgreSQL同样也提供了返回实时时间值的函数，它们的返回值会在事务中随时间的前 进而变化。这些函数如下：

  1. statement_timestamp
  2. clock—timestamp
  3. timeofday

now()函数、CURRENT_TIMESTAMP 函数和transaction_timestamp()函数都是等效的。 不过transaction_timestamp()的命名更准确地表明了其含义。statement_timestamp()返回当前 语句开始时刻的时间戳，它和transaction_timestamp()在事务的第一条命令里的返回值相同， 但是在随后的命令中却不一定相同。clock_timestamp()返回实时时钟的当前时间戳，因此它 的值甚至在同一条SQL命令中都会变化。timeofday()相当于clock_timestamp()，也返回实时 时钟的当前时间戳，但它返回的是一个text字符串，而不是timestamp with time zone值。

所有日期/时间类型还接受特殊的文本值now，用于声明当前的日期和时间（重申：乃当前事务的开始时刻）。因此，下面三个都返回相同的结果：

  1. select CURRENT_TIMESTAMP
  2. select now()
  3. select timestamp with time zone ‘now’;

示例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
pgdemo=# begin;
BEGIN
pgdemo=# select current_timestamp;
              now              
-------------------------------
 2018-12-14 15:31:10.428465+08
(1 row)

pgdemo=# select now();
              now              
-------------------------------
 2018-12-14 15:31:10.428465+08
(1 row)

pgdemo=# select timestamp with time zone 'now';
          timestamptz          
-------------------------------
 2018-12-14 15:31:10.428465+08
(1 row)

extract 和 date_part函数

extract函数格式如下： extract (field FROM source) extract函数从日期/时间数值里抽取子域，比如年、小时等，其返回类型为double precision 的数值。source必须是一个timestamp、time、interval类盤的值表达式。此外，类型为date的 表达式可自动转换为timestamp，因此source也可以用date类型。field是一个标识符或字符 串，它指定从源数据中抽取的域。

枚举类型

枚举类型是一个包含一系列有序的静态值集合的数据类型，等于某些编程语言中的 enum类型

枚举类型的使用

与MySQL中不同，在 PostgreSQL中要使用枚举类型需要先使用CREATE TYPE创建一 个枚举类型。见下面的例子。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
pgdemo=# create type week as enum ('Sun','Mon','Tues');
CREATE TYPE
pgdemo=# create table duty (person varchar,weekday week);
CREATE TABLE

pgdemo=# insert into duty values('张三','Mon');
INSERT 0 1
pgdemo=# insert into duty values('李四','Tues');
INSERT 0 1
pgdemo=# select * from duty;
 person | weekday
--------+---------
 张三   | Mon
 李四   | Tues
(2 rows)

pgdemo=# insert into duty values('李四','Sta');
ERROR:  invalid input value for enum week: "Sta"
LINE 1: insert into duty values('李四','Sta');

如果输入的字符串不在枚举类型之间，则会报错。

在psql中可以使用“\dT” 命令查看枚举类型的定义: 

1
2
3
4
5
6
7
8
pgdemo=# \dT+ week;
                                      List of data types
 Schema | Name | Internal name | Size | Elements |  Owner   | Access privileges | Description 
--------+------+---------------+------+----------+----------+-------------------+-------------
 public | week | week          | 4    | Sun     +| postgres |                   | 
        |      |               |      | Mon     +|          |                   | 
        |      |               |      | Tues     |          |                   | 
(1 row)

直接查询表pg_enum也可以看到枚举类型的定义:

1
2
3
4
5
6
7
pgdemo=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
    231613 |             1 | Sun
    231613 |             2 | Mon
    231613 |             3 | Tues
(3 rows)

枚举类型的说明

在枚举类型中，值的顺序是创建枚举类型时定义的顺序。所有比较标准的运算符及其相 关的聚集函数都可支持枚举类型。

1
2
3
4
5
pgdemo=# select min(weekday),max(weekday) from duty;
 min | max  
-----+------
 Mon | Tues
(1 row)

每个枚举类型都是独立的，不能与其他枚举类型混用。一个枚举值在磁盘上占4 字节。 一个枚举值的文本标签长度由NAMEDATALEN设置并 编译到PostgreSQL中，且是以标准编译方式进行的，也就意味着至少是63字节。 枚举类型的值是大小写敏感的，因 此 “Mon”不等于“mon”。标签中的空格也是一样，如 “Mon”（Mon后有一个空格）不等于“Mon”。

几何类型

PostgreSQL主要支持一些二维的几何数据类型。最基本的类型是“pomt”，它是其他类 型的基础。PostgreSQL支持的几何类型见下表：

网络地址类型

PostgreSQL提供了专门的数据类型存储IPv4、IPv6 和MAC地址。这比使用字符串更好 一些，因为这些类型可以做更好的检测。

inet与cidr类型

inet和cidr类型都可以用于存储一个IPv4或IPv6的地址:

1
2
3
4
5
6
7
8
9
10
11
postgres=# select '192.168.100.1'::inet;
     inet      
---------------
 192.168.100.1
(1 row)

postgres=# select '192.168.100.1'::cidr;
       cidr       
------------------
 192.168.100.1/32
(1 row)

这两种类型输入IPv4 地址的格式都为：
x.x.x.x/masklen
其掩码可以省略，比如：
x.x.x.x

注意掩码的长度都是用一个数字表示的，不能使用下面的格式：

1
osdba=# select '198.168.1 .100/255.255.255.0 '::cidr; ERROR: invalid input syntax for type cidr: "198.168.1.100/255.255.255.0" LINE 1: select ' 1 98.168.1.100/255.255.255.0cidr;

IPv6 地址的输人格式可以为：
ipv6_addr/masklen

inet与 cidr的区别

对于inet来说，如果子网掩码是32位并且地址是IPv4,那么它不表示任何子网，所表示 的只是一台主机的地址，如下：

1
2
3
4
5
postgres=# select '192.168.100.32'::inet;
      inet      
----------------
 192.168.100.32
(1 row)

同样，由于在IPv6 里地址长度是128位，因此在inet中 128位的掩码也表明是一个主机 地址，而不是一个子网地址:

1
2
3
4
5
postgres=# select '::10.2.3.4/128'::inet;
    inet    
------------
 ::10.2.3.4
(1 row)

而对于cidr来说，总是显示出掩码，如下:

1
2
3
4
5
6
7
8
9
10
11
postgres=# select '192.168.1.100/32'::cidr;
       cidr       
------------------
 192.168.1.100/32
(1 row)

postgres=# select '192.168.1.100'::cidr;
       cidr       
------------------
 192.168.1.100/32
(1 row)

且cidr总是对地址与掩码之间的关系进行检查，如果不正确会报错，如下:

1
2
3
4
5
6
7
8
9
10
postgres=# select '192.168.1.100/16'::cidr;
ERROR:  invalid cidr value: "192.168.1.100/16"
LINE 1: select '192.168.1.100/16'::cidr;
               ^
DETAIL:  Value has bits set to right of mask.
postgres=# select '192.168.0.0/16'::cidr;
      cidr      
----------------
 192.168.0.0/16
(1 row)

macaddr类型

macaddr类型用于存储以太网的M AC地址，可以接受多种自定义的格式，如下：

1
2
3
4
5
6
'00:e0:4c:75:7d:5a',
'00-e0-4c-75-7d-5a',
'00e04c-757d5a', 
'00e04c:757d5a',
'00e0.4c75.7d5a',
'00e04c757d5a'

复合类型

在 PostgreSQL中可以如C 语言中的结构体一样定义一个复合类型。

复合类型的定义

下面先给几个例子来看复合类型是如何定义的。
例子1: 定义一个复数类型。

1
2
3
4
postgres=# create type complex as (
postgres(# r double precision,
postgres(# i double precision
postgres(# );

例子2: 定义一个person

1
2
3
4
5
6
postgres=# create type Person as (
postgres(# name text,
postgres(# age integer,
postgres(# sex boolean
postgres(# );
CREATE TYPE

可以看到创建复合类型的语法类似于CREATE TABLE，但这里只能声明字段名字和类型，目前不能声明约束（比如NOTNULL)。请注意，AS关键字很重要，没有它，系统会认为这是另一种完全不同的CREATE TYPE命令，因此你会看到奇怪的语法错误。

访问复合类型

访问复合类型字段的一个域就如在c 语言中访问结构体中的一个成员一样，即写出一个 点和域的名字就可以了。这也非常像从一个表名字里选出一个字段。实际上，因为实在太像 了，所以经常需要用圆括号来避免SQL解析器的混淆。比如，可能需要从personjnfo字段中 选取一些子域，像下面这样：

1
osdba=# select person_info.name from author; ERROR: missing FROM-clause entry for table "person_info" LINE 1: select person_info.name from author;

但这样会报错，这时就需要在字段名称中加圆括号，如下:

1
2
osdba=# select (person_info).name from author; name
张三 (1 rows)

或者也可以加上表名，如下：

1
select (author.person_info).name from author;

类似的语法问题适用于在任何需要从一个复合类型值中査询一个域的地方。 比如，要从 一个返回复合类型值的函数中选取一个字段，则需要写像下面这样的代码：

1
SELECT (my_func (...)) .field FROM ...

如果没有额外的圆括号，就会产生语法错误

修改复合类型

先看插入或更新整个字段的例子:

1
2
3
insert into author values ( ('张三'，29, TRUE), '自传');
UPDATE author SET person_info = ROW ('李四' , 39, TRUE) WHERE id =1;
UPDATE author SET person_info = ('王二'， 49, TRUE) WHERE id =2;

也可以只更新一个复合字段的某个子域:

1
2
UPDATE author SET person_info.name ='王二二' WHERE id =2;
UPDATE author SET person_info.age = (person_info).age + 1 WHERE id =2;

需要注意的是，不能在SET后面出现的字段名周围放上圆括号，但若需要在等号右边的 表达式中引用同一个字段则需加上圆括号，否则会报错：

1
2
3
4
5
osdba=# UPDATE author SET (person_info).name ='王二二' WHERE id =2; 
ERROR: syntax error at or near "." LINE 1: UPDATE author SET (person_info).name = '王二二' WHERE id =2;
osdba=# UPDATE author SET person_info.age = person_info.age + 1 WHERE id =2; 
ERROR: missing FROM-clause entry for table "person_info" 
LINE 1: UPDATE author SET person_info.age = person_info.age + 1 WHER...

在 INSERT也可以指定复合字段的子域，示例如下:

1
INSERT INTO author (id, person_info.name, person_info.age) VALUES(10,'张三',29);

在上面的例子中，因子域没有为复合字段提供数值，故将用NULL填充.

XML类型

xml类型可以用于存储XML数据。使用字符串类型（如text)也可以存储XML数据，但 text类型不能保证其中存储的是合法XML数据，通常需要由应用程序来负责保证输人数据的 正确性，这将增加应用程序开发的难度。而使用xml类型就不存在此问题。数据库会对输入 的数据进行检查，让一些不符合XML标准的数据不能存放到数据库中，同时还提供了函数对 其类型进行安全性检查。

注意，要使用xml数据类型，在编译PostgreSQL源码时必须使用以下参数：
configure —— with-libxml.

JSON 类型

JSON数据类型可以用来存储JSON (JavaScript Object Notation) 数据，而JSON数据格式 是在RFC 4627中定义的。当然也可以使用text、varchar等类型存储JSON数据，但使用这些 通用的字符串格式将无法自动检测字符串是否为合法的JSON数据。而且，JSON数据类型还 可以使用丰富的函数。

JSON 类型

JSON数据类型是从PostgreSQL 9.3开始提供的一种类型，9.3版中只有一种类型：JSON。 在 PostgreSQL 9.4中又提供了一种更高效的类型JSONB这两种类型在使用上几乎完全一致， 主要的区别是，JSON类型是把输入的数据原封不动地存放到数据库中（当然在存放前会做 JSON的语法检查)，使用的时候需要重新解析数据，而 JSONB类型是在存放时就把JSON解 析成二进制格式了，使用的时候就不需要再次解析，所以JSONB在使用时性能会更高。另 外，JSONB支持在其上建索弓丨，而 JSON则不能，这是JSONB类型的很大一个优点。

因为JSON类型是把输人的整个字符串原封不改动地保存到数据库中的，因此JSON串 中key之间多余的空格也会保留。而且，如果JSON串中有重复的key，这些重复的key也 会保留（默认处理时以最后一个为准)，同时也会保留输人时JSON串中各个key的顺序。而 JSONB类型则恰恰相反，不会保留多余的空格，不会保留key的顺序，也不会保留重复的key。
在PostgreSQL中只允许每个数据库用一种服务器编码，如果数据库的编码不是UTF-8, PostgreSQL中的JSON类型是无法严格符合JSON规范中对字符集的要求的。 如果输人中包含 不能在服务器编码中表示的字符数据，将无法导人到数据库中。但是，能在服务器编码中表示的 非 UTF-8字符则是被允许的。可以使用\nXXXX形式的转义，从而忽视数据库的字符集编码。

当把一个JSON字符串转换成JSONB类型时，JSON字符串内的数据类型实际上被转 换成了 PostgreSQL数据库中的类型，两者的映射关系见表5-30。需要注意的是，如果是在 JSONB中，在 PostgreSQL里不能输入超出numeric数据类型范围的值。

你的支持我的动力