postgresql數據庫——數據類型總結

postgresql數據庫的 數據類型 

postgresql支持多種數據類型，主要有：整數類型、浮點數類型、任意精度數值、日期時間類型、字符串類型、二進制類型、布爾類型和數組類型等。

1、整數類型

   postgresql提供了多種數值數據類型，不同的數據類型提供不同的取值范圍，可以存儲的值范圍越大，所需要的存儲空間也會越大。

  postgresql提供的整數類型有：mallint、int、bigint。

《類型名稱》  《說明》     《存儲需求》   《取值范圍》

smallint   小范圍的整數       2字節        -32768----32767

int        普通大小的整數     4字節        -2147483648----2147483647

bigint     大整數            8字節        -9223372036854775808----9223372036854775807

2、浮點數類型：

postgresql中使用浮點數來表示小數。浮點類型有兩種：real和double precision，如下所示：

《類型名稱》            《說明》         《存儲需求》  

real                 6位十進制數字精度          4字節

double precision     15位十進制數字精度         8字節

3、任意精度類型

  postgresql中使用numeric表示任意精度的類型是數值，使用numeric(m,n)來表示，其中m稱為精度，表示總共的位數；n稱為標度，是表示小數的位數。例如：563.129的精度為9，標度為3

注意：numeric的有效取值范圍由M和D的值決定。如果改變M而固定D，那么其取值范圍將隨M的變大而變大。另外，如果用戶指定的精度超出精度外，就會四舍五入進行處理。如下：

testdb=# create table emp (t numeric(5,1),y numeric(5,3));  

testdb=# insert into emp values (9.12,9.156);

testdb=# select * from emp;

  t  |   y   

-----+-------

 9.1 | 9.156

4、日期與時間類型：

   postgresql中有多種表示日期的數據類型，主要有：time、date、timestamp和interval。每一個類型都有合法的取值范圍，當指定確實不合法的值時系統將“零”值插入數據庫中。如下類型介紹：

《類型名稱》     《含義》            《日期范圍》         《存儲需求》      《存儲格式》

time        只用于一日內的時間   00:00:00---24:00:00        8字節           HH:MM:SS     

date        只用于日期          4713BC---58784897AD         4字節          YYYY-MM-DD   

timestamp   日期和時間          4713BC---58784897AD         8字節       YYYY-MM-DD HH:MM:SS 

注意：time和timestamp類型，默認情況下為without time zone（不帶時區）；如果需要，可以設置為帶時區（with time zone ）

4.1、time：類型使用

time類型用在時間上，在存儲時需要8字節，格式為：HH:MM:SS。HH表示小時；MM表示分鐘；SS表示秒。

例子：

testdb=# create table test (t time);

testdb=# insert into test values ('11:10:25 '),('23:45');   ---注意，第一個時間值最后有一個空格，

testdb=# select * from test;

    t     

----------

 11:10:25

 23:45:00

(2 rows)

從上面的結果可以看出，兩個時間都正確插入，而且格式都為：HH:MM:SS

testdb=# insert into test values (current_time),(now());    ---插入系統當前時間

INSERT 0 2

testdb=# select * from test;

        t        

-----------------

 11:10:25

 23:45:00

 04:56:57.034863

 04:56:57.034863

(4 rows)

從上面可以看出，會顯示時區；

4.2、date：類型

   date類型用在日期值時，沒有時間部分，存儲需要4字節。日期格式為：'YYYY-MM-DD'，其中YYYY表示年；MM表示月；DD表示日；。在給date類型的字段賦值時，可以使用字符串類型或者數字類型的數據插入，只要符合date的日期格式即可。

①：以“YYYY-MM-DD”或者“YYYYMMDD”字符串格式表示的日期。例如，輸入‘2018-12-31’或者‘20181231’，插入數據庫的日期都為2018-12-31.

②：以“YY-MM-DD”或者“YYMMDD”字符串格式表示的日期，在這里YY表示兩位的年值。postgresql使用以下規則解釋兩位年值：“00-69”轉換年值為‘2000--2069’；‘70-99’轉換年值為‘1970-1999’；

③：利用current_date或者now()插入當前系統日期。

例子：

testdb=# create table emp (d date);

testdb=# insert into emp values ('1998-08-08'),('19980808'),('20180808');

testdb=# insert into emp values (now());  ---插入系統當前值

