資料型別

0. 資料型別的分類

分類名稱	說明	與其他資料庫對比
布爾型別	支持SQL標準boolean型別	與MYSQL中的bool，boolean型別一樣，占用1位元組空間
數值型別	整數型別有2位元組的smallint，2位元組int 8位元組bigint ；精確數字型別numeric;非精確型別的浮點小數real和double precision 還有8位元組的money（貨幣型別）	無MYSQL中的unsigned型別，也無mysql中的1位元組長tinint和3位元組長的mediumint整數型別
字符型別	有varchar(n)，char(n)，text三種型別	pg中最大可存盤1G mysql中字符最大可存盤64KB,PG中的text型別相當于MYSQL中的longtext型別
二進制資料型別	bytea型別	對應的MYSQL中的blob和longblob型別
位串型別	由 0 和 1組成的字串，有bit(n),bit varying(n)兩種型別	PG最大可支持8388080個bit位
日期和時間型別	date ,time, timestamp	PG可精確到秒以下，MYSQL可精確到毫秒
列舉型別	enum	PGSQL列舉需要預先使用create type來創建該型別
幾何型別	包括點(point)，直線(line)，線段(lseg)，路徑(path)，多邊形(polygon),圓(cycle)	PG特有的型別
網路地址型別	有cidr,inet,macaddr 型別	PG特有
陣列型別	可以存盤一個陣列	PG特有型別
復合型別	可以把已有的型別組合成用戶自定義型別	對應其他資料庫的自定義型別
xml	XML相關資料存盤
JSON/JSONB型別	JSON型別	MYSQL在8.0后可支持
range型別	范圍型別，可存盤范圍資料	無
物件識別符號型別	PG內部標識物件型別，如oid型別，regproc型別，regclass型別
偽型別	不能作為欄位的資料型別，但他可用于宣告函式的引數或者結果的型別
UUID型別
其他型別

1. 數值型別

型別名稱	存盤長度	描述	范圍
smallint	2位元組	小范圍整數型別	-32768_{32767（-2^15}2^15-1）
integer	4位元組	整數型別	-2147483648_{2147493647（-2^31}2^31-1）
bigint	8位元組	大范圍整數型別	（-2^63~263-1）
decimal	可變	用戶指定精度	無限制
numeric	可變	用戶指定精度	無限制
real	4位元組	變長，不精確	6位十進制精度
double precision	8位元組	變長，不精確	15位十進制精度
smallserial	2位元組	smallint自增序列	1~32767
serial	4位元組	integer自增序列	1~2147483647 （1-2^31-1）
bigserial	8位元組	bigInteger自增序列	1~922372036854775807（1-2^64-1）

注意：

decimal和numeric是等效的
real和double precison 浮點型別
smallSerial和serial 和bingSerial指的是自增型別，但是其實是在創建后再后面默認添加了一個函式
浮點型別中還有幾個特殊值
- Infinity
- -Infinity
- NaN

2. 字符型別

字符型別名稱	描述
varchar(n)	變長型別，字符最大有限制，最大1G 存盤空間為:4+實際字符長度，
char(n)	定長欄位，沒達到最大用空白填充最大1G ,存盤空間為4+n
text	文本型別，變長，無限制

注意：

如果character varying(n)如果不宣告長度，則和text一樣，可以沒有限制長度
pgsql最大支持長度為1GB

2.1 字串函式

計算字符數：char_length(xxx);
計算占用位元組數：octet_length(xxx);
位置：postition(a in b)
子串：substring(str from 1 for 4)
拆分字串：split_part(String text,delimiter text,filed int)

3. 時間型別

字符型別名稱	存盤長度	描述
timestamp	8位元組	包括日期和時間，不帶時區，簡寫成timestamp
Timestamp with time zone	8位元組	包括時間和日期，帶時區，簡寫timestamptz
date	4位元組	日期，但不包含一天中的時間
time	8位元組	一天中的時間，不包含日期，不帶時區
time with time zone	12位元組	一天中的時間，不包含日期，帶時區
interval	16位元組	時間間隔

