PostgreSQL（五）常用数据类型-Toy模板网

这篇具有很好参考价值的文章主要介绍了PostgreSQL（五）常用数据类型。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1.字符串类型：char(n)，varchar(n)，text

数据类型	别名	说明
`character(n)`	`char(n)`	定长字符串，不足补空格
`character varying(n)`	`varchar(n)`	变长字符串
`text`		变长字符串

2.数值类型：smallint，int，bigint，decimal，numeric，real，double precision，smallserial，serial，bigserial

数据类型	别名	说明	范围
`smallint`	`int2`	有符号2字节整数	:-32768 到 +32767
`integer`	`int`、`int4`	有符号4字节整数	:-2147483648 到 +2147483647
`bigint`	`int8`	有符号8字节整数	:-9223372036854775808 到 +9223372036854775807
`decimal(p,s)`		可选精度的精确数字	小数点前 131072 位；小数点后 16383 位
`numeric(p,s)`		可选精度的精确数字	小数点前 131072 位；小数点后 16383 位
`real`	`float4`	单精度浮点数（4字节）	6 位十进制数字精度
`double precision`	`float8`	双精度浮点数（8字节）	15 位十进制数字精度
`smallserial`	`serial2`	自增2字节整数	1 到 32767
`serial`	serial4	自增4字节整数	1 到 2147483647
`bigserial`	`serial8`	自增8字节整数	1 到 9223372036854775807

3.时间和日期类型：date，time，timestamp，interval

数据类型	别名	说明
`date`		日历日期（年月日）
`time(n) without time zone`	`time(n)`	时间（无时区）
`time(n) with time zone`	`timetz`	时间，包括时区
`timestamp(n) without time zone`	`timestamp(n)`	日期和时间（无时区）
`timestamp(n) with time zone`	`timestamptz`	日期和时间，包括时区
`interval fields(n)`	`interval(n)`	时间跨度

4.布尔类型：boolean

数据类型	别名	说明
`boolean`	`bool`	逻辑布尔值（真/假）

5.枚举类型：enum

枚举类型 是一个包含静态和值的有序集合的数据类型。

PostgreSQL 中的枚举类型类似于 C 语言中的 enum类型。
与其他类型不同的是，枚举类型需要使用 CREATE TYPE 命令创建的。
像其他类型一样，一旦创建，枚举类型可以用于表和函数定义。

创建枚举类型：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

使用枚举类型：

CREATE TABLE person (
	name text,
    current_mood mood
);

插入数据：

INSERT INTO person VALUES ('Moe', 'happy');

查询数据：

SELECT * FROM person WHERE current_mood = 'happy';

查询结果：

 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

6.复合类型：composite

复合类型，也叫组合类型，将简单类型组合在一起，形成自定义类型。

数据表的列可以定义为组合类型。

创建复合类型，声明类型包含的字段和字段类型：

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

将表字段声明为组合类型：

CREATE TABLE on_hand (
    item    inventory_item,
    count   integer
);

插入数据：

（ROW 语句用于构建组合类型的值。）

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

查询数据：

SELECT * FROM on_hand;

查询结果：

 item          | count
 ------------------------+-------
 ("fuzzy dice",42,1.99) |  1000

补充：创建数据表的同时，会创建同名的 TYPE，如下所示：

-- 建表相当于同时执行了 CREATE TYPE complex AS (r double precision, i double precision);
CREATE TABLE complex (r double precision, i double precision);


-- 使用复合类型 complex
CREATE TABLE my_complex (name text, value complex);

-- 插入数据
INSERT INTO my_complex (name, value) VALUES ('one', ROW(1.0, 1.0));

-- 查询数据
SELECT * FROM my_complex;

-- 查询结果
name | value
------+-------
one  | (1,1)

查询复合类型，用()包围复合类型的列名，用.指向复合类型的 “域”：

SELECT (value).r FROM my_complext WHERE (value).i = 1;

查询结果：

r
---
1

7.数组类型：array

PostgreSQL 允许将字段定义为变长的多维数组。

数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

声明数组：

创建表的时候，我们可以声明数组，方式如下：

CREATE TABLE sal_emp {
	name            text,
	pay_by_quarter  integer[],
	schedule        text[][]
};

pay_by_quarter 为一维整型数组，schedule 为二维文本类型数组。

我们也可以使用 “ARRAY” 关键字，如下所示：

CREATE TABLE sal_emp (
	name            text,
    pay_by_quarter  integer ARRAY[4],
    schedule        text[][]
);

插入值：

插入值使用花括号，元素在 {} 使用逗号隔开：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

访问数组：

现在我们可以在这个表上运行一些查询。

首先，我们演示如何访问数组的一个元素。这个查询检索在第二季度薪水变化的雇员名：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

查询结果：

 name
-------
 Carol
(1 row)

数组的下标数字是写在方括弧内的。

修改数组

我们可以对数组的值进行修改：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

或者使用 ARRAY 构造器语法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

数组中检索

要搜索一个数组中的数值，你必须检查该数组的每一个值。

比如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

或者，可以使用 generate_subscripts 函数。例如：

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

8.网络地址类型：cidr，inet，macaddr，macaddr8

数据类型	别名	说明
`cidr`		IPv4 或 IPv6 网络地址
`inet`		IPv4 或 IPv6 主机地址
`macaddr`		MAC（媒体访问控制）地址
`macaddr8`		MAC（媒体访问控制）地址（EUI-64格式）

9.几何类型：point，line，lseg，box，path，polygon，circle

数据类型	别名	说明
`point`		平面上的几何点
`line`		平面上的无限线
`lseg`		平面上的线段
`box`		平面上的长方形盒子
`path`		平面上的几何路径
`polygon`		平面上的闭合几何路径
`circle`		在平面上画圈

10.JSON类型：json，jsonb

数据类型	别名	说明
`json`		文本 JSON 数据
`jsonb`		二进制 JSON 数据，分解

11.其他类型

数据类型	别名	说明
`bit(n)`		定长位串
`bit varying(n)`	`varbit(n)`	变长位串
`bytea`		二进制数据（“字节数组”）
`money`		货币金额
`pg_lsn`		PostgreSQL日志序列号
`pg_snapshot`		用户级事务 ID 快照
`tsquery`		文本搜索查询
`tsvector`		文本搜索文档
`txid_snapshot`		用户级事务 ID 快照（已弃用；请参阅pg_snapshot）
`uuid`		通用唯一标识符
`xml`		XML 数据