Postgres bytea類型 轉(zhuǎn)換及查看操作
一開發(fā)表里的列類型為 bytea ，可是它select出來的時(shí)候又不是想要的結(jié)果：
在postgres配置文件里修改參數(shù)
bytea_output = 'escape'
這個(gè)默認(rèn)是hex類型的，修改成escape。
查看的時(shí)候還是不是想要的結(jié)果：
select encode(data::bytea,'hex') from data_from_target limit 1;
補(bǔ)充：PostgreSQL的數(shù)據(jù)類型及日常實(shí)踐筆記
數(shù)據(jù)類型是編程語言中，在其數(shù)據(jù)結(jié)構(gòu)上定義的相同值類型的集合以及對(duì)該相同值集合的一組操作。而數(shù)據(jù)類型的值存儲(chǔ)離不開變量，因此變量的一個(gè)作用就是使用它來存儲(chǔ)相同值集的數(shù)據(jù)類型。數(shù)據(jù)類型決定了如何將代表這些值的集合存儲(chǔ)在計(jì)算機(jī)的內(nèi)存中。變量一般遵循先聲明后使用的原則。而在數(shù)據(jù)庫(kù)中，變量就是字段，用字段來表示一組相同值類型的集合，其實(shí)也是先聲明后使用的原則。
PostgreSQL支持豐富的數(shù)據(jù)類型，包括一般的數(shù)據(jù)類型和非常規(guī)的數(shù)據(jù)類型。一般數(shù)據(jù)類型包括數(shù)值型，貨幣類型，字符類型，日期類型，布爾類型，枚舉類型等，非常規(guī)數(shù)據(jù)類型包括二進(jìn)制數(shù)據(jù)類型，幾何類型，網(wǎng)絡(luò)地址類型，位串類型，文本搜索類型，UUID類型,XML類型，JSON類型，數(shù)組類型，復(fù)合類型，范圍類型，Domain類型，OID類型，pg_lsn類型和pseudo-Types類型。
一 數(shù)值類型*
1.1整型
PostgreSQL中的整型類型有小整型，整型，大整型，用 smallint,integer,和bigint表示，雖然三個(gè)都可以存儲(chǔ)相同的數(shù)據(jù)類型，但是它們各自的存儲(chǔ)大小和存儲(chǔ)范圍卻不相同。見下表：
| 名稱 | 描述 | 存儲(chǔ)空間 | 范圍 |
|---|---|---|---|
| SMALLINT | 小范圍整數(shù)，別名為INT2。 | 2字節(jié) | -32,768 - +32,767 |
| INTEGER | 常用的整數(shù)，別名為INT4。 | 4字節(jié) | -2,147,483,648 - +2,147,483,647 |
| BIGINT | 大范圍的整數(shù)，別名為INT8。 | 8字節(jié) | -9,223,372,036,854,775,808 - 9,223,372,036,854,775,807 |
如下示例所示，在PostgreSQL中，smallint，integer，bigint 數(shù)據(jù)類型可以使用 int2,int4,int8的擴(kuò)展寫法來標(biāo)識(shí)。
示例：
hrdb=# --創(chuàng)建整型數(shù)據(jù)類型的表
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=# --描述數(shù)據(jù)類型
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=# --插入不同整型的范圍數(shù)值
hrdb=# INSERT INTO tab_num
hrdb-# VALUES (-32768,
hrdb(# 32767,
hrdb(# -2147483648,
hrdb(# 2147483647,
hrdb(# -9223372036854775808,
hrdb(# 9223372036854775807);
INSERT 0 1
hrdb=# --查詢結(jié)果
hrdb=# SELECT * FROM tab_num;
v1 | v2 | v3 | v4 | v5 | v6
--------+-------+-------------+------------+----------------------+---------------------
-32768 | 32767 | -2147483648 | 2147483647 | -9223372036854775808 | 9223372036854775807
(1 row)
如上所示，查詢的結(jié)果為插入不同整型范圍的最值，也說明不同整型范圍的邊界都是被包括的。在實(shí)際生產(chǎn)場(chǎng)景中，SMALLINT、INTEGER和BIGINT類型存儲(chǔ)各種范圍的數(shù)字，也就是整數(shù)。當(dāng)試圖存儲(chǔ)超出范圍以外的數(shù)值將會(huì)導(dǎo)致錯(cuò)誤。
常用的類型是INTEGER，因?yàn)樗峁┝嗽诜秶?、存?chǔ)空間、性能之間的最佳平衡。一般只有取值范圍確定不超過SMALLINT的情況下，才會(huì)使用SMALLINT類型。而只有在INTEGER的范圍不夠的時(shí)候才使用BIGINT，因?yàn)榍罢呦鄬?duì)要快。
除此之外，創(chuàng)建表也可以使用 int2,int4,int8來代表 smallint,integer,bigint。如下示例所示：
hrdb=# /*
hrdb*# smallint,integer,bigint
hrdb*# 數(shù)據(jù)類型分別使用
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=# --描述表定義及數(shù)據(jù)類型
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任意精度類型和浮點(diǎn)類型
任意精度類型 numeric、decimal可以存儲(chǔ)范圍大的數(shù)字，存儲(chǔ)大小為可變大小，小數(shù)點(diǎn)前最多131072位數(shù)字，小數(shù)點(diǎn)后最多16383位。它可以使用類似浮點(diǎn)類型，將小數(shù)精確到保留幾位，也可以參與計(jì)算可以得到準(zhǔn)確的值，但是相比于浮點(diǎn)類型，它的計(jì)算比較慢。通常 numeric被推薦使用于存儲(chǔ)貨幣金額或其它要求計(jì)算準(zhǔn)確的值。詳細(xì)見下表：
| 名稱 | 描述 | 存儲(chǔ)空間 | 范圍 |
|---|---|---|---|
| NUMERIC[(p[,s])],DECIMAL[(p[,s])] | 精度p取值范圍為[1,1000]，標(biāo)度s取值范圍為[0,p]。說明： p為總位數(shù)，s為小數(shù)位數(shù) | 用戶聲明精度。每四位（十進(jìn)制位）占用兩個(gè)字節(jié)，然后在整個(gè)數(shù)據(jù)上加上八個(gè)字節(jié)的額外開銷。 | 未指定精度的情況下，小數(shù)點(diǎn)前最大131,072位，小數(shù)點(diǎn)后最大16,383位。 |
| real | 可變精度 | 4個(gè)字節(jié) | 6位小數(shù)精度 |
| double precision | 可變精度 | 8個(gè)字節(jié) | 15位小數(shù)精度 |
示例：任意精度類型
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 '表示整數(shù)最大位數(shù)為6,小數(shù)僅保留4位';
COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col2 IS '表示整數(shù)最大位數(shù)為2,小數(shù)保留4位';
COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col3 IS '表示可變的6位精度的數(shù)值類型';
COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col4 IS '表示可變的15位精度的數(shù)值類型';
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 | | 表示整數(shù)最大位數(shù)為6,小數(shù)僅保留4位
col2 | numeric(6,4) | | | | main | | 表示整數(shù)最大位數(shù)為2,小數(shù)保留4位
col3 | real | | | | plain | | 表示可變的6位精度的數(shù)值類型
col4 | double precision | | | | plain | | 表示可變的15位精度的數(shù)值類型
col5 | real | | | | plain | | 同real
col6 | double precision | | | | plain | | 同double precision
hrdb=# --插入任意精度測(cè)試
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=# --可以發(fā)現(xiàn)col1和col2小數(shù)部分可以超過4位，但是讀取僅僅保留4位，并遵循四舍五入的原則,如下結(jié)果
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 插入的整數(shù)最大位數(shù)超過6，將會(huì)報(bào)錯(cuò)。
hrdb*# 如果 col2 插入的整數(shù)最大位數(shù)超過2，將會(huì)報(bào)錯(cuò)。
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類型不是真正的數(shù)據(jù)類型，只是為在表中設(shè)置唯一標(biāo)識(shí)做的概念上的便利。因此，創(chuàng)建一個(gè)整數(shù)字段，并且把它的缺省數(shù)值安排為從一個(gè)序列發(fā)生器讀取。應(yīng)用了一個(gè)NOT NULL約束以確保NULL不會(huì)被插入。在大多數(shù)情況下用戶可能還希望附加一個(gè)UNIQUE或PRIMARY KEY約束避免意外地插入重復(fù)的數(shù)值，但這個(gè)不是自動(dòng)的。最后，將序列發(fā)生器從屬于那個(gè)字段，這樣當(dāng)該字段或表被刪除的時(shí)候也一并刪除它。
| 名稱 | 描述 | 存儲(chǔ)空間 | 范圍 |
|---|---|---|---|
| SMALLSERIAL | 二字節(jié)序列整型 | 2字節(jié) | 1 - 32,767 |
| SERIAL | 四字節(jié)序列整型 | 4字節(jié) | 1 - 2,147,483,647 |
| BIGSERIAL | 八字節(jié)序列整型 | 8字節(jié) | 1 - 9,223,372,036,854,775,807 |
示例：
hrdb=# --創(chuàng)建序列類型表
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=# --插入數(shù)據(jù)
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=# --如果插入的值大于序列整型值的范圍，將會(huì)整型類型越界的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=# --當(dāng)然，既然是序列類型，那可以插入默認(rèn)值
hrdb=# INSERT INTO tab_serial
hrdb-# VALUES(default,default,default);
INSERT 0 1
通過上述示例，可以知道 smallserial,serial,bigserial相當(dāng)于先創(chuàng)建一個(gè)序列，然后在創(chuàng)建表分別指定不同的整型數(shù)據(jù)類型smallint,integer,bigint。如下示例：
hrdb=# --先創(chuàng)建序列
hrdb=# CREATE SEQUENCE IF NOT EXISTS serial_small
hrdb-# INCREMENT BY 1
hrdb-# START WITH 1
hrdb-# NO CYCLE;
CREATE SEQUENCE
hrdb=# --再創(chuàng)建表
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=# --插入數(shù)據(jù)
hrdb=# INSERT INTO tab_test_serial VALUES(default);
INSERT 0 1
hrdb=# --查詢數(shù)據(jù)
hrdb=# SELECT * FROM tab_test_serial ;
col1 | col2 | col3
------+------+------
1 | 2 | 3
(1 row)
二 貨幣數(shù)據(jù)類型
貨幣類型存儲(chǔ)帶有固定小數(shù)精度的貨幣金額。
關(guān)于貨幣數(shù)據(jù)類型的詳細(xì)信息如下表：
| 名稱 | 存儲(chǔ)容量 | 描述 | 范圍 |
|---|---|---|---|
| money | 8 字節(jié) | 貨幣金額 | -92233720368547758.08 到 +92233720368547758.07 |
示例：
hrdb=# --創(chuàng)建貨幣數(shù)據(jù)類型表
hrdb=# CREATE TABLE IF NOT EXISTS tab_money(amounts money);
CREATE TABLE
hrdb=# --字段注釋
hrdb=# COMMENT ON COLUMN tab_money.amounts IS '金額';
COMMENT
hrdb=# --插入數(shù)值
hrdb=# INSERT INTO tab_money VALUES('20.00');
INSERT 0 1
hrdb=# --查詢數(shù)據(jù)
hrdb=# SELECT * FROM tab_money;
amounts
---------
$20.00
(1 row)
這里需要注意的是，如果插入的貨幣數(shù)據(jù)類型的金額沒有明確指定貨幣表示符號(hào)，那么默認(rèn)輸出本區(qū)域貨幣符號(hào)，如上示例所示的20.00輸出為$20.00。
如果是人民幣，那么如何處理呢？
解決方法有兩種，第一種，使用translate函數(shù)；第二種，修改本地區(qū)域貨幣符號(hào)顯示參數(shù)。
hrdb=# --方法一:直接使用translate函數(shù)將 $ 符號(hào)轉(zhuǎn)換為 ￥ 符號(hào) hrdb=# SELECT translate(amounts::varchar,'$','￥') FROM tab_money; translate ----------- ￥20.00 (1 row) hrdb=# --方法二:修改區(qū)域貨幣符號(hào)顯示參數(shù) hrdb=# --查看本地區(qū)域貨幣符號(hào)顯示參數(shù) hrdb=# show lc_monetary ; lc_monetary ------------- en_US.UTF-8 (1 row) hrdb=# --修改區(qū)域貨幣符號(hào)顯示參數(shù) hrdb=# ALTER SYSTEM SET lc_monetary = 'zh_CN.UTF-8'; ALTER SYSTEM hrdb=# --重新加載動(dòng)態(tài)參數(shù) hrdb=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) hrdb=# --重新查看本地區(qū)域貨幣符號(hào)顯示參數(shù) hrdb=# show lc_monetary; lc_monetary ------------- zh_CN.UTF-8 (1 row) hrdb=# --重新查詢數(shù)據(jù) hrdb=# SELECT * FROM tab_money; amounts --------- ￥20.00 (1 row)
貨幣符號(hào)作為特殊的數(shù)據(jù)類型，需要注意計(jì)算方式，以防止發(fā)生精度丟失的問題。
這種問題解決方式需要將貨幣類型轉(zhuǎn)換為 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=# --貨幣數(shù)據(jù)類型避免精度丟失的解決方法
SELECT amounts::numeric::float8 FROM tab_money;
amounts
---------
20
20.22
溫馨提示：
當(dāng)一個(gè)money類型的值除以另一個(gè)money類型的值時(shí)，結(jié)果是double precision（也就是，一個(gè)純數(shù)字，而不是money類型）；在運(yùn)算過程中貨幣單位相互抵消
三 布爾類型
PostgreSQL提供標(biāo)準(zhǔn)的boolean值，boolean的狀態(tài)為 true或者false和unknown，如果是unknown狀態(tài)表示boolean值為null。
| 名稱 | 描述 | 存儲(chǔ)空間 | 取值 |
|---|---|---|---|
| BOOLEAN | 布爾類型 | 1字節(jié) | true：真 false：假 null：未知（unknown |
示例：
hrdb=# --創(chuàng)建boolean類型表
hrdb=# CREATE TABLE IF NOT EXISTS tab_boolean(col1 boolean,col2 boolean);
CREATE TABLE
hrdb=# --插入布爾類型的狀態(tài)值，狀態(tài)值可以是以下任意一種
hrdb=# INSERT INTO tab_boolean VALUES(TRUE,FALSE);--規(guī)范用法
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類型被廣泛地使用在業(yè)務(wù)環(huán)境中，例如手機(jī)開關(guān)機(jī)，1表示開機(jī)，0表示關(guān)機(jī)或不在服務(wù)區(qū)。手機(jī)APP登錄登出，1表示登錄，0表示登出，微信登陸狀態(tài)，1表示登錄成功，0表示登錄失敗（可能由于網(wǎng)絡(luò)或者密碼錯(cuò)誤導(dǎo)致）等等，此處不再一一舉例。
四 字符類型
SQL定義了兩種主要的字符類型:character varying(n) 和 character(n)。該處的n是一個(gè)正數(shù)。這兩種字符類型都可以存儲(chǔ)n（非字節(jié)）個(gè)長(zhǎng)度的字符串。如果存儲(chǔ)的字符長(zhǎng)度超過了字符類型約束的長(zhǎng)度會(huì)引起錯(cuò)誤，除非多出的字符是空格。
| 名稱 | 描述 | 存儲(chǔ)空間 |
|---|---|---|
| CHAR(n)CHARACTER(n) | 定長(zhǎng)字符串，不足補(bǔ)空格。n是指字符長(zhǎng)度，如不帶精度n，默認(rèn)精度為1。 | 最大為10MB。 |
| VARCHAR(n)CHARACTER VARYING(n) | 變長(zhǎng)字符串。n是指字符長(zhǎng)度。 | 最大為10MB。 |
| TEXT | 變長(zhǎng)字符串。 | 最大為1G-8023B（即1073733621B）。 |
注意，除了每列的大小限制以外，每個(gè)元組的總大小也不可超過1G-8023B（即1073733621B）。
在PostgreSQL中，除了以上的字符數(shù)據(jù)類型外，還有兩種特殊的字符類型如下：
| 名稱 | 描述 | 存儲(chǔ)空間 |
|---|---|---|
| name | 用于對(duì)象名的內(nèi)部類型。 | 64字節(jié) |
| “char” | 單字節(jié)內(nèi)部類型。 | 1字節(jié) |
示例：
hrdb=# --創(chuàng)建字符類型表
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 '表示定長(zhǎng)為15的字符串';
COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col2 IS '表示變長(zhǎng)為15的字符串';
COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col3 IS '表示變長(zhǎng)字符串，為varchar的擴(kuò)展字符串';
COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col4 IS '用于對(duì)象名的內(nèi)部類型';
COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col5 IS '表示單字節(jié)類型';
COMMENT
hrdb=# --插入數(shù)據(jù)
hrdb=# INSERT INTO tab_chartype
hrdb-# VALUES('sungsasong','sungsasong','sungsasong','tab_chartype','s');
INSERT 0 1
hrdb=# --插入包含空格的數(shù)據(jù)
hrdb=# INSERT INTO tab_chartype
hrdb-# VALUES('sungsa song','sung sas ong','sung sa song ','tab_chartype','s');
INSERT 0 1
hrdb=# --計(jì)算不同數(shù)據(jù)類型存儲(chǔ)的字符串的長(zhǎng)度
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
溫馨提示：
在上面示例中，雖然統(tǒng)計(jì)的col1的定長(zhǎng)為15的字符存儲(chǔ)的字符長(zhǎng)度為10個(gè)和11個(gè)，但是實(shí)際上，在存儲(chǔ)中col1列占用的長(zhǎng)度為15個(gè)。并且，在計(jì)算長(zhǎng)度的時(shí)候，空格也被當(dāng)作一個(gè)字符來對(duì)待。
五 二進(jìn)制數(shù)據(jù)類型
在PostgreSQL中，二進(jìn)制數(shù)據(jù)類型有兩種，一種為 bytea hex格式，一種為 bytea escape格式。
| 名稱 | 描述 | 存儲(chǔ)空間 |
|---|---|---|
| BYTEA | 變長(zhǎng)的二進(jìn)制字符串 | 4字節(jié)加上實(shí)際的二進(jìn)制字符串。最大為1G-8203字節(jié)。 |
注意：除了每列的大小限制以外，每個(gè)元組的總大小也不可超過1G-8203字節(jié)。示例
hrdb=# --創(chuàng)建兩種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 格式的二進(jìn)制串';
COMMENT
hrdb=# COMMENT ON COLUMN tab_bytea.col2 IS 'bytea escape 格式的二進(jìn)制串';
COMMENT
hrdb=# --插入數(shù)據(jù),第一個(gè)值代表單引號(hào)，輸出16進(jìn)制的值為\x27,第二個(gè)為轉(zhuǎn)義16進(jìn)制的值f
hrdb=# INSERT INTO tab_bytea
hrdb-# VALUES('\047',E'\xF');
INSERT 0 1
hrdb=# --插入數(shù)據(jù),第一個(gè)值代表反斜杠，輸出16禁止的值為\x5c,第二個(gè)值為轉(zhuǎn)義16進(jìn)制的值fc
hrdb=# INSERT INTO tab_bytea
hrdb-# VALUES('\134',E'\\xFC');
INSERT 0 1
hrdb=# --查看結(jié)果
hrdb=# SELECT * FROM tab_bytea;
col1 | col2
------+------
\x27 | \x0f
\x5c | \xfc
注意：
實(shí)際上bytea多個(gè)十六進(jìn)制值使用E'\xFC' 類似于Oracle中的rawtohex函數(shù)。只不過Oracle中的rawtohex函數(shù)轉(zhuǎn)換后的值為大寫十六進(jìn)制字符串。實(shí)際上如果要在上表中的col2中插入E'\xFG'時(shí)，會(huì)提示G不是一個(gè)有效的十六進(jìn)制字符。
同時(shí)需要注意的是，如果使用E'\xF'只包含單個(gè)十六進(jìn)制字符時(shí)，使用一個(gè)反斜杠，如果有多個(gè)十六進(jìn)制字符，需要兩個(gè)反斜杠，如E'\xFE'。
如下:此處的hextoraw函數(shù)為我自定義實(shí)現(xiàn)的一個(gè)UDF函數(shù)。
六 日期時(shí)間數(shù)據(jù)類型
PostgreSQL支持豐富的日期時(shí)間數(shù)據(jù)類型如下表：
| 名稱 | 描述 | 存儲(chǔ)空間 |
|---|---|---|
| DATE | 日期和時(shí)間 | 4字節(jié)（實(shí)際存儲(chǔ)空間大小為8字節(jié)） |
| TIME [§] [WITHOUT TIME ZONE] | 只用于一日內(nèi)時(shí)間。p表示小數(shù)點(diǎn)后的精度，取值范圍為0-6。 | 8字節(jié) |
| TIME [§] [WITH TIME ZONE] | 只用于一日內(nèi)時(shí)間，帶時(shí)區(qū)。p表示小數(shù)點(diǎn)后的精度，取值范圍為0-6。 | 12字節(jié) |
| TIMESTAMP[§] [WITHOUT TIME ZONE] | 日期和時(shí)間。p表示小數(shù)點(diǎn)后的精度，取值范圍為0-6。 | 8字節(jié) |
| TIMESTAMP[§][WITH TIME ZONE] | 日期和時(shí)間，帶時(shí)區(qū)。TIMESTAMP的別名為TIMESTAMPTZ。p表示小數(shù)點(diǎn)后的精度，取值范圍為0-6。 | 8字節(jié) |
| reltime | 相對(duì)時(shí)間間隔。格式為：X years X mons X days XX:XX:XX。 | 4字節(jié) |
6.1日期輸入
日期和時(shí)間的輸入可以是任何合理的格式，包括ISO-8601格式、SQL-兼容格式、傳統(tǒng)POSTGRES格式或者其它的形式。系統(tǒng)支持按照日、月、年的順序自定義日期輸入。如果把DateStyle參數(shù)設(shè)置為MDY就按照“月-日-年”解析，設(shè)置為DMY就按照“日-月-年”解析，設(shè)置為YMD就按照“年-月-日”解析。
日期的文本輸入需要加單引號(hào)包圍，語法如下：
type [ ( p ) ] 'value'
可選的精度聲明中的p是一個(gè)整數(shù)，表示在秒域中小數(shù)部分的位數(shù)。
示例：
hrdb=> --創(chuàng)建日期輸入表
hrdb=> CREATE TABLE tab_datetype(col1 date);
CREATE TABLE
hrdb=> --字段注釋
hrdb=> COMMENT ON COLUMN tab_datetype.col1 IS '日期類型，默認(rèn)遵循datestyle風(fēng)格(MDY)';
COMMENT
hrdb=> --插入數(shù)據(jù)
hrdb=> INSERT INTO tab_datetype VALUES(date '04-26-2020');
INSERT 0 1
hrdb=> --在MDY風(fēng)格下，也支持YMD的輸入方式，但是不支持DMY或者其它格式的輸入，如下會(huì)報(bào)錯(cuò)
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=> --查看當(dāng)前數(shù)據(jù)庫(kù)的datestyle的格式
hrdb=> show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)
hrdb=> --會(huì)話級(jí)別修改datestyle格式
hrdb=> SET datestyle = 'DMY';
SET
hrdb=> --再次插入 22-04-2020
hrdb=> INSERT INTO tab_datetype VALUES(date '22-04-2020');
INSERT 0 1
hrdb=> --查詢數(shù)據(jù)
hrdb=> SELECT * FROM tab_datetype ;
col1
------------
2020-04-26
2020-04-22
6.2時(shí)間輸入
時(shí)間類型包括 time [ (p) ] without time zone 和time [ (p) ] with time zone。 如果只寫time等效于time without time zone。即不帶時(shí)區(qū)的時(shí)間格式 如果在time without time zone類型的輸入中聲明了時(shí)區(qū)，則會(huì)忽略這個(gè)時(shí)區(qū)。 示例： hrdb=> --不帶時(shí)區(qū)的時(shí)間 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 特殊時(shí)間類型
特殊時(shí)間類型以reltime表示，表示真實(shí)的時(shí)間計(jì)算值，如100將會(huì)使用00:01:40來表示。
示例：
hrdb=> --創(chuàng)建reltime時(shí)間數(shù)據(jù)類型表
hrdb=> CREATE TABLE tab_reltime(col1 varchar,col2 reltime);
CREATE TABLE
hrdb=> --字段注釋
hrdb=> COMMENT ON COLUMN tab_reltime.col1 IS '原始時(shí)間文本時(shí)間';
COMMENT
hrdb=> COMMENT ON COLUMN tab_reltime.col2 IS 'reltime表示的時(shí)間以實(shí)際時(shí)間計(jì)算得到顯示結(jié)果';
COMMENT
hrdb=> --插入數(shù)據(jù)
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=> --查詢數(shù)據(jù)
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
溫馨提示：
對(duì)于 reltime 時(shí)間的輸入，需要使用文本類型的輸入，也就是說使用單引號(hào)引起來。
6.4 其它時(shí)間類型
其它時(shí)間類型包含時(shí)間戳及間隔時(shí)間數(shù)據(jù)類型，示例如下：
示例：
hrdb=> --創(chuàng)建時(shí)間戳和間隔時(shí)間表
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 '帶時(shí)區(qū)的時(shí)間戳';
COMMENT
hrdb=> COMMENT ON COLUMN tab_timestamp_interval.col2 IS '不帶時(shí)區(qū)的時(shí)間戳';
COMMENT
hrdb=> COMMENT ON COLUMN tab_timestamp_interval.col1 IS '間隔時(shí)間類型';
COMMENT
hrdb=> --插入數(shù)據(jù)
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=> --查詢數(shù)據(jù)
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
時(shí)間數(shù)據(jù)類型在業(yè)務(wù)應(yīng)用中使用非常廣泛，如手機(jī)APP登錄時(shí)間，登出時(shí)間，金融業(yè)務(wù)交易時(shí)間等等.
七 網(wǎng)絡(luò)地址類型
PostgreSQL也提供網(wǎng)絡(luò)地址類型，以用于存儲(chǔ)兩大IP家族(IPv4 IPv6地址)地址和MAC地址的數(shù)據(jù)類型。
| 名稱 | 存儲(chǔ)空間 | 描述 |
|---|---|---|
| cidr | 7或19字節(jié) | IPv4或IPv6網(wǎng)絡(luò) |
| inet | 7或19字節(jié) | IPv4或IPv6主機(jī)和網(wǎng)絡(luò) |
| macaddr | 6字節(jié) | MAC地址 |
cidr（無類別域間路由，Classless Inter-Domain Routing）類型，保存一個(gè)IPv4或IPv6網(wǎng)絡(luò)地址。聲明網(wǎng)絡(luò)格式為address/y，address表示IPv4或者IPv6地址，y表示子網(wǎng)掩碼的二進(jìn)制位數(shù)。如果省略y，則掩碼部分使用已有類別的網(wǎng)絡(luò)編號(hào)系統(tǒng)進(jìn)行計(jì)算，但要求輸入的數(shù)據(jù)已經(jīng)包括了確定掩碼所需的所有字節(jié)。
inet類型在一個(gè)數(shù)據(jù)區(qū)域內(nèi)保存主機(jī)的IPv4或IPv6地址，以及一個(gè)可選子網(wǎng)。主機(jī)地址中網(wǎng)絡(luò)地址的位數(shù)表示子網(wǎng)（“子網(wǎng)掩碼”）。如果子網(wǎng)掩碼是32并且地址是IPv4，則這個(gè)值不表示任何子網(wǎng)，只表示一臺(tái)主機(jī)。在IPv6里，地址長(zhǎng)度是128位，因此128位表示唯一的主機(jī)地址。
該類型的輸入格式是address/y，address表示IPv4或者IPv6地址，y是子網(wǎng)掩碼的二進(jìn)制位數(shù)。如果省略/y，則子網(wǎng)掩碼對(duì)IPv4是32，對(duì)IPv6是128，所以該值表示只有一臺(tái)主機(jī)。如果該值表示只有一臺(tái)主機(jī)，/y將不會(huì)顯示。
inet和cidr類型之間的基本區(qū)別是inet接受子網(wǎng)掩碼，而cidr不接受。
macaddr類型存儲(chǔ)MAC地址，也就是以太網(wǎng)卡硬件地址（盡管MAC地址還用于其它用途）。
示例：
hrdb=> --創(chuàng)建IP地址及MAC地址表
hrdb=> CREATE TABLE tab_icm(col1 cidr,col2 inet,col3 macaddr);
CREATE TABLE
hrdb=> --字段注釋
hrdb=> COMMENT ON COLUMN tab_icm.col1 IS '存儲(chǔ)IPv4或IPv6網(wǎng)絡(luò)地址類型';
COMMENT
hrdb=> COMMENT ON COLUMN tab_icm.col2 IS '存儲(chǔ)IPv4或IPv6網(wǎng)絡(luò)地址類型及子網(wǎng)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_icm.col3 IS '存儲(chǔ)設(shè)備MAC地址';
COMMENT
hrdb=> --插入數(shù)據(jù)
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=> --查詢數(shù)據(jù)
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)
八 幾何數(shù)據(jù)類型
PostgreSQL支持集合數(shù)據(jù)類型，用于存儲(chǔ)GIS（地理信息系統(tǒng)）環(huán)境中的幾何數(shù)據(jù)，用于地圖測(cè)繪，城市交通軌跡，地圖圈圖等場(chǎng)景。
PostgreSQL支持以下幾何數(shù)據(jù)類型：
點(diǎn)
線(射線)
線段
矩形
路徑(包含開放路徑【開放路徑類似多邊形】和閉合路徑)
多邊形
圓
對(duì)于以上幾何類型而言，點(diǎn)是其它幾何類型的基礎(chǔ)。
| 名稱 | 存儲(chǔ)空間 | 說明 | 表現(xiàn)形式 |
|---|---|---|---|
| point | 16字節(jié) | 平面中的點(diǎn) | (x,y) |
| lseg | 32字節(jié) | （有限）線段 | ((x1,y1),(x2,y2)) |
| box | 32字節(jié) | 矩形 | ((x1,y1),(x2,y2)) |
| path | 16+16n字節(jié) | 閉合路徑（與多邊形類似） | ((x1,y1),…) |
| path | 16+16n字節(jié) | 開放路徑 | [(x1,y1),…] |
| polygon | 40+16n字節(jié) | 多邊形（與閉合路徑相似） | ((x1,y1),…) |
| circle | 24字節(jié) | 圓 | <(x,y),r> （圓心和半徑） |
對(duì)于所有的幾何數(shù)據(jù)類型，都是使用二維坐標(biāo)上面的橫坐標(biāo)和縱坐標(biāo)來實(shí)現(xiàn)的。計(jì)算也是在二維坐標(biāo)中進(jìn)行的。
示例：
hrdb=> --創(chuàng)建幾何數(shù)據(jù)類型表
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 '二維幾何的基本構(gòu)造點(diǎn)(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=> --創(chuàng)建幾何數(shù)據(jù)類型表
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 '二維幾何的基本構(gòu)造點(diǎn)(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 '多邊形,相當(dāng)于閉合路徑((x1,y1),(x2,y2),(x3,y3),...,(xn,yn)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col7 IS '一組坐標(biāo)點(diǎn)作為圓心和半徑r構(gòu)成<(x,y),r>';
COMMENT
hrdb=> --插入數(shù)據(jù)
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=> --查詢數(shù)據(jù)
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數(shù)據(jù)類型
JSON數(shù)據(jù)類型可以用來存儲(chǔ)JSON（JavaScript Object Notation）數(shù)據(jù)。數(shù)據(jù)可以存儲(chǔ)為text，但是JSON數(shù)據(jù)類型更有利于檢查每個(gè)存儲(chǔ)的數(shù)值是可用的JSON值。
在 PostgreSQL中，JSON數(shù)據(jù)類型有兩種，原生JSON和JSONB。最主要的區(qū)別就是效率不同。JSON 數(shù)據(jù)類型對(duì)于輸入文本進(jìn)行復(fù)制，因此在解析時(shí)需要進(jìn)行轉(zhuǎn)換，輸入速度塊。而JSONB是對(duì)輸入文本進(jìn)行分解并以二進(jìn)制存儲(chǔ)，因此在解析時(shí)不需要進(jìn)行轉(zhuǎn)換，處理速度塊，但是輸入速度相對(duì)會(huì)慢。除此之外，JSONB數(shù)據(jù)類型還支持索引。
示例：
hrdb=> --創(chuàng)建JSON數(shù)據(jù)類型表
hrdb=> CREATE TABLE tab_json(col1 json,col2 jsonb);
CREATE TABLE
hrdb=> --字段注釋
hrdb=> COMMENT ON COLUMN tab_json.col1 IS '存儲(chǔ)json輸入文本';
COMMENT
hrdb=> COMMENT ON COLUMN tab_json.col1 IS '存儲(chǔ)json轉(zhuǎn)換后的二進(jìn)制文本';
COMMENT
hrdb=> --插入數(shù)據(jù)
hrdb=> --插入數(shù)據(jù)
hrdb=> INSERT INTO tab_json
hrdb-> VALUES('{"江蘇省":"南京市","甘肅省":"蘭州市","北京市":"北京市"}',
hrdb(> '{"湖北省":"武漢市","四川省":"成都市","陜西省":"西安市"}');
INSERT 0 1
hrdb=> --給col1創(chuàng)建索引，將會(huì)不被支持。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=> --查詢數(shù)據(jù)
hrdb=> SELECT * FROM tab_json;
col1 | col2
---------------------------------------------------------+--------------------------------------------------------------
{"江蘇省":"南京市","甘肅省":"蘭州市","北京市":"北京市"} | {"四川省": "成都市", "湖北省": "武漢市", "陜西省": "西安市"}
溫馨提示：
使用jsonb類型，可以使用PL/PYTHON映射為Python中表示的字典，列表等。
十 數(shù)組數(shù)據(jù)類型
ostgreSQL支持?jǐn)?shù)組數(shù)據(jù)類型，同時(shí)支持多維數(shù)組。數(shù)組最大的優(yōu)點(diǎn)就是按照數(shù)組下標(biāo)訪問，此時(shí)下標(biāo)相當(dāng)于一個(gè)索引，處理速度快。但是同時(shí)數(shù)組也有劣勢(shì)，比如在刪除或者添加數(shù)組元素需要對(duì)數(shù)組中的元素進(jìn)行向前或者向后移動(dòng)，這樣導(dǎo)致刪除或者添加數(shù)組元組時(shí)比較慢。
示例：
hrdb=> --創(chuàng)建數(shù)組表
hrdb=> CREATE TABLE tab_array(col1 text[],col2 integer[][],col3 integer ARRAY[3]);
CREATE TABLE
hrdb=> --字段注釋
hrdb=> COMMENT ON COLUMN tab_array.col1 IS '文本類型一維數(shù)組';
COMMENT
hrdb=> COMMENT ON COLUMN tab_array.col2 IS '整型類型二維數(shù)組';
COMMENT
hrdb=> COMMENT ON COLUMN tab_array.col3 IS '聲明長(zhǎng)度為3的數(shù)組';
COMMENT
hrdb=> --插入數(shù)據(jù)
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=> --查詢數(shù)據(jù)
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=> --訪問指定列中某個(gè)數(shù)組的元素
hrdb=> SELECT col1[1],col2[3],col3[4] FROM tab_array;
col1 | col2 | col3
--------+------+------
江蘇省 | 3 |
天津市 | 3 | 44
通過上述示例，可以發(fā)現(xiàn)，在PostgreSQL中，雖然聲明了數(shù)組的長(zhǎng)度，但是PostgreSQL對(duì)于數(shù)組的長(zhǎng)度不會(huì)做任何限制。
同時(shí)訪問數(shù)組元素從下標(biāo)1開始，并且在PostgreSQL中并不會(huì)出現(xiàn)數(shù)組越界異常，如果數(shù)組的下標(biāo)訪問超過元素的長(zhǎng)度，那么PostgreSQL便會(huì)返回一行空值。
以上就是常用數(shù)據(jù)類型介紹。但是在PostgreSQL中，除了上述數(shù)據(jù)類型外，還有其它的數(shù)據(jù)類型，比如XML數(shù)據(jù)類型，文本搜索數(shù)據(jù)類型，UUID數(shù)據(jù)類型，復(fù)合數(shù)據(jù)類型，范圍類型，偽類型如any,anyelement,internal等等，在此不做一一介紹。
相關(guān)文章
隨著使用時(shí)間的增加，數(shù)據(jù)庫(kù)中的數(shù)據(jù)量也不斷增加，因此數(shù)據(jù)庫(kù)查詢?cè)絹碓铰?。加速?shù)據(jù)庫(kù)的方法很多，如添加特定的索引，將日志目錄換到單獨(dú)的磁盤分區(qū)，調(diào)整數(shù)據(jù)庫(kù)引擎的參數(shù)等。本文即將介紹的數(shù)據(jù)庫(kù)表分區(qū)即能達(dá)到此效果2021-06-06
postgresql rank() over, dense_rank(), row_number()用法區(qū)別
這篇文章主要介紹了postgresql rank() over, dense_rank(), row_number()的用法區(qū)別，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了postgres之jsonb屬性的使用操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-06-06
postgresql開啟pg_log日志詳細(xì)步驟及參數(shù)說明
pg_log日志要啟動(dòng)保存的話需要去設(shè)置一下相關(guān)的配置文件參數(shù)就好了,下面這篇文章主要給大家介紹了關(guān)于postgresql開啟pg_log日志詳細(xì)步驟及參數(shù)說明的相關(guān)資料,需要的朋友可以參考下2024-02-02
PostgreSQL 數(shù)據(jù)庫(kù)性能提升的幾個(gè)方面
PostgreSQL提供了一些幫助提升性能的功能。主要有一些幾個(gè)方面。2009-09-09
PostgreSQL模式匹配與正則表達(dá)式方法總結(jié)
在postgresql中使用正則表達(dá)式時(shí)需要使用關(guān)鍵字“~”,以表示該關(guān)鍵字之前的內(nèi)容需匹配之后的正則表達(dá)式,這篇文章主要給大家介紹了關(guān)于PostgreSQL模式匹配與正則表達(dá)式的相關(guān)資料,需要的朋友可以參考下2022-11-11
這篇文章主要介紹了pgsql的UUID生成函數(shù)實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL樹形結(jié)構(gòu)的遞歸查詢示例
這篇文章主要給大家介紹了關(guān)于PostgreSQL樹形結(jié)構(gòu)的遞歸查詢的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用PostgreSQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-05-05
PostgreSQL數(shù)據(jù)庫(kù)管理系統(tǒng)快速入門
這篇文章主要介紹了PostgreSQL數(shù)據(jù)庫(kù)快速入門,PostgreSQL是一個(gè)功能強(qiáng)大的開源對(duì)象關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)，他使用和擴(kuò)展了SQL語言，并結(jié)合了許多安全存儲(chǔ)和擴(kuò)展最復(fù)雜數(shù)據(jù)工作負(fù)載的功能,需要的朋友可以參考下2023-07-07
這篇文章主要介紹了解決postgresql無法遠(yuǎn)程訪問的情況，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01