PostgreSQL數(shù)據(jù)類型說明

PGSQL支持的類型特別豐富，大多數(shù)的類型和MySQL都有對(duì)應(yīng)的關(guān)系

名稱	說明	對(duì)比MySQL
布爾類型	boolean，標(biāo)準(zhǔn)的布爾類型，只能存儲(chǔ)true，false	MySQL中雖然沒有對(duì)應(yīng)的boolean，但是有替換的類型，數(shù)值的tinyint類型，和PGSQL的boolean都是占1個(gè)字節(jié)。
整型	smallint（2字節(jié)），integer（4字節(jié)），bigint（8字節(jié)）	跟MySQL沒啥區(qū)別。
浮點(diǎn)型	decimal，numeric（和decimal一樣一樣的，精準(zhǔn)浮點(diǎn)型），real（float），double precision（double），money（貨幣類型）	和MySQL基本也沒區(qū)別，MySQL支持float，double，decimal。MySQL沒有這個(gè)貨幣類型。
字符串類型	varchar(n)（character varying），char(n)（character），text	這里和MySQL基本沒區(qū)別。 PGSQL存儲(chǔ)的varchar類型，可以存儲(chǔ)一個(gè)G。MySQL好像存儲(chǔ)64kb。
日期類型	date（年月日），time（時(shí)分秒），timestamp（年月日時(shí)分秒）（time和timestamp可以設(shè)置時(shí)區(qū)）	沒啥說的，和MySQL基本沒區(qū)別。 MySQL有個(gè)datetime。
二進(jìn)制類型	bytea-存儲(chǔ)二進(jìn)制類型	MySQL也支持，MySQL中是blob
位圖類型	bit(n)（定長(zhǎng)位圖），bit varying(n)（可變長(zhǎng)度位圖）	就是存儲(chǔ)0，1。MySQL也有，只是這個(gè)類型用的不多。
枚舉類型	enum，跟Java的enum一樣	MySQL也支持。
幾何類型	點(diǎn)，直線，線段，圓…………	MySQL沒有，但是一般開發(fā)也用不到
數(shù)組類型	在類型后，追加[]，代表存儲(chǔ)數(shù)組	MySQL沒有~~~
JSON類型	json（存儲(chǔ)JSON數(shù)據(jù)的文本），jsonb（存儲(chǔ)JSON二進(jìn)制）	可以存儲(chǔ)JSON，MySQL8.x也支持
ip類型	cidr（存儲(chǔ)ip地址）	MySQL也不支持~

更多數(shù)據(jù)類型見官網(wǎng)

PostgreSQL數(shù)據(jù)類型使用

單引號(hào)和雙引號(hào)

在PGSQL中，寫SQL語句時(shí)，單引號(hào)用來標(biāo)識(shí)實(shí)際的值。雙引號(hào)用來標(biāo)識(shí)一個(gè)關(guān)鍵字，比如表名，字段名。

-- 單引號(hào)寫具體的值，雙引號(hào)類似MySQL的``標(biāo)記，用來填充關(guān)鍵字
-- 下面的葡萄牙會(huì)報(bào)錯(cuò)，因?yàn)槠咸蜒啦皇顷P(guān)鍵字
select 1.414,'卡塔爾',"葡萄牙";

數(shù)據(jù)類型轉(zhuǎn)換

第一種方式：只需要在值的前面，添加上具體的數(shù)據(jù)類型即可

-- 將字符串轉(zhuǎn)成位圖類型
select bit '010101010101001';

第二種方式：也可以在具體值的后面，添加上 ::類型 ，來指定

-- 數(shù)據(jù)類型
select '2011-11-11'::date;
select '101010101001'::bit(20);
select '13'::int;

第三種方式：使用CAST函數(shù)

-- 類型轉(zhuǎn)換的完整寫法
select CAST(varchar '100' as int);

布爾類型

