PostgreSQL - 数据类型

在创建表的时候, 指定字段数据类型, 那么有以下多种的类型选择:

数字类型

数字类型列表

数据类型	说明
int2	小范围整数 -32768 ～ +32767
int / int4	整数类型 -2147483648 ～ +2147483647
int8	大范围整数 -9223372036854775808 ～ 9223372036854775807
decimal	用户指定的精度，精确. 小数点前最多为131072个数字; 小数点后最多为16383个数字。
numberic	用户指定的精度，精确. 小数点前最多为131072个数字; 小数点后最多为16383个数字。
serial	自动增加的整数 .1～2147483647

数字函数及运算符

函数类型	说明
+ - * / %	加 , 减 , 乘 , 除 , 取余数
round	四舍五入
ceil	整体进位, 取整
floor	整体舍小数, 取整

• ceil

dong=# select ceil(3.0) , ceil(3.1) , ceil(3.4) , ceil(3.5) , ceil(3.6)  , ceil(3.7) , ceil(3.9)  ;
 ceil | ceil | ceil | ceil | ceil | ceil | ceil 
------+------+------+------+------+------+------
    3 |    4 |    4 |    4 |    4 |    4 |    4
(1 row)

dong=# select ceil(-3.0) , ceil(-3.1) , ceil(-3.4) , ceil(-3.5) , ceil(-3.6)  , ceil(-3.7) , ceil(-3.9)  ;
 ceil | ceil | ceil | ceil | ceil | ceil | ceil 
------+------+------+------+------+------+------
   -3 |   -3 |   -3 |   -3 |   -3 |   -3 |   -3
(1 row)

• floor

dong=# select floor(3.0) , floor(3.1) , floor(3.4) , floor(3.5) , floor(3.6)  , floor(3.7) , floor(3.9)  ;
 floor | floor | floor | floor | floor | floor | floor 
-------+-------+-------+-------+-------+-------+-------
     3 |     3 |     3 |     3 |     3 |     3 |     3
(1 row)



dong=# select floor(-3.0) , floor(-3.1) , floor(-3.4) , floor(-3.5) , floor(-3.6)  , floor(-3.7) , floor(-3.9)  ;
 floor | floor | floor | floor | floor | floor | floor 
-------+-------+-------+-------+-------+-------+-------
    -3 |    -4 |    -4 |    -4 |    -4 |    -4 |    -4
(1 row)

字符类型

字符型

数据类型	说明
char(size)	size是要存储的字符数。固定长度字符串，右边的空格填充到相等大小的字符。
varchar(size)	可变长度字符串。
text	无长度限制

字符函数

测试环境准备

## 创建测试表  
dong=# create table test_char
dong-#  (c1 char(4) ,      ## 定长类型
dong(#   c2 varchar(4));   ## 可变长类型
CREATE TABLE
 
## 插入测试数据
dong=# insert into test_char values ('a' , 'a');
dong=# insert into test_char values ('dong','SQL');

## 查询数据
dong=# select * from test_char;
  c1  | c2 
------+----
 a    | a
 dong | SQL

函数类型	说明
length	字符数
octet_length	实际占用字节数
position	指定字符在字符串的位置
substring	类似substr函数, 截取指定位置字符串
split_part	拆分字符串

• length

dong=# select length(c1) , length(c2) from test_char;
 length | length 
--------+--------
      1 |      1

• octet_length

dong=# select octet_length(c1) , octet_length(c2) from test_char;
 octet_length | octet_length 
--------------+--------------
            4 |            1

• position

dong=# select position('o' in 'postgres') ;
 position 
----------
        2

• substring

dong=# select substring('postgres' from 3 for 4) ;
 substring 
-----------
 stgr
(1 row)

• split_part

dong=# select split_part('xxxxxx@gmail.com' , '@' , 1 );
 split_part 
------------
 xxxxxx


dong=# select split_part('xxxxxx@gmail@.com' , '@' , 2);
 split_part 
------------
 gmail

时间\日期类型

时间\日期的数据类型

dong=# select now();		###### 显示当前日期 & 时间 & 带时区
              now              
-------------------------------
 2020-03-02 18:36:22.027928+08
(1 row)

dong=# select now()::timestamptz(0) ; ###### 显示当前日期 & 时间 & 带时区(小数精度为0) (可不加)
          now           
------------------------
 2020-03-02 18:42:03+08
(1 row)

dong=# select now()::date ; ###### 显示当前日期
    now     
------------
 2020-03-02
(1 row)

dong=# select now()::time;	###### 显示当前时间
       now       
-----------------
 18:36:57.719849
(1 row)


dong=# select now()::timestamp ;  ###### 显示当前日期 & 时间 & 不带时区
            now             
----------------------------
 2020-03-02 18:38:06.249962
(1 row)

dong=# select now()::date , (now()+interval '1 day')::date;
    now     |    date    
------------+------------
 2020-03-02 | 2020-03-03

时间\日期操作及函数

• 日期之间的加减乘除
• 获取当前日期, 时间 current_date 和 current_time

dong=# select now()::date , current_date;
    now     | current_date 
------------+--------------
 2020-03-02 | 2020-03-02
(1 row)

dong=# select now()::time , current_time;
       now       |    current_time    
-----------------+--------------------
 18:46:38.178901 | 18:46:38.178901+08

• 从日期和时间中 , 获取年\月\日\时\分\秒 extract( field FROM source )

field	source
year	now()
month	timestamp
day	time
hour	interval表达式
minute
second
week 当前日期所在年份的第几周
doy 当前日期所在年份的第几周天

dong=# select extract(year from now());
 date_part 
-----------
      2020
(1 row)

布尔型

boolean类型的数据只有真 和 假 两种. (可以是null 空值)

真	假
t	f
true	false
TRUE	FALSE
yes	no
y	n
1	0

网络类型

网络IP数据类型

字符类型	说明
cidr	更为严谨的IPv4 , IPv6 . 会输出子网掩码
inet	不校验子网掩码的IPv4 , IPv6

## 错误示范
dong=# select '192.168.56.11/24'::cidr;
ERROR:  invalid cidr value: "192.168.56.11/24"
LINE 1: select '192.168.56.11/24'::cidr;
               ^
DETAIL:  Value has bits set to right of mask.

## 正确演示
dong=# select '192.168.56.11'::cidr;
       cidr       
------------------
 192.168.56.11/32
(1 row)

网络地址函数

函数	说明
text( )	获取IP和掩码 , 返回文本格式
host( )	获取IP , 返回文本格式
netmast	获取掩码 , 返回文本格式

dong=# select text('192.168.56.11'::cidr) ;
       text       
------------------
 192.168.56.11/32
(1 row)

dong=# select host('192.168.56.11'::cidr) ;
     host      
---------------
 192.168.56.11
(1 row)

dong=# select netmask('192.168.56.11'::cidr) ;
     netmask     
-----------------
 255.255.255.255
(1 row)

数组类型

P56

范围类型

P60

JSON类型

P63

数据类型转换

1 函数转换

这些函数的用法与Oracle的转换函数用法一样, 不做过多的赘述了

函数	说明
to_char()	把时间 , 数字 , 间隔 转换为字符类型
to_date()	把字符串类型转换为时间类型
to_number()	把字符串转换为数字类型
to_timestamp	把字符串转换为时间戳类型

2 通过:: 操作符转换