PostgreSQL（五）常用数据类型

目录

• • 1.字符串类型：char(n)，varchar(n)，text
  • 2.数值类型：smallint，int，bigint，decimal，numeric，real，double precision，smallserial，serial，bigserial
  • 3.时间和日期类型：date，time，timestamp，interval
  • 4.布尔类型：boolean
  • 5.枚举类型：enum
  • 6.复合类型：composite
  • 7.数组类型：array
  • • 声明数组：
    • 插入值：
    • 访问数组：
    • 修改数组
    • 数组中检索
  • 8.网络地址类型：cidr，inet，macaddr，macaddr8
  • 9.几何类型：point，line，lseg，box，path，polygon，circle
  • 10.JSON类型：json，jsonb
  • 11.其他类型

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

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

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

4.布尔类型：boolean

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_idinteger, pricenumeric)

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

CREATE TABLE on_hand (iteminventory_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);-- 使用复合类型 complexCREATE 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 {nametext,pay_by_quarterinteger[],scheduletext[][]};

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

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

CREATE TABLE sal_emp (nametext,pay_by_quarterinteger ARRAY[4],scheduletext[][]);

插入值：

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

INSERT INTO sal_empVALUES ('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 ORpay_by_quarter[2] = 10000 ORpay_by_quarter[3] = 10000 ORpay_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 sFROM sal_emp) AS foo WHERE pay_by_quarter[s] = 10000;

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

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

10.JSON类型：json，jsonb

11.其他类型

此外，PostgreSQL还支持自定义数据类型和扩展数据类型。自定义数据类型允许用户创建自己的数据类型，而扩展数据类型则是一种插件机制，允许第三方开发人员扩展 PostgreSQL 的数据类型。

参考地址：

1.postgresql数据类型，https://blog.csdn.net/eryou_979/article/details/118927281

2.PostgreSQL 数据类型，https://www.runoob.com/postgresql/postgresql-data-type.html

3.PostgreSQL数据类型，一篇就够了，https://zhuanlan.zhihu.com/p/105097036?utm_id=0