postgreSQL之數據類型發布於 6 個月前 (10月25日) - 480 次檢閱

2020 年 4 月 9 日
筆記

以下是自己結合postgreSQL文檔手冊總結整理的入門手札，如有不嚴謹，請到官方文檔手冊查看：http://www.postgres.cn/docs/10/index.html

數字類型

pgsql支援的數字類型有整數類型、用戶指定精度類型、浮點型、和serial類型(自增)

數字類型圖：

整數類型

smallint、integer、bigint都是整數類型，存儲一定範圍的整數，超出範圍將會報錯。

PS：

smallint欄位定義時可以寫成int2,integer比smallint大，所以是int4(最常用的整數類型），同理bigint可寫成int8.

任意精度數字

類型decimal和numeric是等效的,可以存儲非常多位的數字. 比如numeric的語法：

1.numberic(precision,scale)  precision:指全部位數，必須為正整數-->（精度）  scale:指小數部分的數字位數，可以為0或者整數-->(標度)  2.numberic(precision)  3.numberic

PS:

numberic類型性能比整數類型性能低，所以如果兩種類型都能滿足需求，建議使用整數類型。最後：為了增強程式的移植性，最好同時指定numeric的精度和標度。

浮點類型

數據類型real和double precision是指浮點數據類型(不準確的、變精度的數字類型),real支援4位元組，double precision是支援8位元組。

序列號類型（官方文檔-->序數類型）

smallserial、serial和bigserial是自增類型，但嚴格意義上它不是真正的類型，它們只是為了創建唯一標識符列而存在的方便符號（類似其它一些資料庫中支援的AUTO_INCREMENT屬性），如下程式碼:

CREATE TABLE test_serial (      id serial,      flag text  );

等價於以下語句：

CREATE SEQUENCE test_serial_seq;  CREATE TABLE test_serial (      id integer NOT NULL DEFAULT,      flag text      nextval('test_serial_seq')  );  ALTER SEQUENCE test_serial_seq OWNED BY test_serial.id;

簡單的說，就是mysql自增(AUTO_INCREMENT).

PS:

類型名serial和serial4是等效的, 兩個都創建integer列。類型名bigserial和serial8也一樣，只不過它們創建一個 bigint列。類型名smallserial和serial2也以相同方式工作，只不過它們創建一個smallint列。

字元類型

字元串類型圖，如下：

character varying(n)存儲的是變長字元類型，其中n是一個正整數，如果存儲的字元串長度超過n會報錯喔,但是如果存儲字元串比n小的話，character varying(n)只會存儲實際的字元串。

character(n)存儲的是定長字元串，如果存儲的字元串長度超過n會報錯，比它小則會用空白填充...

說了那麼多，還是用一個例子吧：

創建一個表  create table test_char(col1 varchar(4),col2 char(4));  ---  插入數據  insert into test_char(col1,col2) values('a','a');  ---  比較字元串長度  select char_length(col1),char_length(col2) from test_char;  ----  char_length	char_length1  1	1  ----  比較字元串佔用的位元組數  select octet_length(col1),octet_length(col2) from test_char  ----  octet_length	octet_length1  1	4

varchar(n)和char(n)的概念分別是character varying(n)和character(n)的別名。沒有長度聲明詞的character等效於character(1)。如果不帶長度說明詞使用character varying，那麼該類型接受任何長度的串。後者是一個PostgreSQL的擴展。

text字元類型，它可以存儲任何長度的串。和沒有什麼字元長度的character varying(n)字元類型幾乎沒有差別。

PS:

這三種類型之間沒有性能差別，只不過是在使用填充空白的類型的時候需要更多存儲尺寸，以及在存儲到一個有長度約束的列時需要少量額外CPU周期來檢查長度。雖然在某些其它的資料庫系統里，character(n)有一定的性能優勢，但在PostgreSQL里沒有。事實上，character(n)通常是這三種類型之中最慢的一個，因為它需要額外的存儲開銷。在大多數情況下，應該使用text或者character varying.（postgreSQL支援字元串存儲的最長字串大概是 1 GB）

日期/時間類型

PostgreSQL 支援 SQL標準中所有的日期和時間類型。具體如下：

PS:

SQL要求只寫timestamp等效於timestamp without time zone，並且PostgreSQL鼓勵這種行為。timestamptz被接受為timestamp with time zone的一種簡寫，這是一種PostgreSQL的擴展

time、timestamp和interval接受一個可選的精度值 p，這個精度值聲明在秒域中小數點之後保留的位數。預設情況下，在精度上沒有明確的邊界，p允許的範圍是從 0 到 6。

說了那麼多，我們還是用實例說話吧... 首先先用系統自帶的now()函數，now()函數顯示是當前時間，並返回類型為：timestamp with time zone,如：

select now();  ---  now  2019-10-20 00:23:25.288484+08

如果需要轉到timestamp without time zone,並且不需要保留小數點，如

select now()::timestamp(0) without time zone;  ----  now  2019-10-20 00:24:42

轉換到date類型，如：

select now()::date;  ----  now  2019-10-20

轉到time without time zone，如：

select now()::time without time zone;  ----  now  00:26:33.661496

最後就是interval類型，其實interval類型是有一個附加選項，它可以通過寫下面之一的短語來限制存儲的fields的集合：

year,month,day,hour,minute,second,yeartomonth,daytohour,daytominute,daytosecond,hourtominute,hourtosecond,minutetosecond

又來舉個例子吧：

select now(),now()+interval '1 day'  ----  now	?column?  2019-10-20 00:29:49.530872+08	2019-10-21 00:29:49.530872+08

剩下那麼多附加選項，自己測吧...

布爾類型(boolean-->true/false)

「真(true)」狀態的有效文字值是:

TRUE、't'、'true'、'y'、'yes'、'on'、'1'

而對於「假」狀態有效文字值是:

FALSE、'f'、'false'、'n'、'no'、'off'、'0'

知道我們的通病都不喜歡看文字，而看例子，如：

create table test_boolean(col1 boolean,col2 boolean);  ----  insert into test_boolean(col1,col2) values('true','false');     insert into test_boolean(col1,col2) values('true','false');     insert into test_boolean(col1,col2) values('t','f');     insert into test_boolean(col1,col2) values('TRUE','FALSE');     insert into test_boolean(col1,col2) values('yes','no');     insert into test_boolean(col1,col2) values('y','n');     insert into test_boolean(col1,col2) values('1','0');     insert into test_boolean(col1,col2) values(null,null);     ---------     select * from test_boolean  ----  col1	col2  t	f  t	f  t	f  t	f  t	f  t	f    ps:一共有7條數據，其中布爾類型可以支援插入null字元...

網路地址類型

PostgreSQL提供用於存儲 IPv4、IPv6 和 MAC 地址的數據類型,使用網路地址類型存儲IP地址會優於字元類型，因為網路地址類型一方面會對數據合法性進行檢查，另一方面也提供了網路地址類型相關的函數，方便我們使用吧..

網路地址數據類型，如下：

inet或者cidr類型存儲的網路地址格式都為：address(地址)/y(網路掩碼的位數)

address就是表示ipv4或者ipv6的網路地址 y表示網路掩碼位數，如果y省略，則對於ipv4的網路掩碼就為32，而ipv6則為128,因此該值表示只有一台主機。

如果y顯示時，如果y部分指定一個單台主機，它將不會被顯示出來。

之前說了inet或者cidr類型都會對數據合法性進行檢查，如果數據不合法則會報錯，如：

[SQL]select '192.168.1.1000'::inet     [Err] ERROR:  invalid input syntax for type inet: "192.168.1.1000"  LINE 1: select '192.168.1.1000'::inet

inet和cidr網路類型也存在一定的差別噠。

1.cidr類型的輸出默認帶子網掩碼資訊，而inte不一定。如：

select '192.168.1.100'::cidr,'192.168.1.100'::inet  ---  cidr	inet  192.168.1.100/32	192.168.1.100  ---------  select '192.168.1.100/32'::cidr,'192.168.1.100/32'::inet  ---  cidr	inet  192.168.1.100/32	192.168.1.100  --------  select '192.168.1.100'::cidr,'192.168.1.100/16'::inet  ---  cidr	inet  192.168.1.100/32	192.168.1.100/16

2.cidr類型對ip地址和子網掩碼合法性進行檢查，而inte不會，如：

[SQL]select '192.168.1.100/8'::cidr     [Err] ERROR:  invalid cidr value: "192.168.1.100/8"  LINE 1: select '192.168.1.100/8'::cidr                 ^  DETAIL:  Value has bits set to right of mask.     --------------------     select '192.168.1.100/8'::inet  ----  inet  192.168.1.100/8

從上面的例子看出，cidr比inet網路類型更加得嚴謹吧... 簡單的說就是：

inet和cidr類型之間的本質區別是inet接受右邊有非零位的網路掩碼，而cidr不接受。例如，192.168.1.100/24對inet來說是有效的，但是cidr來說是無效的。

順便記錄一下網路操作符吧，因為oracle和mysql我自己也沒有用到過網路類型的。

macaddr和macaddr8

macaddr和macaddr8是存儲mac地址。對於mac的介紹更多可到：http://www.postgres.cn/docs/10/datatype-net-types.html

數組類型

postgreSQL支援一維數據or多維數組，常用的數據類型為數字類型的數組和字元串的數據，但也支援其他類型的吧?枚舉類型、複合類型數組...怎麼支援自己去全球最大的同性戀交友網站看官網手冊..(github.com)

怎麼用數組類型?噗,開玩笑我也不會... 那麼我們就從怎麼創建表開始吧..如：

create table test_array(  id INTEGER,  array_i INTEGER[],  arrat_t TEXT[]  );

從創表語句看，INTEGER[]就是Integer類型的一維數組了，同樣，TEXT[]就是text類型的一維數據. 什麼?那麼二維數組呢???喵喵喵???不就是:

create table test_array_er(  id INTEGER,  array_i INTEGER[][],  arrat_t TEXT[][]  );

數組類型插入數據有兩種方式，一種方式使用花括弧的方式，即：{}-->:

'{val1 delim val2 delim ...}'

說白了，就是將數組元素值用花括弧「{}」包圍並用delim分隔符分開，delim分隔符常用基本上為逗號?如：

select '{1,2,3}'  ----  ?column?  {1,2,3}

往表test_array 插入一條數據：

insert into test_array(id,array_i,arrat_t)  values (1,'{1,2,3}','{"a","b","c"}');

往數組插入的第二張方式為使用array關鍵字，如：

select array[1,2,3]  ----  array  {1,2,3}

往表test_array 插入一條數組：

insert into test_array(id,array_i,arrat_t)  values (2,array[1,2,3],array['a','b','c']);

同理，如果插入多維數組表，如：

insert into test_array_er(id,array_i,arrat_t)  values (1,'{{1,2,3},{4,5,6}}','{{"a","b","c"},{"e","f","g"}}');  ----  insert into test_array_er(id,array_i,arrat_t)  values (2,array[[1,2,3],[4,5,6]],array[['a','b','c'],['e','f','g']]);

查詢數組元素

如果要查詢數組所有元素值，只需要查詢數組欄位名稱即可，如：

select array_i from test_array

數組元素的引用可以通過方括弧[]方式，數據下表卸載方括弧裡面，範圍基本上是1到n，n為數組長度，超出數據範圍返回null，如：

select array_i[1],arrat_t[1] from test_array

數組元素追加、刪除、更新

數組元素的追加使用array_append函數，如:

array_append(anyarray,anyelement)

使用array_append函數向數組末端追加一個元素，如：

select array_append(array[1,2,3],4)  ----  array_append  {1,2,3,4}

也可以用array_prepend函數，如：

SELECT array_prepend(1, ARRAY[2,3]);  ----  array_prepend  {1,2,3}

更可以用array_cat函數，如：

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);  ----  array_cat  {1,2,3,4}     ------------  SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);  array_cat  ---------------------   {{1,2},{3,4},{5,6}}

