Postgres bytea類型轉換及查看操作

Posted on 2020-12-25 by WalkonNet

一開發表裡的列類型為 bytea ，可是它select出來的時候又不是想要的結果：

在postgres配置文件裡修改參數

bytea_output = ‘escape’

這個默認是hex類型的，修改成escape。

查看的時候還是不是想要的結果：

select encode(data::bytea,’hex’) from data_from_target limit 1;

補充：PostgreSQL的數據類型及日常實踐筆記

數據類型是編程語言中，在其數據結構上定義的相同值類型的集合以及對該相同值集合的一組操作。而數據類型的值存儲離不開變量，因此變量的一個作用就是使用它來存儲相同值集的數據類型。數據類型決定瞭如何將代表這些值的集合存儲在計算機的內存中。變量一般遵循先聲明後使用的原則。而在數據庫中，變量就是字段，用字段來表示一組相同值類型的集合，其實也是先聲明後使用的原則。

PostgreSQL支持豐富的數據類型，包括一般的數據類型和非常規的數據類型。一般數據類型包括數值型，貨幣類型，字符類型，日期類型，佈爾類型，枚舉類型等，非常規數據類型包括二進制數據類型，幾何類型，網絡地址類型，位串類型，文本搜索類型，UUID類型,XML類型，JSON類型，數組類型，復合類型，范圍類型，Domain類型，OID類型，pg_lsn類型和pseudo-Types類型。

一數值類型*

1.1整型

PostgreSQL中的整型類型有小整型，整型，大整型，用 smallint,integer,和bigint表示，雖然三個都可以存儲相同的數據類型，但是它們各自的存儲大小和存儲范圍卻不相同。見下表：

名稱	描述	存儲空間	范圍
SMALLINT	小范圍整數，別名為INT2。	2字節	-32,768 – +32,767
INTEGER	常用的整數，別名為INT4。	4字節	-2,147,483,648 – +2,147,483,647
BIGINT	大范圍的整數，別名為INT8。	8字節	-9,223,372,036,854,775,808 – 9,223,372,036,854,775,807

如下示例所示，在PostgreSQL中，smallint，integer，bigint 數據類型可以使用 int2,int4,int8的擴展寫法來標識。

示例：

hrdb=# --創建整型數據類型的表
hrdb=# CREATE TABLE IF NOT EXISTS tab_num(v1 smallint,v2 smallint,v3 int,v4 int,v5 bigint,v6 bigint);
CREATE TABLE
hrdb=# --表字段註釋
hrdb=# COMMENT ON COLUMN tab_num.v1 IS '小整型最小范圍';
COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v2 IS '小整型最大范圍';
COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v3 IS '整型最小范圍';
COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v4 IS '整型最大范圍';
COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v5 IS '大整型最小范圍';
COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v6 IS '大整型最大范圍';
COMMENT
hrdb=# --描述數據類型
hrdb=# \d+ tab_num
         Table "public.tab_num"
 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 
--------+----------+-----------+----------+---------+---------+--------------+----------------
 v1  | smallint |   |   |   | plain |    | 小整型最小范圍
 v2  | smallint |   |   |   | plain |    | 小整型最大范圍
 v3  | integer |   |   |   | plain |    | 整型最小范圍
 v4  | integer |   |   |   | plain |    | 整型最大范圍
 v5  | bigint |   |   |   | plain |    | 大整型最小范圍
 v6  | bigint |   |   |   | plain |    | 大整型最大范圍

hrdb=# --插入不同整型的范圍數值
hrdb=# INSERT INTO tab_num
hrdb-# VALUES (-32768,
hrdb(#   32767,
hrdb(#   -2147483648,
hrdb(#   2147483647,
hrdb(#   -9223372036854775808,
hrdb(#   9223372036854775807);
INSERT 0 1
hrdb=# --查詢結果
hrdb=# SELECT * FROM tab_num;
 v1 | v2 |  v3  |  v4  |   v5   |   v6   
--------+-------+-------------+------------+----------------------+---------------------
 -32768 | 32767 | -2147483648 | 2147483647 | -9223372036854775808 | 9223372036854775807
(1 row)

如上所示，查詢的結果為插入不同整型范圍的最值，也說明不同整型范圍的邊界都是被包括的。在實際生產場景中，SMALLINT、INTEGER和BIGINT類型存儲各種范圍的數字，也就是整數。當試圖存儲超出范圍以外的數值將會導致錯誤。

常用的類型是INTEGER，因為它提供瞭在范圍、存儲空間、性能之間的最佳平衡。一般隻有取值范圍確定不超過SMALLINT的情況下，才會使用SMALLINT類型。而隻有在INTEGER的范圍不夠的時候才使用BIGINT，因為前者相對要快。

除此之外，創建表也可以使用 int2,int4,int8來代表 smallint,integer,bigint。如下示例所示：

hrdb=# /*
hrdb*# smallint,integer,bigint
hrdb*# 數據類型分別使用
hrdb*# int2,int4,int8代替
hrdb*# */
hrdb-# CREATE TABLE IF NOT EXISTS tab_numint(v1 int2,v2 int2,v3 int4,v4 int4,v5 int8,v6 int8);
CREATE TABLE
hrdb=# --描述表定義及數據類型
hrdb=# \d+ tab_numint
         Table "public.tab_numint"
 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 
--------+----------+-----------+----------+---------+---------+--------------+-------------
 v1  | smallint |   |   |   | plain |    | 
 v2  | smallint |   |   |   | plain |    | 
 v3  | integer |   |   |   | plain |    | 
 v4  | integer |   |   |   | plain |    | 
 v5  | bigint |   |   |   | plain |    | 
 v6  | bigint |   |   |   | plain |

1.2任意精度類型和浮點類型

任意精度類型 numeric、decimal可以存儲范圍大的數字，存儲大小為可變大小，小數點前最多131072位數字，小數點後最多16383位。它可以使用類似浮點類型，將小數精確到保留幾位，也可以參與計算可以得到準確的值，但是相比於浮點類型，它的計算比較慢。通常 numeric被推薦使用於存儲貨幣金額或其它要求計算準確的值。詳細見下表：

名稱	描述	存儲空間	范圍
NUMERIC[(p[,s])],DECIMAL[(p[,s])]	精度p取值范圍為[1,1000]，標度s取值范圍為[0,p]。說明： p為總位數，s為小數位數	用戶聲明精度。每四位（十進制位）占用兩個字節，然後在整個數據上加上八個字節的額外開銷。	未指定精度的情況下，小數點前最大131,072位，小數點後最大16,383位。
real	可變精度	4個字節	6位小數精度
double precision	可變精度	8個字節	15位小數精度

示例：任意精度類型

hrdb=# --任意精度類型示例
hrdb=# CREATE TABLE IF NOT EXISTS tab_any_precision(col1 numeric(10,4),col2 decimal(6,4),col3 real,col4 double precision,col5 float4,col6 float8);
CREATE TABLE
hrdb=# --字段註釋
hrdb=# COMMENT ON COLUMN tab_any_precision.col1 IS '表示整數最大位數為6,小數僅保留4位';
COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col2 IS '表示整數最大位數為2,小數保留4位';
COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col3 IS '表示可變的6位精度的數值類型';
COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col4 IS '表示可變的15位精度的數值類型';
COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col5 IS '同real';
COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col6 IS '同double precision';
COMMENT
hrdb=# --查看表定義
hrdb=# \d+ tab_any_precision
           Table "public.tab_any_precision"
 Column |  Type  | Collation | Nullable | Default | Storage | Stats target |   Description   
--------+------------------+-----------+----------+---------+---------+--------------+-----------------------------------
 col1 | numeric(10,4) |   |   |   | main |    | 表示整數最大位數為6,小數僅保留4位
 col2 | numeric(6,4)  |   |   |   | main |    | 表示整數最大位數為2,小數保留4位
 col3 | real    |   |   |   | plain |    | 表示可變的6位精度的數值類型
 col4 | double precision |   |   |   | plain |    | 表示可變的15位精度的數值類型
 col5 | real    |   |   |   | plain |    | 同real
 col6 | double precision |   |   |   | plain |    | 同double precision

hrdb=# --插入任意精度測試
hrdb=# INSERT INTO tab_any_precision
hrdb-# VALUES(202004.26,20.2004,20.200426,20.203415341535157,20.200426,20.203415341535157);
INSERT 0 1
hrdb=# INSERT INTO tab_any_precision
hrdb-# VALUES(202004.26105,20.20045,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575);
INSERT 0 1
hrdb=# --可以發現col1和col2小數部分可以超過4位，但是讀取僅僅保留4位，並遵循四舍五入的原則,如下結果
hrdb=# SELECT * FROM tab_any_precision;
 col1  | col2 | col3 |  col4  | col5 |  col6  
-------------+---------+---------+------------------+---------+------------------
 202004.2600 | 20.2004 | 20.2004 | 20.2034153415352 | 20.2004 | 20.2034153415352
 202004.2611 | 20.2005 | 20.2004 | 20.2034153415352 | 20.2004 | 20.2034153415352
(2 rows)

hrdb=# /*
hrdb*# 如果 col1 插入的整數最大位數超過6，將會報錯。
hrdb*# 如果 col2 插入的整數最大位數超過2，將會報錯。
hrdb*# real 和 double precision 沒有限制。
hrdb*# */
hrdb-# INSERT INTO tab_any_precision
hrdb-# VALUES(2020042.610,20.2004,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 4 must round to an absolute value less than 10^6.
hrdb=# INSERT INTO tab_any_precision
hrdb-# VALUES(202004.26105,202.200,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575);
ERROR: numeric field overflow
DETAIL: A field with precision 6, scale 4 must round to an absolute value less than 10^2.
hrdb=#

1.3 序列類型

SMALLSERIAL，SERIAL和BIGSERIAL類型不是真正的數據類型，隻是為在表中設置唯一標識做的概念上的便利。因此，創建一個整數字段，並且把它的缺省數值安排為從一個序列發生器讀取。應用瞭一個NOT NULL約束以確保NULL不會被插入。在大多數情況下用戶可能還希望附加一個UNIQUE或PRIMARY KEY約束避免意外地插入重復的數值，但這個不是自動的。最後，將序列發生器從屬於那個字段，這樣當該字段或表被刪除的時候也一並刪除它。

名稱	描述	存儲空間	范圍
SMALLSERIAL	二字節序列整型	2字節	1 – 32,767
SERIAL	四字節序列整型	4字節	1 – 2,147,483,647
BIGSERIAL	八字節序列整型	8字節	1 – 9,223,372,036,854,775,807

示例：

hrdb=# --創建序列類型表
hrdb=# CREATE TABLE tab_serial(col1 smallserial,col2 serial,col3 bigserial);
CREATE TABLE
hrdb=# --字段註釋
hrdb=# COMMENT ON COLUMN tab_serial.col1 IS '小整型序列，從1開始，最大值為32767';
COMMENT
hrdb=# COMMENT ON COLUMN tab_serial.col2 IS '小整型序列，從1開始，最大值為2147483647';
COMMENT
hrdb=# COMMENT ON COLUMN tab_serial.col3 IS '小整型序列，從1開始，最大值為9223372036854775807';
COMMENT
hrdb=# --查看表定義
hrdb=# \d+ tab_serial
                 Table "public.tab_serial"
 Column | Type | Collation | Nullable |     Default     | Storage | Stats target |     Description     
--------+----------+-----------+----------+------------------------------------------+---------+--------------+--------------------------------------------------
 col1 | smallint |   | not null | nextval('tab_serial_col1_seq'::regclass) | plain |    | 小整型序列，從1開始，最大值為32767
 col2 | integer |   | not null | nextval('tab_serial_col2_seq'::regclass) | plain |    | 小整型序列，從1開始，最大值為2147483647
 col3 | bigint |   | not null | nextval('tab_serial_col3_seq'::regclass) | plain |    | 小整型序列，從1開始，最大值為9223372036854775807

hrdb=# --插入數據
hrdb=# INSERT INTO tab_serial VALUES(1,1,1);
INSERT 0 1
hrdb=# INSERT INTO tab_serial VALUES(32767,2147483647,9223372036854775807);
INSERT 0 1
hrdb=# --如果插入的值大於序列整型值的范圍，將會整型類型越界的ERROR
hrdb=# INSERT INTO tab_serial VALUES(32767,2147483647,9223372036854775808);
ERROR: bigint out of range
hrdb=# INSERT INTO tab_serial VALUES(32767,2147483648,9223372036854775807);
ERROR: integer out of range
hrdb=# INSERT INTO tab_serial VALUES(32768,2147483647,9223372036854775807);
ERROR: smallint out of range

hrdb=# --當然，既然是序列類型，那可以插入默認值
hrdb=# INSERT INTO tab_serial
hrdb-# VALUES(default,default,default);
INSERT 0 1

通過上述示例，可以知道 smallserial,serial,bigserial相當於先創建一個序列，然後在創建表分別指定不同的整型數據類型smallint,integer,bigint。如下示例：

hrdb=# --先創建序列
hrdb=# CREATE SEQUENCE IF NOT EXISTS serial_small
hrdb-# INCREMENT BY 1
hrdb-# START WITH 1 
hrdb-# NO CYCLE;
CREATE SEQUENCE
hrdb=# --再創建表
hrdb=# CREATE TABLE IF NOT EXISTS tab_test_serial(
hrdb(# col1 smallint default nextval('serial_small'),
hrdb(# col2 integer default nextval('serial_small'),
hrdb(# col3 bigint default nextval('serial_small')
hrdb(# );
CREATE TABLE
hrdb=# --插入數據
hrdb=# INSERT INTO tab_test_serial VALUES(default);
INSERT 0 1
hrdb=# --查詢數據
hrdb=# SELECT * FROM tab_test_serial ;
 col1 | col2 | col3 
------+------+------
 1 | 2 | 3
(1 row)

二貨幣數據類型

貨幣類型存儲帶有固定小數精度的貨幣金額。

關於貨幣數據類型的詳細信息如下表：

名稱	存儲容量	描述	范圍
money	8 字節	貨幣金額	-92233720368547758.08 到 +92233720368547758.07

示例：

hrdb=# --創建貨幣數據類型表
hrdb=# CREATE TABLE IF NOT EXISTS tab_money(amounts money);
CREATE TABLE
hrdb=# --字段註釋
hrdb=# COMMENT ON COLUMN tab_money.amounts IS '金額';
COMMENT
hrdb=# --插入數值
hrdb=# INSERT INTO tab_money VALUES('20.00');
INSERT 0 1
hrdb=# --查詢數據
hrdb=# SELECT * FROM tab_money;
 amounts 
---------
 $20.00
(1 row)

這裡需要註意的是，如果插入的貨幣數據類型的金額沒有明確指定貨幣表示符號，那麼默認輸出本區域貨幣符號，如上示例所示的20.00輸出為$20.00。

如果是人民幣，那麼如何處理呢？

解決方法有兩種，第一種，使用translate函數；第二種，修改本地區域貨幣符號顯示參數。

hrdb=# --方法一:直接使用translate函數將 $ 符號轉換為 ￥ 符號
hrdb=# SELECT translate(amounts::varchar,'$','￥') FROM tab_money;
 translate 
-----------
 ￥20.00
(1 row)

hrdb=# --方法二:修改區域貨幣符號顯示參數
hrdb=# --查看本地區域貨幣符號顯示參數
hrdb=# show lc_monetary ;
 lc_monetary 
-------------
 en_US.UTF-8
(1 row)

hrdb=# --修改區域貨幣符號顯示參數
hrdb=# ALTER SYSTEM SET lc_monetary = 'zh_CN.UTF-8';
ALTER SYSTEM
hrdb=# --重新加載動態參數
hrdb=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

hrdb=# --重新查看本地區域貨幣符號顯示參數
hrdb=# show lc_monetary;
 lc_monetary 
-------------
 zh_CN.UTF-8
(1 row)

hrdb=# --重新查詢數據
hrdb=# SELECT * FROM tab_money;
 amounts 
---------
 ￥20.00
(1 row)

貨幣符號作為特殊的數據類型，需要註意計算方式，以防止發生精度丟失的問題。

這種問題解決方式需要將貨幣類型轉換為 numeric 類型以避免精度丟失。

hrdb=# INSERT INTO tab_money VALUES('20.22');
INSERT 0 1
hrdb=# SELECT * FROM tab_money ;
 amounts 
---------
 ￥20.00
 ￥20.22
(2 rows)

hrdb=# --貨幣數據類型避免精度丟失的解決方法
SELECT amounts::numeric::float8 FROM tab_money;
 amounts 
---------
  20
 20.22

溫馨提示：

當一個money類型的值除以另一個money類型的值時，結果是double precision（也就是，一個純數字，而不是money類型）；在運算過程中貨幣單位相互抵消

三佈爾類型

PostgreSQL提供標準的boolean值，boolean的狀態為 true或者false和unknown，如果是unknown狀態表示boolean值為null。

名稱	描述	存儲空間	取值
BOOLEAN	佈爾類型	1字節	true：真 false：假 null：未知（unknown

示例：

hrdb=# --創建boolean類型表
hrdb=# CREATE TABLE IF NOT EXISTS tab_boolean(col1 boolean,col2 boolean);
CREATE TABLE
hrdb=# --插入佈爾類型的狀態值，狀態值可以是以下任意一種
hrdb=# INSERT INTO tab_boolean VALUES(TRUE,FALSE);--規范用法
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('true','false');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('True','False');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('TRUE','FALSE');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('1','0');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('on','off');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('ON','OFF');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('y','n');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('Y','N');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('yes','no');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('Yes','No');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('YES','NO');
INSERT 0 1
hrdb=# SELECT * FROM tab_boolean ;
 col1 | col2 
------+------
 t | f
 t | f
 t | f
 t | f
 t | f
 t | f
 t | f
 t | f
 t | f
 t | f
 t | f
 t | f
(12 rows)

boolean類型被廣泛地使用在業務環境中，例如手機開關機，1表示開機，0表示關機或不在服務區。手機APP登錄登出，1表示登錄，0表示登出，微信登陸狀態，1表示登錄成功，0表示登錄失敗（可能由於網絡或者密碼錯誤導致）等等，此處不再一一舉例。

四字符類型

SQL定義瞭兩種主要的字符類型:character varying(n) 和 character(n)。該處的n是一個正數。這兩種字符類型都可以存儲n（非字節）個長度的字符串。如果存儲的字符長度超過瞭字符類型約束的長度會引起錯誤，除非多出的字符是空格。

名稱	描述	存儲空間
CHAR(n)CHARACTER(n)	定長字符串，不足補空格。n是指字符長度，如不帶精度n，默認精度為1。	最大為10MB。
VARCHAR(n)CHARACTER VARYING(n)	變長字符串。n是指字符長度。	最大為10MB。
TEXT	變長字符串。	最大為1G-8023B（即1073733621B）。

註意，除瞭每列的大小限制以外，每個元組的總大小也不可超過1G-8023B（即1073733621B）。

在PostgreSQL中，除瞭以上的字符數據類型外，還有兩種特殊的字符類型如下：

名稱	描述	存儲空間
name	用於對象名的內部類型。	64字節
“char”	單字節內部類型。	1字節

示例：

hrdb=# --創建字符類型表
hrdb=# CREATE TABLE IF NOT EXISTS tab_chartype(
hrdb(# col1 char(15),
hrdb(# col2 varchar(15),
hrdb(# col3 text,
hrdb(# col4 name,
hrdb(# col5 "char" );
CREATE TABLE
hrdb=# --字段註釋
hrdb=# COMMENT ON COLUMN tab_chartype.col1 IS '表示定長為15的字符串';
COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col2 IS '表示變長為15的字符串';
COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col3 IS '表示變長字符串，為varchar的擴展字符串';
COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col4 IS '用於對象名的內部類型';
COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col5 IS '表示單字節類型';
COMMENT
hrdb=# --插入數據
hrdb=# INSERT INTO tab_chartype
hrdb-# VALUES('sungsasong','sungsasong','sungsasong','tab_chartype','s');
INSERT 0 1
hrdb=# --插入包含空格的數據
hrdb=# INSERT INTO tab_chartype
hrdb-# VALUES('sungsa song','sung sas ong','sung sa song ','tab_chartype','s');
INSERT 0 1
hrdb=# --計算不同數據類型存儲的字符串的長度
hrdb=# SELECT char_length(col1),char_length(col2),char_length(col3),char_length(col4),char_length(col5)
hrdb-# FROM tab_chartype ;
 char_length | char_length | char_length | char_length | char_length 
-------------+-------------+-------------+-------------+-------------
   10 |   10 |   10 |   12 |   1
   11 |   12 |   13 |   12 |   1

溫馨提示：

在上面示例中，雖然統計的col1的定長為15的字符存儲的字符長度為10個和11個，但是實際上，在存儲中col1列占用的長度為15個。並且，在計算長度的時候，空格也被當作一個字符來對待。

五二進制數據類型

在PostgreSQL中，二進制數據類型有兩種，一種為 bytea hex格式，一種為 bytea escape格式。

名稱	描述	存儲空間
BYTEA	變長的二進制字符串	4字節加上實際的二進制字符串。最大為1G-8203字節。

註意：除瞭每列的大小限制以外，每個元組的總大小也不可超過1G-8203字節。示例

hrdb=# --創建兩種bytea格式的表
hrdb=# CREATE TABLE IF NOT EXISTS tab_bytea(col1 bytea,col2 bytea);
CREATE TABLE
hrdb=# --字段註釋
hrdb=# COMMENT ON COLUMN tab_bytea.col1 IS 'bytea hex 格式的二進制串';
COMMENT
hrdb=# COMMENT ON COLUMN tab_bytea.col2 IS 'bytea escape 格式的二進制串';
COMMENT
hrdb=# --插入數據,第一個值代表單引號，輸出16進制的值為\x27,第二個為轉義16進制的值f
hrdb=# INSERT INTO tab_bytea
hrdb-# VALUES('\047',E'\xF');
INSERT 0 1
hrdb=# --插入數據,第一個值代表反斜杠，輸出16禁止的值為\x5c,第二個值為轉義16進制的值fc
hrdb=# INSERT INTO tab_bytea
hrdb-# VALUES('\134',E'\\xFC');
INSERT 0 1
hrdb=# --查看結果
hrdb=# SELECT * FROM tab_bytea;
 col1 | col2 
------+------
 \x27 | \x0f
 \x5c | \xfc

註意：

實際上bytea多個十六進制值使用E’\xFC’ 類似於Oracle中的rawtohex函數。隻不過Oracle中的rawtohex函數轉換後的值為大寫十六進制字符串。實際上如果要在上表中的col2中插入E’\xFG’時，會提示G不是一個有效的十六進制字符。

同時需要註意的是，如果使用E’\xF’隻包含單個十六進制字符時，使用一個反斜杠，如果有多個十六進制字符，需要兩個反斜杠，如E’\xFE’。

如下:此處的hextoraw函數為我自定義實現的一個UDF函數。

六日期時間數據類型

PostgreSQL支持豐富的日期時間數據類型如下表：

名稱	描述	存儲空間
DATE	日期和時間	4字節（實際存儲空間大小為8字節）
TIME [§] [WITHOUT TIME ZONE]	隻用於一日內時間。p表示小數點後的精度，取值范圍為0-6。	8字節
TIME [§] [WITH TIME ZONE]	隻用於一日內時間，帶時區。p表示小數點後的精度，取值范圍為0-6。	12字節
TIMESTAMP[§] [WITHOUT TIME ZONE]	日期和時間。p表示小數點後的精度，取值范圍為0-6。	8字節
TIMESTAMP[§][WITH TIME ZONE]	日期和時間，帶時區。TIMESTAMP的別名為TIMESTAMPTZ。p表示小數點後的精度，取值范圍為0-6。	8字節
reltime	相對時間間隔。格式為：X years X mons X days XX:XX:XX。	4字節

6.1日期輸入

日期和時間的輸入可以是任何合理的格式，包括ISO-8601格式、SQL-兼容格式、傳統POSTGRES格式或者其它的形式。系統支持按照日、月、年的順序自定義日期輸入。如果把DateStyle參數設置為MDY就按照“月-日-年”解析，設置為DMY就按照“日-月-年”解析，設置為YMD就按照“年-月-日”解析。

日期的文本輸入需要加單引號包圍，語法如下：

type [ ( p ) ] ‘value’

可選的精度聲明中的p是一個整數，表示在秒域中小數部分的位數。

示例：

hrdb=> --創建日期輸入表
hrdb=> CREATE TABLE tab_datetype(col1 date);
CREATE TABLE
hrdb=> --字段註釋
hrdb=> COMMENT ON COLUMN tab_datetype.col1 IS '日期類型，默認遵循datestyle風格(MDY)';
COMMENT
hrdb=> --插入數據
hrdb=> INSERT INTO tab_datetype VALUES(date '04-26-2020');
INSERT 0 1
hrdb=> --在MDY風格下，也支持YMD的輸入方式，但是不支持DMY或者其它格式的輸入，如下會報錯
hrdb=> INSERT INTO tab_datetype VALUES(date '22-04-2020');
ERROR: date/time field value out of range: "22-04-2020"
LINE 1: INSERT INTO tab_datetype VALUES(date '22-04-2020');
            ^
HINT: Perhaps you need a different "datestyle" setting.
hrdb=> --解決辦法,修改datestyle的格式
hrdb=> --查看當前數據庫的datestyle的格式
hrdb=> show datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

hrdb=> --會話級別修改datestyle格式
hrdb=> SET datestyle = 'DMY';
SET
hrdb=> --再次插入 22-04-2020
hrdb=> INSERT INTO tab_datetype VALUES(date '22-04-2020');
INSERT 0 1
hrdb=> --查詢數據
hrdb=> SELECT * FROM tab_datetype ;
 col1 
------------
 2020-04-26
 2020-04-22

6.2時間輸入

時間類型包括

time [ (p) ] without time zone 和time [ (p) ] with time zone。



如果隻寫time等效於time without time zone。即不帶時區的時間格式



如果在time without time zone類型的輸入中聲明瞭時區，則會忽略這個時區。



示例：

hrdb=> --不帶時區的時間
hrdb=> SELECT time '13:22:25';
 time 
----------
 13:22:25
(1 row)

hrdb=> SELECT time without time zone '20:20:18';
 time 
----------
 20:20:18
(1 row)

hrdb=> SELECT time with time zone '18:20:20';
 timetz 
-------------
 18:20:20+08
(1 row)

6.3 特殊時間類型

特殊時間類型以reltime表示，表示真實的時間計算值，如100將會使用00:01:40來表示。

示例：

hrdb=> --創建reltime時間數據類型表
hrdb=> CREATE TABLE tab_reltime(col1 varchar,col2 reltime);
CREATE TABLE
hrdb=> --字段註釋
hrdb=> COMMENT ON COLUMN tab_reltime.col1 IS '原始時間文本時間';
COMMENT
hrdb=> COMMENT ON COLUMN tab_reltime.col2 IS 'reltime表示的時間以實際時間計算得到顯示結果';
COMMENT
hrdb=> --插入數據
hrdb=> INSERT INTO tab_reltime VALUES('125','125');
INSERT 0 1
hrdb=> INSERT INTO tab_reltime VALUES('10 DAYS','10 DAYS');
INSERT 0 1
hrdb=> INSERT INTO tab_reltime VALUES('420 DAYS 12:00:23','420 DAYS 12:00:23');
INSERT 0 1
hrdb=> --查詢數據
hrdb=> SELECT * FROM tab_reltime;
  col1  |    col2    
-------------------+-------------------------------
 125    | 00:02:05
 10 DAYS   | 10 days
 420 DAYS 12:00:23 | 1 year 1 mon 25 days 06:00:23

溫馨提示：

對於 reltime 時間的輸入，需要使用文本類型的輸入，也就是說使用單引號引起來。

6.4 其它時間類型

其它時間類型包含時間戳及間隔時間數據類型，示例如下：

示例：

hrdb=> --創建時間戳和間隔時間表
hrdb=> CREATE TABLE tab_timestamp_interval(col1 timestamp with time zone,col2 timestamp without time zone,col3 interval day to second);
CREATE TABLE
hrdb=> --字段註釋
hrdb=> COMMENT ON COLUMN tab_timestamp_interval.col1 IS '帶時區的時間戳';
COMMENT
hrdb=> COMMENT ON COLUMN tab_timestamp_interval.col2 IS '不帶時區的時間戳';
COMMENT
hrdb=> COMMENT ON COLUMN tab_timestamp_interval.col1 IS '間隔時間類型';
COMMENT
hrdb=> --插入數據
hrdb=> INSERT INTO tab_timestamp_interval
hrdb-> VALUES('2020-04-26 13:20:34.234322 CST',
hrdb(>  '2020-04-08 14:40:12.234231+08',
hrdb(>  '165');
INSERT 0 1
hrdb=> INSERT INTO tab_timestamp_interval
hrdb-> VALUES('2020-04-25 14:56:34.223421',
hrdb(>  '2020-04-09 18:54:12.645643 CST',
hrdb(>  '10 YEAR 3 MONTH 25 DAYS 14 HOUR 32 MINUTE 19 SECOND');
INSERT 0 1
hrdb=> --查詢數據
hrdb=> SELECT * FROM tab_timestamp_interval;
    col1    |   col2   |    col3    
-------------------------------+----------------------------+----------------------------------
 2020-04-27 03:20:34.234322+08 | 2020-04-08 14:40:12.234231 | 00:02:45
 2020-04-25 14:56:34.223421+08 | 2020-04-09 18:54:12.645643 | 10 years 3 mons 25 days 14:32:19

時間數據類型在業務應用中使用非常廣泛，如手機APP登錄時間，登出時間，金融業務交易時間等等.

七網絡地址類型

PostgreSQL也提供網絡地址類型，以用於存儲兩大IP傢族(IPv4 IPv6地址)地址和MAC地址的數據類型。

名稱	存儲空間	描述
cidr	7或19字節	IPv4或IPv6網絡
inet	7或19字節	IPv4或IPv6主機和網絡
macaddr	6字節	MAC地址

cidr（無類別域間路由，Classless Inter-Domain Routing）類型，保存一個IPv4或IPv6網絡地址。聲明網絡格式為address/y，address表示IPv4或者IPv6地址，y表示子網掩碼的二進制位數。如果省略y，則掩碼部分使用已有類別的網絡編號系統進行計算，但要求輸入的數據已經包括瞭確定掩碼所需的所有字節。

inet類型在一個數據區域內保存主機的IPv4或IPv6地址，以及一個可選子網。主機地址中網絡地址的位數表示子網（“子網掩碼”）。如果子網掩碼是32並且地址是IPv4，則這個值不表示任何子網，隻表示一臺主機。在IPv6裡，地址長度是128位，因此128位表示唯一的主機地址。

該類型的輸入格式是address/y，address表示IPv4或者IPv6地址，y是子網掩碼的二進制位數。如果省略/y，則子網掩碼對IPv4是32，對IPv6是128，所以該值表示隻有一臺主機。如果該值表示隻有一臺主機，/y將不會顯示。

inet和cidr類型之間的基本區別是inet接受子網掩碼，而cidr不接受。

macaddr類型存儲MAC地址，也就是以太網卡硬件地址（盡管MAC地址還用於其它用途）。

示例：

hrdb=> --創建IP地址及MAC地址表
hrdb=> CREATE TABLE tab_icm(col1 cidr,col2 inet,col3 macaddr);
CREATE TABLE
hrdb=> --字段註釋
hrdb=> COMMENT ON COLUMN tab_icm.col1 IS '存儲IPv4或IPv6網絡地址類型';
COMMENT
hrdb=> COMMENT ON COLUMN tab_icm.col2 IS '存儲IPv4或IPv6網絡地址類型及子網';
COMMENT
hrdb=> COMMENT ON COLUMN tab_icm.col3 IS '存儲設備MAC地址';
COMMENT
hrdb=> --插入數據
hrdb=> INSERT INTO tab_icm VALUES('10.10.20.10/32','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10.10.20/24','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10.10/16','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10/8','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('fe80::81a7:c17c:788c:7723/128','fe80::81a7:c17c:788c:7723','00-50-56-C0-00-01');
INSERT 0 1
hrdb=> --查詢數據                         
SELECT * FROM tab_icm;
    col1    |   col2   |  col3  
-------------------------------+---------------------------+-------------------
 10.10.20.10/32    | 10.10.20.10    | 00:50:56:c0:00:08
 10.10.20.0/24     | 10.10.20.10    | 00:50:56:c0:00:08
 10.10.0.0/16     | 10.10.20.10    | 00:50:56:c0:00:08
 10.0.0.0/8     | 10.10.20.10    | 00:50:56:c0:00:08
 fe80::81a7:c17c:788c:7723/128 | fe80::81a7:c17c:788c:7723 | 00:50:56:c0:00:01
(5 rows)

八幾何數據類型

PostgreSQL支持集合數據類型，用於存儲GIS（地理信息系統）環境中的幾何數據，用於地圖測繪，城市交通軌跡，地圖圈圖等場景。

PostgreSQL支持以下幾何數據類型：

點

線(射線)

線段

矩形

路徑(包含開放路徑【開放路徑類似多邊形】和閉合路徑)

多邊形

圓

對於以上幾何類型而言，點是其它幾何類型的基礎。

名稱	存儲空間	說明	表現形式
point	16字節	平面中的點	(x,y)
lseg	32字節	（有限）線段	((x1,y1),(x2,y2))
box	32字節	矩形	((x1,y1),(x2,y2))
path	16+16n字節	閉合路徑（與多邊形類似）	((x1,y1),…)
path	16+16n字節	開放路徑	[(x1,y1),…]
polygon	40+16n字節	多邊形（與閉合路徑相似）	((x1,y1),…)
circle	24字節	圓	<(x,y),r> （圓心和半徑）

對於所有的幾何數據類型，都是使用二維坐標上面的橫坐標和縱坐標來實現的。計算也是在二維坐標中進行的。

示例：

hrdb=> --創建幾何數據類型表
hrdb=> CREATE TABLE tab_geometric(col1 point,col2 lseg,col3 box,col4 path,col5 path,col6 polygon,col7 circle);
CREATE TABLE
hrdb=> --字段註釋
hrdb=> COMMENT ON COLUMN tab_geometric.col1 IS '二維幾何的基本構造點(x,y)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col2 IS '線段((x1,y1),(x2,y2))';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col3 IS '矩形((x1,y1),(x1,y2),(x2,y1),(x2,y1)),';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col4 IS '開放路徑((x1,y1),(x2,y2),(x3,y3),...)';
COMMENT
hrdb=> drop table tab_geometric ;
DROP TABLE
hrdb=> --創建幾何數據類型表
hrdb=> CREATE TABLE tab_geometric(col1 point,col2 lseg,col3 box,col4 path,col5 path,col6 polygon,col7 circle);
CREATE TABLE
hrdb=> --字段註釋
hrdb=> COMMENT ON COLUMN tab_geometric.col1 IS '二維幾何的基本構造點(x,y)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col2 IS '線段[(x1,y1),(x2,y2)]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col3 IS '矩形((x1,y1),(x1,y2)),';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col4 IS '開放路徑[(x1,y1),(x2,y2),(x3,y3),...]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col5 IS '閉合路徑[(x1,y1),(x2,y2),(x3,y3),...,(xn,yn)]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col6 IS '多邊形,相當於閉合路徑((x1,y1),(x2,y2),(x3,y3),...,(xn,yn)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col7 IS '一組坐標點作為圓心和半徑r構成<(x,y),r>';
COMMENT
hrdb=> --插入數據 
hrdb=> INSERT INTO tab_geometric
hrdb-> VALUES('(1,2)',
hrdb(>  '[(1,2),(2,3)]',
hrdb(>  '((1,2),(1,3))',
hrdb(>  '[(1,2),(2,3),(2,4),(1,3),(0,2)]',
hrdb(>  '[(1,2),(2,3),(3,4)]',
hrdb(>  '((1,2),(2,3),(2,4),(1,3),(0,2))',
hrdb(>  '<(2,3),3>');
INSERT 0 1
hrdb=> --查詢數據
hrdb=> SELECT * FROM tab_geometric;
 col1 |  col2  | col3  |    col4    |  col5   |    col6    | col7 
-------+---------------+-------------+---------------------------------+---------------------+---------------------------------+-----------
 (1,2) | [(1,2),(2,3)] | (1,3),(1,2) | [(1,2),(2,3),(2,4),(1,3),(0,2)] | [(1,2),(2,3),(3,4)] | ((1,2),(2,3),(2,4),(1,3),(0,2)) | <(2,3),3>

九 JSON數據類型

JSON數據類型可以用來存儲JSON（JavaScript Object Notation）數據。數據可以存儲為text，但是JSON數據類型更有利於檢查每個存儲的數值是可用的JSON值。

在 PostgreSQL中，JSON數據類型有兩種，原生JSON和JSONB。最主要的區別就是效率不同。JSON 數據類型對於輸入文本進行復制，因此在解析時需要進行轉換，輸入速度塊。而JSONB是對輸入文本進行分解並以二進制存儲，因此在解析時不需要進行轉換，處理速度塊，但是輸入速度相對會慢。除此之外，JSONB數據類型還支持索引。

示例：

hrdb=> --創建JSON數據類型表
hrdb=> CREATE TABLE tab_json(col1 json,col2 jsonb);
CREATE TABLE
hrdb=> --字段註釋
hrdb=> COMMENT ON COLUMN tab_json.col1 IS '存儲json輸入文本';
COMMENT
hrdb=> COMMENT ON COLUMN tab_json.col1 IS '存儲json轉換後的二進制文本';
COMMENT
hrdb=> --插入數據
hrdb=> --插入數據
hrdb=> INSERT INTO tab_json
hrdb-> VALUES('{"江蘇省":"南京市","甘肅省":"蘭州市","北京市":"北京市"}',
hrdb(>  '{"湖北省":"武漢市","四川省":"成都市","陜西省":"西安市"}');
INSERT 0 1
hrdb=> --給col1創建索引，將會不被支持。col2支持索引
hrdb=> CREATE INDEX idx_col1 ON tab_json USING GIN(col1);
ERROR: data type json has no default operator class for access method "gin"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
hrdb=> CREATE INDEX idx_col2 ON tab_json USING GIN(col2);
CREATE INDEX
hrdb=> --查詢數據
hrdb=> SELECT * FROM tab_json;
       col1       |        col2        
---------------------------------------------------------+--------------------------------------------------------------
 {"江蘇省":"南京市","甘肅省":"蘭州市","北京市":"北京市"} | {"四川省": "成都市", "湖北省": "武漢市", "陜西省": "西安市"}

溫馨提示：

使用jsonb類型，可以使用PL/PYTHON映射為Python中表示的字典，列表等。

十數組數據類型

ostgreSQL支持數組數據類型，同時支持多維數組。數組最大的優點就是按照數組下標訪問，此時下標相當於一個索引，處理速度快。但是同時數組也有劣勢，比如在刪除或者添加數組元素需要對數組中的元素進行向前或者向後移動，這樣導致刪除或者添加數組元組時比較慢。

示例：

hrdb=> --創建數組表
hrdb=> CREATE TABLE tab_array(col1 text[],col2 integer[][],col3 integer ARRAY[3]);
CREATE TABLE
hrdb=> --字段註釋
hrdb=> COMMENT ON COLUMN tab_array.col1 IS '文本類型一維數組';
COMMENT
hrdb=> COMMENT ON COLUMN tab_array.col2 IS '整型類型二維數組';
COMMENT
hrdb=> COMMENT ON COLUMN tab_array.col3 IS '聲明長度為3的數組';
COMMENT
hrdb=> --插入數據
hrdb=> INSERT INTO tab_array
hrdb-> VALUES('{"江蘇省","甘肅省","北京市"}',
hrdb(>  '{1,2,3,4,5}',
hrdb(>  '{21,22,31}');
INSERT 0 1
hrdb=> INSERT INTO tab_array
hrdb-> VALUES('{"天津市","湖北省","陜西市"}',
hrdb(>  '{5,4,3,2,1}',
hrdb(>  '{21,22,31,44}');
INSERT 0 1
hrdb=> --查詢數據
hrdb=> SELECT * FROM tab_array;
   col1   | col2  |  col3  
------------------------+-------------+---------------
 {江蘇省,甘肅省,北京市} | {1,2,3,4,5} | {21,22,31}
 {天津市,湖北省,陜西市} | {5,4,3,2,1} | {21,22,31,44}
(2 rows)

hrdb=> --訪問指定列中某個數組的元素
hrdb=> SELECT col1[1],col2[3],col3[4] FROM tab_array;
 col1 | col2 | col3 
--------+------+------
 江蘇省 | 3 |  
 天津市 | 3 | 44

通過上述示例，可以發現，在PostgreSQL中，雖然聲明瞭數組的長度，但是PostgreSQL對於數組的長度不會做任何限制。

同時訪問數組元素從下標1開始，並且在PostgreSQL中並不會出現數組越界異常，如果數組的下標訪問超過元素的長度，那麼PostgreSQL便會返回一行空值。

以上就是常用數據類型介紹。但是在PostgreSQL中，除瞭上述數據類型外，還有其它的數據類型，比如XML數據類型，文本搜索數據類型，UUID數據類型，復合數據類型，范圍類型，偽類型如any,anyelement,internal等等，在此不做一一介紹。

Postgres bytea類型轉換及查看操作

一數值類型*

二貨幣數據類型

三佈爾類型

四字符類型

五二進制數據類型

六日期時間數據類型

七網絡地址類型

八幾何數據類型

九 JSON數據類型

十數組數據類型

推薦閱讀：

發佈留言取消回覆

近期文章

一 數值類型*

二 貨幣數據類型

三 佈爾類型

四 字符類型

五 二進制數據類型

六 日期時間數據類型

七 網絡地址類型

八 幾何數據類型

九 JSON數據類型

十 數組數據類型

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

一數值類型*

二貨幣數據類型

三佈爾類型

四字符類型

五二進制數據類型

六日期時間數據類型

七網絡地址類型

八幾何數據類型

十數組數據類型

發佈留言取消回覆