3.1 時間型別函式

日期相加

select date '2017-07-29'+ interval'1 days'

日期相減

select date '2017-07-29'- interval'1 days'

常用函式

顯示當前時間：select current_date,current_time;
抽取年月日: select extract(field from source)

時間函式

CURREN_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
now()

4. 布爾型別(bool)

1 位元組，狀態為true或false

5. 網路地址型別

字符型別名稱	存盤長度	描述
cidr	7或者19位元組	IPV4或者IPV6
inet	7或者19位元組	IPV4或者IPV6
macaddr	6位元組	MAC地址
macaddr8	8位元組	MAC地址（EUI-64格式）

6. 陣列型別

在創建的時候添加[]就可以變成陣列型別

CREATE table text_arr1(

    id integer,

	arr_i integer[],

	arr_t text[]
)

插入

insert into text_arr1 values(1,'{1,2,3}','["dadsa","dsada"]')	
insert into text_arr1 values(1,array[1,2,3],array["dadsa","dsada"])

陣列追加：array_append(anyArr,anyElem)

陣列remove: array_remove(anyArr,anyElem);

獲取陣列維度：array_ndims(陣列)

獲取陣列的位置:array_postition(arr,findElem)

替換:array_replace(arr,sourceElem,target)

TOString:array_to_string(arr,sourceElem,target)

7. 范圍型別

int4range:integer范圍型別
int8range:bigint范圍型別
numrange:numeric 范圍型別
tsrange :不帶時區的timestamp范圍型別
tstzrange:帶時區的timstamp范圍型別
date:date范圍型別

8. json/jsonb型別

這兩種型別使用上完全一致，JSON是把輸入的資料原封不動的存盤到資料中，但是jsonb型別是在存盤的時候就把JSON決議成為了二進制型別，JSONB型別會比JSON型別效率高

8.0 JSON支持的型別

JSON的型別	PG上的型別	注意事項
string	text	字符集只支持UTF-8
number	numeric	JSON中沒有PG中的NaN和infinity的值
boolean	boolean	JSON中只能接受小寫的true或者false
null	none	SQL中的NULL代表不同的意思

8.1 json型別

創建表

create table table_name(id serial primary key,name json);

插入資料

insert into table_name(name) value ('{"col1":"dd","col2":"col2value"}')

查詢

select * from table_name -- 全部
select name->'col2' from table_name --單欄位，顯示鍵值
select name ->>'col2' from table_name -- 單獨欄位，以文本格式

8.2 jsonb型別：其實就是以二進制存盤，并且會去除一些不必要的空格和洗掉重復鍵

-- 將josn物件轉換成鍵值物件
select * from json_each('{"col1":"dd","col2":"col2value"}')
--以文本形式回傳
select * from json_each_text('{"col1":"dd","col2":"col2value"}')
-- 將行轉換成json
select row_to_json(test_copy) from test_copy where id=1

9. 資料型別轉換

函式	回傳型別	描述	實體
to_char(timestamp,text)	text	把時間戳轉換成字串	to_char(current_timestamp,"HH12:MI:SS")
to_char(interval,text)	text	把間隔轉換成字串	to_char(interval '15h 2m 12s',"HH12:MI:SS")
To_char(int,text)	text	把整數型別轉換成字串
to_char(numeric,text)	text	把數字轉換成字串
to_date(text,text)	date	把字符轉換成日期
to_number(text,text)	numeric	把字符轉換成數字
to_timestamp(text,text)	Timestamp with time zone	把字串轉換成時間戳

用case 轉換

select cast(varchar'123' as text);
select cast(varchar'123' as int4);

通過::進行轉換

select 1::int4,3/2::numeric;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/271155.html

標籤：PostgreSQL

上一篇：gpfdist原理決議

下一篇：MongoDB常用命令

postgresql資料型別