array_prepend、array_append或array_cat這三個函數區別就是前兩個函數僅支援一維數組，但array_cat支援多維數組。

數據元素追加到數組也可以使用||，如：

select array[1,2,3]||4  ----  ?column?  {1,2,3,4}

數組元素的刪除是使用array_remove函數，如:

array_remove(anyarray,anyelement)

使用array_remove函數將移除數組中等於給定值的所有數組元素，如：

select array[1,2,2,3],array_remove(array[1,2,2,3],2)  ----  array	array_remove  {1,2,2,3}	{1,3}

修改數組

1.一個數組值可以被整個替換：

花括弧的語法方式：  update test_array set array_i='{2,3,4}' where id =1  ----  使用ARRAY表達式語法:  update test_array set array_i=array[1,3,4] where id =1

2.可以在一個元素上被更新，如：

update test_array set array_i[3] = 5 where id =1

或者是二維數組的切片更新，如：

update test_array_er set array_i[1:2] = array[[7,8,9],[8,9,10]] where id =1

數組元素豐富的操作符：

想要更深入了解數組類型，可到：http://www.postgres.cn/docs/10/arrays.html

json/jsonb類型

json類型

開始我們先用一個簡單的json例子開啟json類型的使用吧~

select '{"a":1,"b":2}'::json  ----  json  {"a":1,"b":2}

