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

      数据类型	别名	说明
      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 数据

          此外，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