布爾類型可以存儲(chǔ)三個(gè)值，true，false，null。

布爾類型的約束沒有那么強(qiáng)，true，false大小寫隨意，他會(huì)給你轉(zhuǎn)，同時(shí)yes，no這種他也認(rèn)識(shí)，但是需要轉(zhuǎn)換

select true,false,'yes'::boolean,boolean 'no',True,FaLse,NULL::boolean;

boolean類型在做and和or的邏輯操作時(shí)的結(jié)果

字段A	字段B	A and B	A or B
true	true	true	true
true	false	false	true
true	NULL	NULL	true
false	false	false	false
false	NULL	false	NULL
NULL	NULL	NULL	NULL

數(shù)值類型

整型

整型比較簡(jiǎn)單，主要就是三個(gè)：

• smallint、int2：2字節(jié)
• integer、int、int4：4字節(jié)
• bigint、int8：8字節(jié)

一般就用integer，如果要存主鍵，比如雪花算法，那就bigint。如果要節(jié)約空間，根據(jù)情況可選smallint。

浮點(diǎn)型

浮點(diǎn)類型就關(guān)注2個(gè)

• decimal(n,m)：本質(zhì)就是numeric，PGSQL會(huì)幫你轉(zhuǎn)換
• numeric(n,m)：PGSQL本質(zhì)的浮點(diǎn)類型

針對(duì)浮點(diǎn)類型的數(shù)據(jù)，就使用 numeric

序列

MySQL中的主鍵自增，是基于auto_increment去實(shí)現(xiàn)。MySQL里沒有序列的對(duì)象。PGSQL和Oracle十分相似，支持序列：sequence。序列的正常構(gòu)建方式：

create sequence erdan.table_id_seq;
-- 查詢下一個(gè)值
select nextval('erdan.table_id_seq');
-- 查詢當(dāng)前值
select currval('erdan.table_id_seq');

序列大多數(shù)的應(yīng)用，是用作表的主鍵自增效果。

-- 表自增
create table erdan.xxx(id int8 default nextval('erdan.table_id_seq'),name varchar(16)
);
insert into erdan.xxx (name) values ('xxx');
select * from erdan.xxx;

PGSQL提供了序列的數(shù)據(jù)類型，可以在聲明表結(jié)構(gòu)時(shí)，直接指定序列的類型即可。

• smallserial
• serial
• bigserial

-- 表自增（爽）
create table erdan.yyy(id bigserial,   name varchar(16)
);
insert into erdan.yyy (name) values ('yyy');

在drop表之后，序列不會(huì)被刪除，但是序列會(huì)變?yōu)椴豢捎玫臓顟B(tài)。因?yàn)樾蛄性谑褂胹erial去構(gòu)建時(shí)，會(huì)綁定到指定表的指定列上。如果是單獨(dú)構(gòu)建序列，再構(gòu)建表，使用傳統(tǒng)方式實(shí)現(xiàn)，序列和表就是相對(duì)獨(dú)立的。

數(shù)值的常見操作

針對(duì)數(shù)值下面實(shí)現(xiàn)加減乘除取余這5個(gè)操作

操作符	描述	示例	結(jié)果
^	冪	2 ^ 3	8
|/	平方根	|/ 36	6
@	絕對(duì)值	@ -5	5
&	與	31 & 16	16
|	或	31|32	63
<<	左移	1<<1	2
>>	右移	16>>1	8

數(shù)值操作也提供了一些函數(shù)，比如pi()，round(數(shù)值，位數(shù))，floor()，ceil()

字符串類型

字符串類型用的是最多的一種，在PGSQL里，主要支持三種：

• character（就是MySQL的char類型），定長(zhǎng)字符串。（最大可以存儲(chǔ)1G）
• character varying（varchar），可變長(zhǎng)度的字符串。（最大可以存儲(chǔ)1G）
• text（跟MySQL異常）長(zhǎng)度特別長(zhǎng)的字符串。

