数值类型

货币类型

money            8字节              

money 类型存储带有固定小数精度的货币金额,可以与numeric、int、bigint进行转化。不建议使用浮点数处理货币类型,因为可能存在精度问题。

字符类型

char(n) ,character(n)定长,不足补空白

varchar(n) ,character varying(n)变长,有长度限制

text 变长,无长度限制

日期 / 时间

布尔类型

boolean   一字节     true / false / null

枚举类型

创建枚举   

create type 枚举名 as enum('值1', '值2', ..., '值n');

例如:create type mood enum as ('aaa','bbb');

使用枚举    

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

几何类型

网络地址类型

PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。

用这些数据类型存储网络地址比用纯文本类型好, 因为这些类型提供输入错误检查和特殊的操作和功能。

位串类型

位串就是一串 1 和 0 的字符串。它们可以用于存储和直观化位掩码。 我们有两种 SQL 位类型:bit(n) 和bit varying(n), 这里的n是一个正整数。

bit 类型的数据必须准确匹配长度 n, 试图存储短些或者长一些的数据都是错误的。bit varying 类型数据是最长 n 的变长类型;更长的串会被拒绝。 写一个没有长度的bit 等效于 bit(1), 没有长度的 bit varying 意思是没有长度限制。

json类型

JSON 代表 JavaScript Object Notation。JSON是开放的标准格式,由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同,JSON是人类可读的文本格式。

从PostgreSQL 9.3开始,json就成了postgres里的一种数据类型,也就是和varchar、int一样,我们表里的一个字段的类型可以为json了。

与此同时,postgres还提供了jsonb格式,jsonb格式是json的二进制形式,二者的区别在于json写入快,读取慢,jsonb写入慢,读取快,但在操作上,二者是没有区别的。

注意:键值对的键必须使用双引号

json、jsonb通用操作符

jsonb额外操作符

创建表

假设我们要存储的json数据是这样的:

{
"id": ID
"name":"名字",
"age":年龄
}

建表语句如下:

create table if not exists user (

info jsonb

)

好了,这样就创建了一张表,里面只有一个 info 字段,下面开始进行CRUD操作。

插入数据

插入数据可以直接以json格式插入:

insert into user values('{"id":1,"name":"小明", "age":18}')

在json里插入新的key值gender(info中的id为1时),如下:

select info || '{"gender":2}'::jsonb from user where (info->>'id')::int4 = 1;

注:|| 为json连接符

查询数据

Postgres里的查询需要用到查询符。比如说,我们要查询id为1的数据,语句如下:

select info from user where info @> '{"id":1}'::jsonb

用到了 @> 这个查询符,表明info当前这条记录里的顶层json中有没有id为1的key-value对;有的话则满足条件。

再来一个复杂一点的查询的,查询 age>16 的记录,并且只显示 name ,语句如下:

select info->'name' from user where (info->>'age')::int4 > 16

修改数据

下面,将info中id为1的age改为22 ,SQL语句:

select info || '{"age":22}'::jsonb from user where (info->>'id')::int4 = 1;

上述用法仅适用于9.5以上,9.5以下需要整个记录更新,不可以单独修改某个值。

删除数据

删除所以info的age这个key,SQL如下:

select info-'age' from user

直接用操作符 - 即可。

数组类型

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"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

访问数组

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

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

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';

数组中检索

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

比如:

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);

复合类型

1、创建符合类型

create type 类型名 as (

        属性1    属性1类型

        属性2    属性2类型

        ...

        属性n   属性n类型

);

例如:

create type complex as(

id int,

name text

);

2、创建表使用复合类型

create table user (

        complex_value complex,

        age int

);

3、向表中插入数据 - 

insert into user(complex_value, age) values((1,"张三"), 20);

4、访问复合类型   --    (复合类型名).复合类型属性,加括号防止与 表名.表属性 混合

select (complex).name from user where (complex).id = 1;

若需要添加表名也可这样写:

select (user.complex).name from user where (user.complex).id = 1;

范围类型

范围数据类型代表着某一元素类型在一定范围内的值。

例如,timestamp 范围可能被用于代表一间会议室被预定的时间范围。

PostgreSQL 内置的范围类型有:

  • int4range — integer的范围

  • int8range —bigint的范围

  • numrange —numeric的范围

  • tsrange —timestamp without time zone的范围

  • tstzrange —timestamp with time zone的范围

  • daterange —date的范围

此外,你可以定义你自己的范围类型。

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
SELECT isempty(numrange(1, 5));

范围值的输入必须遵循下面的格式:

(下边界,上边界)
(下边界,上边界]
[下边界,上边界)
[下边界,上边界]
空

圆括号或者方括号显示下边界和上边界是不包含的还是包含的。注意最后的格式是 空,代表着一个空的范围(一个不含有值的范围)。

-- 包括3,不包括7,并且包括二者之间的所有点
SELECT '[3,7)'::int4range;

-- 不包括3和7,但是包括二者之间所有点
SELECT '(3,7)'::int4range;

-- 只包括单一值4
SELECT '[4,4]'::int4range;

-- 不包括点(被标准化为‘空’)
SELECT '[4,4)'::int4range;

 

 

 

 

Logo

华为开发者空间,是为全球开发者打造的专属开发空间,汇聚了华为优质开发资源及工具,致力于让每一位开发者拥有一台云主机,基于华为根生态开发、创新。

更多推荐