這就是強轉的一個小例子，我們還是創一個表吧，如：

create table test_json(  id serial primary key,  name json  );  insert into test_json(name) values('{"col1":1,"col2":"glj","col3":"male"}');  insert into test_json(name) values('{"col1":2,"col2":"hhm","col3":"famale"}');  select * from test_json;  -----  id	name  1	{"col1":1,"col2":"glj","col3":"male"}  2	{"col1":2,"col2":"hhm","col3":"famale"}

查詢json數據

通過『->』操作符可以查詢json數據的鍵值，如：

select name->'col2' from test_json  ----  ?column?  "glj"  "hhm"

如果想以文本格式返回json欄位的鍵值可以使用『->>』操作符，如：

select name->>'col2' from test_json  ----  ?column?  glj  hhm

jsonb鍵/值得追加、刪除、更新

jsonb鍵/值追加可以通過||操作符，例如增加sex的鍵值，如：

select '{"name":"glj","age":23}'::jsonb || '{"sex":"male"}'::jsonb  ----  ?column?  {"age": 23, "sex": "male", "name": "glj"}

jsonb鍵/值刪除有兩種辦法，一是通過操作符-刪除，另一種是通過操作符 #- 刪除指定的鍵/值，首先先通過操作符- 刪除鍵/值，如：