操作可以查看官方說明

日期類型

在PGSQL中，核心的時(shí)間類就三個(gè)。

• timestamp（時(shí)間戳，覆蓋 年月日時(shí)分秒）
• date（年月日）
• time（時(shí)分秒）

在PGSQL中，聲明時(shí)間只需要使用字符串正常的編寫 yyyy-MM-dd HH:mm:ss 使用數(shù)據(jù)轉(zhuǎn)換方式就可以轉(zhuǎn)換為時(shí)間類型。

當(dāng)前系統(tǒng)時(shí)間 ：

• 可以使用now作為當(dāng)前系統(tǒng)時(shí)間（沒有時(shí)區(qū)的概念）

  select timestamp 'now';-- 直接查詢now，沒有時(shí)區(qū)的概念select time with time zone 'now' at time zone '08:00:00'

• 也可以使用current_timestamp的方式獲取（推薦，默認(rèn)東八區(qū)）

日期類型的運(yùn)算:

• 正常對(duì)date類型做+，-操作，默認(rèn)單位就是天~
• date + time = timestamp~~~

  select date '2011-11-11' + time '12:12:12' ;

• 可以針對(duì)timestamp使用interval的方式進(jìn)行 +，-操作，在查詢以時(shí)間范圍為條件的內(nèi)容時(shí)，可以使用

  select timestamp '2011-11-11 12:12:12' + interval '1day' + interval '1minute' + interval '1month';

枚舉類型

枚舉類型MySQL也支持，只是沒怎么用，PGSQL同樣支持這種數(shù)據(jù)類型。聲明枚舉類型作為表中的字段類型，這樣可以無形的給表字段追加規(guī)范。

-- 聲明一個(gè)星期的枚舉，值自然只有周一~周日。
create type week as enum ('Mon','Tues','Sun');
-- 聲明一張表，表中的某個(gè)字段的類型是上面聲明的枚舉。
drop table test;
create table test(id bigserial ,weekday week
);
insert into test (weekday) values ('Mon');
insert into test (weekday) values ('Fri');

IP類型

PGSQL支持IP類型的存儲(chǔ)，支持IPv4，IPv6這種，甚至Mac那種詭異類型也支持。這種IP類型，可以在存儲(chǔ)IP時(shí)，幫助做校驗(yàn)，其次也可以針對(duì)IP做范圍查找。IP校驗(yàn)的效果如下：

IP也支持范圍查找：

JSON&JSONB類型

JSON在MySQL8.x中也做了支持，但是MySQL支持的不好，因?yàn)镴SON類型做查詢時(shí)，基本無法給JSON字段做索引。PGSQL支持JSON類型以及JSONB類型，兩者使用基本沒區(qū)別。

JSON和JSONB的區(qū)別：

• JSON類型無法構(gòu)建索引，JSONB類型可以創(chuàng)建索引。
• JSON類型的數(shù)據(jù)中多余的空格會(huì)被存儲(chǔ)下來。JSONB會(huì)自動(dòng)取消多余的空格。
• JSON類型甚至可以存儲(chǔ)重復(fù)的key，以最后一個(gè)為準(zhǔn)。JSONB不會(huì)保留多余的重復(fù)key（保留最后一個(gè)）。
• JSON會(huì)保留存儲(chǔ)時(shí)key的順序，JSONB不會(huì)保留原有順序。

JSON中key對(duì)應(yīng)的value的數(shù)據(jù)類型

JSON	PGSQL
String	text
number	numeric
boolean	boolean
null	(none)

[{"name": "張三"},{"name": {"info": "xxx"}}]

操作JSON：

• 上述的四種JSON存儲(chǔ)的類型：

  select '9'::JSON,'null'::JSON,'"erdan"'::JSON,'true'::json;select '9'::JSONB,'null'::JSONB,'"erdan"'::JSONB,'true'::JSONB;

• JSON數(shù)組

  select '[9,true,null,"我是字符串"]'::JSON;

• JSON對(duì)象

  select '{"name": "張三","age": 23,"birthday": "2011-11-11","gender": null}'::json;select '{"name": "張三","age": 23,"birthday": "2011-11-11","gender": null}'::jsonb;

• 構(gòu)建表存儲(chǔ)JSON

  create table test(id bigserial,info json,infob jsonb);insert intotest(info,infob)   values ('{"name":            "張三"              ,"age": 23,"birthday": "2011-11-11","gender": null}','{"name":               "張三"             ,"age": 23,"birthday": "2011-11-11","gender": null}')select * from test;

• 構(gòu)建索引的效果

  create index json_index on test(info);create index jsonb_index on test(infob);

JSON還支持很多函數(shù)，可以直接查看官方

復(fù)合類型

復(fù)合類型就好像Java中的一個(gè)對(duì)象，Java中有一個(gè)User，User和表做了一個(gè)映射，User中有個(gè)人信息對(duì)象?？梢曰诜项愋蛯?duì)映射上個(gè)人信息。

public class User{private Integer id;private Info info;
}class Info{private String name;private Integer age;
}

按照上面的情況，將Info構(gòu)建成一個(gè)復(fù)合類型

-- 構(gòu)建復(fù)合類型，映射上Info
create type info_type as (name varchar(32),age int);
-- 構(gòu)建表，映射User
create table tb_user(id serial,info info_type
);
-- 添加數(shù)據(jù)
insert into tb_user (info) values (('張三',23));
insert into tb_user (info) values (('露絲',233));
insert into tb_user (info) values (('jack',33));
insert into tb_user (info) values (('李四',24));
select * from tb_user;

數(shù)組類型

PGSQL中，指定數(shù)組的方式就是[]，可以指定一維數(shù)組，也支持二維甚至更多維數(shù)組。構(gòu)建數(shù)組的方式：

drop table test;
create table test(id serial,col1 int[],col2 int[2],col3 int[][]
);
-- 構(gòu)建表指定數(shù)組長(zhǎng)度后，并不是說數(shù)組內(nèi)容只有2的長(zhǎng)度，可以插入更多數(shù)據(jù)
-- 甚至在你插入數(shù)據(jù)，如果將二維數(shù)組結(jié)構(gòu)的數(shù)組扔到一維數(shù)組上，也可以存儲(chǔ)。
-- 數(shù)組編寫方式
select '{{how,are},{are,you}}'::varchar[];
select array[[1,2],[3,4]];
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{7,8,9}');
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}',array[[1,2],[3,4]]);
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{{1,2},{3,4}}');
select * from test;

如果現(xiàn)在要存儲(chǔ)字符串?dāng)?shù)組，如果存儲(chǔ)的數(shù)組中有雙引號(hào)怎么辦，有大括號(hào)怎么辦。

-- 如果存儲(chǔ)的數(shù)組中的值，有單引號(hào)怎么辦？
-- 使用兩個(gè)單引號(hào)，作為一個(gè)單引號(hào)使用
select '{''how''}'::varchar[];
-- 如果存儲(chǔ)的數(shù)組中的值，有逗號(hào)怎么辦？(PGSQL中的數(shù)組索引從1開始算，寫0也是從1開始算。)
-- 用雙引號(hào)將數(shù)組的數(shù)據(jù)包起來~
select ('{"how,are"}'::varchar[])[2];
-- 如果存儲(chǔ)的數(shù)組中的值，有雙引號(hào)怎么辦？
-- 如果要添加雙引號(hào)，記得轉(zhuǎn)義。
select ('{"\"how\",are"}'::varchar[])[1];

數(shù)組的比較方式

-- 包含
select array[1,2] @> array[1];
-- 被包含
select array[1,2] <@ array[1,2,4];
-- 是否有相同元素
select array[2,4,4,45,1] && array[1];