testdb=# select * from emp;

     d      

------------

 1998-08-08

 1998-08-08

 2018-08-08

 2018-06-23

(4 rows)

注意：now()函數返回日期和時間值，在保存到數據庫時，只保留了日期部分。

4.3、timestamp：類型

timestamp的日期格式為YYYY-MM-DD HH:MM:SS。在存儲時需要8字節。如：

testdb=# create table emp (ts timestamp);

testdb=# insert into emp values ('2018-08-10 11:00:02'),(now());

testdb=# select * from emp;

            ts             

---------------------------

 2018-08-10 11:00:02

 2018-06-23 05:14:17.52538

(2 rows)

4.4、創建帶時區的日期和時間類型

testdb=# create table emp (t time with time zone);

CREATE TABLE

testdb=# insert into emp values ('12:10:05 PST'),('12:10:10');

INSERT 0 2

testdb=# select * from emp;

      t      

-------------

 12:10:05-08

 12:10:10-04

(2 rows)

其中，PST為西8區，如果不指定時區，默認是東8區。

5、字符串類型：

   字符串類型用來存儲字符串數據，除了可以存儲字符串數據之外，還可以存儲其他數據，比如圖片和聲音的二進制數據。除了可以進行區分或者不區分大小寫的字符串比較外，還可以進行模式匹配查找。postgresql的字符串類型包括：char、varchar和text。

《類型名稱》                      《說明》

char(n)              固定長度非二進制字符串，不足補空白

varchar(n)           變長非二進制字符串，有長度限制

text                 變長非二進制字符串，無長度限制

注意：

   char(n) varchar(n)，其中n是一個正整數。表示可以存儲n個字符的字符串。如果要存儲的字符串比聲明的長度短，類型為char的數值將會用空白填滿；而類型為varchar的數值將只存儲短些的字符串。

   char類型中填充的空白是無意義的。例如，比較兩個char值的時候填充的空白都會被忽略，在轉換成其他字符串類型的時候，char值里面的空白會被刪除，注意在varchar和text數值里，結尾的空白是有意思的。

例子：

testdb=# create table emp (v1 char(5),v2 varchar(10));

CREATE TABLE

testdb=# insert into emp values ('ab','ab '),('abc','abc'),('ab   ','ab   ');

INSERT 0 3

testdb=# select * from emp;

  v1   |  v2   

-------+-------

 ab    | ab 

 abc   | abc

 ab    | ab   

(3 rows)

testdb=# select concat('(',v1, ')'),concat('(',v2, ')') from emp;

 concat  | concat  

---------+---------

 (ab   ) | (ab )

 (abc  ) | (abc)

 (ab   ) | (ab   )

(3 rows)

--text類型：

  postgresql提供text類型，它可以存儲任何長度的字符串。雖然text類型不是sql標準，但是許多其他sql數據庫系統也有。

例子：

testdb=# create table emp (t1 text);

testdb=# insert into emp values ('ab'),('agcd'),('ab   ');

testdb=# select * from emp;

  t1   

-------

 ab

 agcd

 ab   

(3 rows)

testdb=# select concat('(',t1, ')') from emp;

 concat  

---------

 (ab)

 (agcd)

 (ab   )

(3 rows)

6、二進制類型

   postgresql支持兩類字符型數據：文本字符串和二進制字符串，postgresql提供了bytea類型，用于存儲二進制字符串。bytea類型存儲空間為4字節加上實際的二進制字符串。

testdb=# create table emp (b bytea);                               ^

testdb=# insert into emp values (E'\\000');

INSERT 0 1

testdb=# select * from emp;

  b   

------

 \x00

(1 row)

7、布爾類型

   postgresql提供了Boolean布爾數據類型。Boolean用1字節來存儲，提供了true(真)，false(假)兩個值。另外，用戶可以使用其他有效文本值替代true和false。替代true的文本值為‘t’‘true’‘y’,'1'‘yes’；替代false的文本值為‘f’‘n’,'0'

testdb=# create table emp (b boolean);

CREATE TABLE

testdb=# insert into emp values (true),(false),('y'),('no'),('0');

INSERT 0 5

testdb=# select * from emp;

 b 

---

 t

 f

 t

 f

 f

(5 rows)

8、數組類型

postgresql允許將字段定義成定長或變長的一維或多維數組。數組類型可以是任何基本類型或用戶定義類型。