select '{"name":"glj","age":23,"sex":"male"}'::jsonb - 'sex'  ----  ?column?  {"age": 23, "name": "glj"}

再通過操作符 #- 刪除鍵/值，但這種方式通常用於嵌套json的數據刪除的，如：

select 	'{"name":"glj","contact":{"phone":"123456779","blogAddrUrl":"http://199604.com"}}'::jsonb #- '{contact,phone}'  ------  ?column?  {"name": "glj", "contact": {"blogAddrUrl": "http://199604.com"}}

方式二：刪除contact中位置1的鍵/值，如：

select 	'{"name":"glj","contact":["0","1","2","3"]}'::jsonb #- '{contact,1}'  -----  ?column?  {"name": "glj", "contact": ["0", "2", "3"]}

jsonb鍵/值更新也有兩種辦法，一種是操作符 ||的方式，如：

select '{"name":"glj","age":22}'::jsonb || '{"age":23}'  ----  ?column?  {"age": 24, "name": "glj"}

這種方式其實就是之前的筆記，jsonb的特點後續會說，就是jsonb會刪除重複的鍵，僅僅保留最後一個。

第二種方式是通過jsonb_set函數，語法：

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

target 指的是jsonb源數據，path 指的是路徑 new_value指的是更新後的鍵值，create_missing當為true是，表示鍵不存在時，則添加，反之，不存在就不添加，如：

select jsonb_set('{"name":"glj","age":22}'::jsonb,'{age}','"23"'::jsonb,false)  ----  jsonb_set  {"age": "23", "name": "glj"}

json和jsonb的對比差異

JSON 數據類型：json 和 jsonb，其實他們基本上一樣噠，主要的實際區別之一是效率。因為json存儲格式為文本，而jsonb存儲格式為二進位，由於存儲格式的不同使得兩者數據類型的處理效率就不太一樣了吧~json類型以文本存儲並且存儲的內容和輸入的內容是一樣的，當要檢索json數據的時候，就需要重新解析，而jsonb不需要，因為它是二進位形式存儲，已經解析好了數據，所以檢索jsonb的數據時，不要重新解析即可查找，因此json寫入比jsonb快，而檢索時候就相反了。

說了那麼多，我們用一個簡單的例子看看吧，例如就是jsonb的輸出鍵順暢就和輸入時候不一樣，如：

select '{"name":"glj","age":23,"sex":"male"}'::jsonb,'{"name":"glj","age":23,"sex":"male"}'::json  ------  jsonb	json  {"age": 23, "sex": "male", "name": "glj"}	{"name":"glj","age":23,"sex":"male"}

從上面看出來，json輸入和輸出順序是一致的。還有，就是jsonb類型會去掉輸入數據中的鍵值空格，而json則不會，如：

select '{"name":"glj",        "age":23,"sex":"male"}'::jsonb,  '{"name":"glj",       "age":23,"sex":"male"}'::json  ---------  jsonb	json  {"age": 23, "sex": "male", "name": "glj"}	{"name":"glj",       "age":23,"sex":"male"}

最最最後，就是jsonb會刪除重複的鍵，僅僅保留最後一個，如:

select '{"name":"glj","age":23,"sex":"male","name":"hhm"}'::jsonb,  '{"name":"glj","age":23,"sex":"male","name":"hhm"}'::json  -----  jsonb	json  {"age": 23, "sex": "male", "name": "hhm"}	{"name":"glj","age":23,"sex":"male","name":"hhm"}

看看吧，上面的name重複了，但是jsonb它是保留了最後一個name鍵的值，而json雞巴沒有這個校驗的規則，它是你輸入啥它就給你輸出啥..所以建議盡量使用jsonb，而除非是那種雞巴特殊需求除外，比如就是對json的鍵值有順序要求的..（這句話說得好沒有底氣，因為我tm也是小白..）

jsonb和json操作符

想要更深入了解json/jsonb類型，可到：http://www.postgres.cn/docs/10/datatype-json.html

Tags: