上一节:
下一节:

  PostgreSQL - 数据类型

  • 简述

    在本章中，我们将讨论 PostgreSQL 中使用的数据类型。在创建表格时，为每一列指定一个数据类型，即，您希望在表格字段中存储什么样的数据。
    这带来了几个好处 -
    • Consistency− 针对相同数据类型的列的操作给出一致的结果并且通常是最快的。
    • Validation− 正确使用数据类型意味着对数据进行格式验证并拒绝超出数据类型范围的数据。
    • Compactness− 由于列可以存储单一类型的值，因此以紧凑的方式存储。
    • Performance− 正确使用数据类型可以最有效地存储数据。可以快速处理存储的值，从而提高性能。
    PostgreSQL 支持多种数据类型。此外，用户可以使用CREATE TYPE SQL 命令创建自己的自定义数据类型。PostgreSQL 中有不同类别的数据类型。它们将在下面讨论。

  • 数值类型

    数值类型由 2 字节、4 字节和 8 字节整数、4 字节和 8 字节浮点数以及可选择精度的小数组成。下表列出了可用的类型。
    名称 存储大小 描述 范围
    smallint 2 个字节 小范围整数 -32768 至 +32767
    integer 4字节 整数的典型选择 -2147483648 至 +2147483647
    bigint 8 个字节 大范围整数 -9223372036854775808 至 9223372036854775807
    decimal 多变的 用户指定精度，精确 小数点前最多 131072 位；小数点后最多 16383 位
    numeric 多变的 用户指定精度，精确 小数点前最多 131072 位；小数点后最多 16383 位
    real 4字节 可变精度，不精确 6 位小数精度
    precision 8 个字节 可变精度，不精确 15 位小数精度
    smallserial 2 个字节 小自增整数 1 至 32767
    serial 4字节 自增整数 1 至 2147483647
    bigserial 8 个字节 大自增整数 1至9223372036854775807

  • 货币类型

    货币类型存储具有固定小数精度的货币金额。numeric、int 和 bigint数据类型的值可以转换为money。由于可能出现舍入错误，不建议使用浮点数来处理货币。
    名称 存储大小 描述 范围
    money 8 个字节 货币金额 -92233720368547758.08 至 +92233720368547758.07

  • 字符类型

    下表列出了 PostgreSQL 中可用的通用字符类型。
    S. 没有。 名称和描述
    1
    character varying(n), varchar(n)
    有限制的可变长度
    2
    character(n), char(n)
    固定长度，空白填充
    3
    text
    可变无限长度

  • 二进制数据类型

    bytea数据类型允许存储二进制字符串，如下表所示。
    名称 存储大小 描述
    bytea 1 或 4 个字节加上实际的二进制字符串 可变长度二进制字符串

  • 日期/时间类型

    PostgreSQL 支持全套 SQL 日期和时间类型，如下表所示。日期根据公历计算。在这里，所有类型的分辨率为1 microsecond / 14 digits除了date类型，其分辨率为day.
    名称 存储大小 描述 低价值 高价值
    timestamp [(p)] [without time zone ] 8 个字节 日期和时间（无时区） 公元前4713年 294276 公元
    TIMESTAMPTZ 8 个字节 日期和时间，带时区 公元前4713年 294276 公元
    date 4字节 日期（没有时间） 公元前4713年 5874897 公元
    time [ (p)] [ without time zone ] 8 个字节 一天中的时间（无日期） 00:00:00 24:00:00
    time [ (p)] with time zone 12 字节 仅一天中的时间，带时区 00:00:00+1459 24:00:00-1459
    interval [fields ] [(p) ] 12 字节 时间间隔 -17.8亿年 17.8亿年

  • 布尔类型

    PostgreSQL 提供标准的 SQL 类型 Boolean。Boolean 数据类型可以具有状态true、false和第三个状态unknown，它由 SQL 空值表示。
    名称 存储大小 描述
    boolean 1 个字节 true 或者 false

  • 枚举类型

    枚举（enum）类型是包含一组静态、有序值的数据类型。它们等效于许多编程语言中支持的枚举类型。
    与其他类型不同，枚举类型需要使用 CREATE TYPE 命令创建。此类型用于存储静态、有序的值集。例如指南针方向，即北、南、东和西或一周中的几天，如下所示 - 
    
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
    枚举，一旦创建，就可以像任何其他类型一样使用。

  • 几何类型

    几何数据类型表示二维空间对象。最基本的类型，点，构成了所有其他类型的基础。
    名称 存储大小 表示 描述
    point 16 字节 平面上的点 (x,y)
    line 32 字节 无限线（未完全实现） ((x1,y1),(x2,y2))
    lsg 32 字节 有限线段 ((x1,y1),(x2,y2))
    box 32 字节 长方形盒子 ((x1,y1),(x2,y2))
    path 16+16n 字节 闭合路径（类似于多边形） ((x1,y1),...)
    path 16+16n 字节 打开路径 [(x1,y1),...]
    polygon 40+16n 多边形（类似于封闭路径） ((x1,y1),...)
    circle 24 字节 圆圈 <(x,y),r>（中心点和半径）

  • 网络地址类型

    PostgreSQL 提供数据类型来存储 IPv4、IPv6 和 MAC 地址。最好使用这些类型而不是纯文本类型来存储网络地址，因为这些类型提供输入错误检查和专门的运算符和功能。
    名称 存储大小 描述
    cidr 7 或 19 个字节 IPv4 和 IPv6 网络
    inet 7 或 19 个字节 IPv4 和 IPv6 主机和网络
    macaddr 6字节 MAC 地址

  • 位串类型

    位串类型用于存储位掩码。它们是 0 或 1。有两种 SQL 位类型：bit(n)和bit varying(n), 其中 n 是一个正整数。

  • 文本搜索类型

    此类型支持全文搜索，即在自然语言文档集合中进行搜索以找到与查询最匹配的文档的活动。这有两种数据类型 -
    S. 没有。 名称和描述
    1
    tsvector
    这是一个不同单词的排序列表，这些单词已被规范化以合并同一单词的不同变体，称为“词素”。
    2
    tsquery
    这存储要搜索的词位，并将它们结合起来，以符合布尔运算符 & (AND)、| （或），和！（不是）。括号可用于强制对运算符进行分组。

  • UUID 类型

    UUID（Universally Unique Identifiers）被写成一系列小写十六进制数字，分几组用连字符隔开，特别是一组八位数字，后跟三组四位数字，然后是一组 12 位数字，用于共有 32 位数字代表 128 位。
    UUID 的一个例子是 - 550e8400-e29b-41d4-a716-446655440000

  • XML 类型

    XML 数据类型可用于存储 XML 数据。为了存储 XML 数据，首先您必须使用 xmlparse 函数创建 XML 值，如下所示 - 
    
XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')
XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

  • 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（行（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 地址范围，或使用浮点数据类型作为基础的浮点范围。
    范围类型分别使用 [ ] 和 ( ) 字符支持包含和排除范围边界。例如 '[4,9)' 表示从 4 到 9 到但不包括 9 的所有整数。

  • 对象标识符类型

    PostgreSQL 在内部使用对象标识符 (OID) 作为各种系统表的主键。如果指定了WITH OIDS或启用了default_with_oids配置变量，则只有在这种情况下，才会将 OID 添加到用户创建的表中。下表列出了几种别名类型。除了专门的输入和输出例程外，OID 别名类型没有自己的操作。
    名称 参考 描述 值示例
    oid any 数字对象标识符 564182
    regproc pg_proc 函数名 sum
    regprocedure pg_proc 具有参数类型的函数 sum(int4)
    regoper pg_operator 运营商名称 +
    regoperator pg_operator 具有参数类型的运算符 *(integer,integer) 或 -(NONE,integer)
    regclass pg_class 关系名称 pg_type
    regtype pg_type 数据类型名称 整数
    regconfig pg_ts_config 文本搜索配置 英语
    regdictionary pg_ts_dict 文本搜索词典 简单的

  • 伪类型

    PostgreSQL 类型系统包含许多特殊用途的条目，统称为伪类型。伪类型不能用作列数据类型，但可以用于声明函数的参数或结果类型。
    下表列出了现有的伪类型。
    S. 没有。 名称和描述
    1
    any
    指示函数接受任何输入数据类型。
    2
    anyelement
    表示函数接受任何数据类型。
    3
    anyarray
    指示函数接受任何数组数据类型。
    4
    anynonarray
    指示函数接受任何非数组数据类型。
    5
    anyenum
    指示函数接受任何枚举数据类型。
    6
    anyrange
    指示函数接受任何范围数据类型。
    7
    cstring
    指示函数接受或返回一个以 null 结尾的 C 字符串。
    8
    internal
    指示函数接受或返回服务器内部数据类型。
    9
    language_handler
    声明过程语言调用处理程序以返回 language_handler。
    10
    fdw_handler
    外部数据包装处理程序被声明为返回 fdw_handler。
    11
    record
    标识返回未指定行类型的函数。
    12
    trigger
    声明一个触发器函数以返回触发器。
    13
    void
    表示函数不返回值。
上一节:
下一节: