Postgresql数据类型-数组类型

PostgreSQL支持一维数组和多维数组，常用的数组类型为数字类型数组和字符型数组，也支持枚举类型、复合类型数组。

数组类型定义

先来看看数组类型的定义，创建表时在字段数据类型后面加方括号“[]”即可定义数组数据类型，如下所示：

        CREATE TABLE test_array1 (
            id          integer,
            array_i     integer[],
            array_t     text[]
        );

以上integer[]表示integer类型一维数组，text[]表示text类型一维数组。

数组类型值输入

数组类型的插入有两种方式，第一种方式使用花括号方式，如下所示：

        '{ val1 delim val2 delim ... }'

将数组元素值用花括号“{}”包围并用delim分隔符分开，数组元素值可以用双引号引用，delim分隔符通常为逗号，如下所示：

        mydb=> SELECT '{1,2,3}';
            ?column?
        ----------
            {1,2,3}
        (1 row)
        往表test_array1中插入一条记录的代码如下所示：
        mydb=> INSERT INTO test_array1(id, array_i, array_t)
        VALUES (1, '{1,2,3}', '{"a", "b", "c"}');
        INSERT 0 1

数组类型插入的第二种方式为使用ARRAY关键字，例如：

        mydb=> SELECT array[1,2,3];
                array
        ---------
            {1,2,3}
        (1 row)

往test_array2表中插入另一条记录，代码如下所示：

        mydb=> INSERT INTO test_array1(id, array_i, array_t)
            VALUES (2, array[4,5,6], array['d', 'e', 'f']);
        INSERT 0 1

表test_array2的数据如下所示：

        mydb=> SELECT ＊ FROM test_array1;
            id | array_i | array_t
        -------+---------+---------
              1 | {1,2,3} | {a, b, c}
              2 | {4,5,6} | {d, e, f}
        (2 rows)

查询数组元素

如果想查询数组所有元素值，只需查询数组字段名称即可，如下所示：

        mydb=> SELECT array_i FROM test_array1 WHERE id=1;
            array_i
        ---------
            {1,2,3}
        (1 row)

数组元素的引用通过方括号“[]”方式，数据下标写在方括号内，编号范围为1到n, n为数组长度，如下所示：

        mydb=> SELECT array_i[1], array_t[3] FROM test_array1 WHERE id=1;
            array_i | array_t
        ------------+---------
                  1 | c
        (1 row)

数组元素的追加、删除、更新

PostgreSQL数组类型支持数组元素的追加、删除与更新操作，数组元素的追加使用array_append函数，用法如下所示：

        array_append(anyarray, anyelement)

array_append函数向数组末端追加一个元素，如下所示：

        mydb=> SELECT array_append(array[1,2,3],4);
            array_append
        --------------
            {1,2,3,4}
        (1 row)

数据元素追加到数组也可以使用操作符||，如下所示：

        mydb=> SELECT array[1,2,3] || 4;
            ?column?
        -----------
            {1,2,3,4}
        (1 row)

数组元素的删除使用array_remove函数，array_remove函数用法如下所示：

        array_remove(anyarray, anyelement)

array_remove函数将移除数组中值等于给定值的所有数组元素，如下所示：

        mydb=> SELECT array[1,2,2,3], array_remove(array[1,2,2,3],2);
            array   | array_remove
        ------------+--------------
          {1,2,2,3} | {1,3}
        (1 row)

数组元素的修改代码如下所示：

        mydb=> UPDATE test_array1 SET array_i[3]=4 WHERE id=1 ;
        UPDATE 1

整个数组也能被更新，如下所示：

        mydb=> UPDATE test_array1 SET array_i=array[7,8,9] WHERE id=1;
        UPDATE 1

数组操作符

PostgreSQL数组元素支持丰富操作符，如表所示。

PostgreSQL支持丰富的数组函数，给数组添加元素或删除元素，如下所示：

        mydb=> SELECT array_append(array[1,2],3), array_remove(array[1,2],2);
            array_append | array_remove
        -----------------+--------------
            {1,2,3}      | {1}
        (1 row)

获取数组维度，如下所示：

        mydb=> SELECT array_ndims(array[1,2]);
            array_ndims
        -------------
                1
        (1 row)

获取数组长度，如下所示：

        mydb=> SELECT array_length(array[1,2],1);
            array_length
        --------------
                2
        (1 row)

返回数组中某个数组元素第一次出现的位置，如下所示：

        mydb=> SELECT array_position(array['a', 'b', 'c', 'd'], 'd');
            array_position
        ----------------
                4
        (1 row)

数组元素替换可使用函数array_replace，语法如下：

        array_replace(anyarray, anyelement, anyelement)

函数返回值类型为anyarray，使用第二个anyelement替换数组中的相同数组元素，如下所示：

        mydb=> SELECT array_replace(array[1,2,5,4],5,10);
            array_replace
        ---------------
            {1,2,10,4}
        (1 row)

将数组元素输出到字符串，可以使用array_to_string函数，语法如下：

        atray_to_string(anyarray, text [, text])

函数返回值类型为text，第一个text参数指分隔符，第二个text表示将值为NULL的元素使用这个字符串替换，示例如下：

        mydb=> SELECT array_to_string(array[1,2, null,3], ', ', '10');
            array_to_string
        -----------------
            1,2,10,3
        (1 row)