DataType
| 数据类型 | 表示 |
|---|---|
| 布尔类型 | boolean |
| 数值类型 | smallint(2),integer(4),bigint(8) |
| 浮点型 | decimal,numeric,real(float),double precision(double),money |
| 字符串 | varchar(n),char(n),text |
| 时间 | date(年月时),time(时分秒),timestamp(年月时时分秒) |
| 二进制类型 | bytea |
| 位图 | bit(n)(定长位图),bit-varying(n)(变成位图) |
| 枚举类型 | enum |
| 几何类型 | 点,直线,圆... |
| 数组类型 | 在类型后添加[] |
| JSON类型 | json(存储json文本)，jsonb(二进制json-可加索引) |
| IP类型 | cidr(存储ip地址) |
注意
pgsql中， 单引号标注具体的数值，比如'北京',双引号类似mysql的``标识一个关键字。
select 1.414,'北京',"墨西哥";-- 墨西哥这里就会报错
类型转换
select 类型 值 ;
select bit '010101';
select 值::类型;
select '010101'::bit(6);
select '2011-10-11'::date;
select cast(类型 值 类型);
select cast(text '010101' as bit(6));
布尔类型
可以存储三个值，true,false,NULL.
但是pgsql的boolean并不严格匹配，允许我们做一些‘出格’的匹配。
比如
-- OK
select true,false;
那如果是True,False,甚至y,n,1,0等都能转换成true,false.
select true,True,TRUE,'y'::boolean,'yes'::boolean,'yeal'::boolean,'YES'::boolean,'1'::boolean,NULL::boolean;
| 字段A | 字段B | and | or |
|---|---|---|---|
| true | true | true | false |
| true | false | false | true |
| true | NULL | NULL | true |
| false | false | false | false |
| false | NULL | false | NULL |
| NULL | NULL | NULL | NULL |
数值类型
整形
smallint,int2,integer,int4,bigint,int8
| 操作符 | 描述 | 实例 | 结果 |
|---|---|---|---|
| ^ | 幂 | 2 ^ 3 | 8 |
| |/ | 平方根 | |/36 | 6 |
| @ | 绝对值 | @ -24 | 24 |
| & | 与 | 31 & 16 | 16 |
| | | 或 | 31 | 32 | 63 |
| >> | 右移 | 2 >> 1 | 0 |
| << | 左移 | 2 << 2 | 8 |
函数
-- 3.1415....
pi()
-- 小数点位数
round(n,m)
-- 向上取整
ceil()
-- 向下取整
floor()
浮点类型
decimal(n,m),numeric(n,m)
序列
序列是什么？
简单理解：
- 就像一个数字生成器，每次调用返回下一个数字
- 保证数字唯一且递增
- 独立于表存在，可以被多个表共用
创建序列
create sequence public.table_id_seq;-- 查询和更新
select currval('public.table_id_seq');
select nextval('public.table_id_seq');
使用序列作为表的主键
-- 比较麻烦，每个表创建一个序列
create table public.xxx(id int8 default nextval('public.table_id_seq'));-- 直接使用序列类型,有smallserial,serial,bigserial
create table public.xxx(id serial);
字符串
- char ，定长字符串，最大1个G
- varchar(n),最大1个G
- text,很长的字符串.
常用函数
||
concat
char_length
octet_length
position
substring
trim
upper
lower
日期类型
select '2022-11-20 15:22:57'::timestamp;select time '15:22:57';select date '2045-11-20';select now()::timestamp;select date '2011-10-10' + 10; # 默认单位加天select date '2011-10-10' + time '10:11:22';select timestamp '2011-10-10 10:10:10' + interval '1month';
枚举类型
-- 声明一个星期的枚举
create type week as enum ('Mon','Tus','Wed','Thu','Fri','Sat','Sun');-- 使用枚举
create table public.test(id bigserial,weekday week
);-- 使用week
insert into public.test (weekday) values('Sun');
IP 类型
select '127.0.0.1'::cidr;-- 报错，可以检验
select '192.168.2.256'::cidr;-- 范围查询
select ip from table between '192.168.1.1' and '192.168.1.10';
JSON & JSONB类型
# json和jsonb的使用几乎没区别，
# 存储上，jsonb是二进制的json类型，但是支持索引，自动去除多余索引，不保证原有顺序
# json可以存储重复的key,但是以最后一个为准，但是jsonb不会保存多余的重复keyselect '9'::json,'null'::json,'"dagouxiong"'::json,'true'::json;select '9'::jsonb,'null'::jsonb,'"dagouxiong"'::jsonb,'true'::jsonb; select '[9,true,null,"asdas"]'::json;select '{"name":"Tom","age":16,"birthday":"2011-10-10"}'::json;select '{"name":"Tom","age":16,"birthday":"2011-10-10"}'::jsonb;
create table json_table(id bigserial,info json,infob jsonb
);create index json_index on json_table(info); -- 报错
create index json_indexb on json_table(infob); -- 正确
| JSON | PGSQL |
|---|---|
| string | text |
| number | numberic |
| boolean | boolean |
| null | (none) |
复合类型
类比一个model对象。
-- 考虑如下结构,只表示结构
User{Id id;Info info;
}
Info{string name;Integer age;
}-- 使用pgsql的复合类型先映射Info,再映射User
create type type_nfo as (name varchar(32),age int);
create table public.user(id serial,info type_info
);-- 添加数据-> ()
insert into public.user (info) values(('Hah',23));
数组类型
create table public.test(id serial,col1 int[],col2 int[2],col3 int[][]
);select '{how,are,you}'::varchar[];insert into public.test(col1,col2,col3) values ('{1,2,3}','{2,3,4}',array[[2,2,4],[1,2,3]]);insert into public.test(col1,col2,col3) values ('{1,2,3}','{2,3,4}','{{2,2,4},{1,2,3}}');-- 当存储的varchar内容等存在单引号，我们需要用两个单引号代替一个单引号
select '{''how'',are}'::varchar[];-- "，"使用双引号包裹/使用\转义
select '{"how,are",sad}'::varchar[];
select '{how\,are,sad}'::varchar[];-- 有双引号
select '{\"how\",are}'::varchar[];
数据类型数组的比较
@> 包含
<@ 被包含
&& 比较相同
select array[1,2,3] @> array[1];select array[1,2] <@ [2,3,4];select array[1,2] && array[2,1];