Postgresql学习笔记之——数据类型之数值类型（整数、小数、浮点数、序列、货币）

一、Postgresql数据库的所有数值类型及其解释：

类型名称	所占空间	描述	取值范围
smallint	2字节	小范围整数，Oracle中使用number代替	-215 ~ 215-1
int或integer	4字节	常用整数，Oracle中也有integer，等效于number(38)，与此不同	-231 ~ 231-1
bigint	8字节	大范围整数，Oracle中没有此类型，也是只有number代替	-263 ~ 263-1
numeric或decimal	变长	用户来声明精度	无限制
real	4字节	变精度，不精确	6位十进制数字精度
double precision	8字节	变精度，不精确	15位十进制数字精度
serial	4字节	自增整数	1 ~ 2的31次方 -1
bigserial	8字节	大范围自增整数	1 ~ 2的63次方-1

二、数值类型的详细解释

1.整数类型

整数类型分三种：smallint、int、bigint。
Postgresql中最常用的是int类型，它提供了在范围，存储空间、性能之间的平衡。一般磁盘紧张会使用smallint，在int范围不足时用bigint类型。
SQL语句中 int、integer和int4是等效的，smallint和int2是等效的，bigint和int8是等效的，也就是说在创建表时，指定字段的类型可以用简写表示，便于编写SQL语句。

2.精确的小数类型

精确的小数类型可以用numeric、numeric(m)、numeric(m,n)表示。
numeric和decimal是等效的。可以存储1000位精度的数字，它的语法是：
numeric(precision， scale)
—— precision必须位正数，规定了小数点前有几位。
—— scale可以是零或者正数，规定了小数点后有几位。
numeric(m)表示了scale是0，与numeric(m,0)的含义相同。
而numeric表示precision和scale没有指定，可以存储任意精度和标度的数值。

postgres=# create table tb_int(id1 numeric(3),id2 numeric(3,0),id3 numeric(3,2),id4 numeric);
CREATE TABLE
postgres=# \d tb_int 
                 Table "public.tb_int"
 Column |     Type     | Collation | Nullable | Default 
--------+--------------+-----------+----------+---------
 id1    | numeric(3,0) |           |          | 
 id2    | numeric(3,0) |           |          | 
 id3    | numeric(3,2) |           |          | 
 id4    | numeric      |           |          | 

postgres=# insert into tb_int values(3.126,3.126,3.126,3.126);
INSERT 0 1
postgres=# select * from tb_int ;
 id1 | id2 | id3  |  id4  
-----+-----+------+-------
   3 |   3 | 3.13 | 3.126
(1 row)
postgres=# insert into tb_int values(3.126,1311.126,3.126,3.126);
2020-02-16 20:33:05.601 CST [27416] ERROR:  numeric field overflow
2020-02-16 20:33:05.601 CST [27416] DETAIL:  A field with precision 3, scale 0 must round to an absolute value less than 10^3.
2020-02-16 20:33:05.601 CST [27416] STATEMENT:  insert into tb_int values(3.126,1311.126,3.126,3.126);
ERROR:  numeric field overflow
DETAIL:  A field with precision 3, scale 0 must round to an absolute value less than 10^3.

PS：声明了精度（precision）没有声明标度（scale），超出精度的会按照四舍五入取值。
同样，声明了标度时，超出标度的会按照四舍五入取值。

3.浮点数类型

数据类型real和double precision 是不精确的、变精度的数字类型。
对于浮点数，需要注意：
（1）如果需要精确计算，应用numeric类型。
（2）两个浮点数类型的值做比较时，结果可能不是所想象的那样运行。
（3）infinity、-infinity、NaN分别表示正无穷大、负无穷大、非数字。

4.序列类型

序列类型中，serial和bigserial与MySQL中的自增字段是同样性质，在Postgresql中是通过序列（sequence）来实现的。
比如：

postgres=# create table tb_serial(id serial);
CREATE TABLE
postgres=# \d+ tb_serial
                                                Table "public.tb_serial"
 Column |  Type   | Collation | Nullable |                Default                | Storage | Stats target | Description 
--------+---------+-----------+----------+---------------------------------------+---------+--------------+-------------
 id     | integer |           | not null | nextval('tb_serial_id_seq'::regclass) | plain   |              | 
Access method: heap

例子中创建了一个表 tb_serial ，它的id列位serial类型，创建后查询表属性，显示id默认的是通过序列赋值，并且可以看到除了表之外还同时创建了一个名字位tb_serial_id_seq的序列。
所以create语句就相当于一下几个语句同时执行：

create sequence tb_serial_id_seq;
create table tb_serial(
	id integer NOT NULL DEFAULT nextval('tb_serial_id_seq')
	);
alter sequence tb_serial_id_seq OWNED BY tb_serial.id;

5.货币类型

货币类型（money type）可以存储固定小数的货币数目，精度准确。查询时输出的格式跟数据库中的参数：lc_monetary的设置有关，不同国家的货币输出的格式时不一样的。例如：

postgres=# select '12.14'::money;
  money  
---------
 ￥12.14
(1 row)

postgres=# select '1214.13'::money;
   money    
------------
 ￥1,214.13
(1 row)

postgres=# show lc_monetary;
 lc_monetary 
-------------
 zh_CN.UTF-8
(1 row)

postgres=# set lc_monetary='en_US.UTF-8';
SET
postgres=# select '12.14'::money;
 money  
--------
 $12.14
(1 row)

postgres=# select '1214.13'::money;
   money   
-----------
 $1,214.13
(1 row)

postgres=# show lc_monetary;
 lc_monetary 
-------------
 en_US.UTF-8
(1 row)

PS:money类型占用了8字节的空间，表示的范围为：-92233720368547758.08 ~
92233720368547758.07

三、数学函数和操作符

数学操作符：

操作符	描述	例子	结果
+	加	4+7	11
-	减	4-7	-3
*	乘	4*7	28
/	除（只保留整数部分，不会四舍五入）	3/2	1
%	模（求余数）	6%4	2
^	次幂（指数运算）	3^3	27
l/	平方根	l/4.0	2
ll/	立方根	ll/8.0	2
！	阶乘	5！	120
!!	前缀方式的阶乘	!!5	120
@	绝对值	@-5.0	5
&	二进制AND	31&15	15
l	二进制OR	31 l 15	31
#	二进制XOR	31 # 15	16
~	二进制NOT	~1	-2
<<	二进制左移	1 << 8	256
>>	二进制右移	16 >> 3	2

数学函数：

函数	描述	例子	结果
abs(x)	绝对值	abs(-23.7)	23.7
cbrt(dp)	立方根	cbrt(8.0)	2
ceil(dp或numeric) 别名：ceiling	不小于参数的最小整数	ceil(-38.8),ceil(38.1),ceiling(38.1)	-38,39,39
degrees(dp)	把弧度转化为角度	degrees(1.0)	57.295779513
exp(dp或numeric)	自然指数	exp(1.0)	2.71828182845905
floor(dp或numeric)	不大于参数的最大整数	floor(-38.8)，floor(38.8)	-38，38
ln(dp或numeric)	自然对数	ln(2.71828)	0.999999
log(dp或numeric)	以10为底的对数	log(1000.0)	3
log(b numeric,x numeric)	以b为底的对数	log(2.0,32.0)	5.00000
mod(y,x)	y/x的余数（模）	mod(7,3)	1
pi()	Π的常量	pi()	3.14159265358979
power(a dp,b dp )	a的b次幂	power(2.0,3.0)	8
power(a numeric,b numeric)	a的b次幂	power(2.0,3.0)	8
radians(dp)	把角度转换为弧度	radians(45.0)	0.785398163397
random()	0.0-1.0之间的随机数	random()	随机返回一个小数
round(dp或numeric)	圆整为最接近的整数（四舍五入）	round(36.5)	37
round(v numeric, s int)	圆整为s位小数（四舍五入）	round(36.5252, 2)	36.53
setseed(dp)	为随后的random()调用设置种子（0到1.0之间）	setseed(0.2)； random()	先设置setseed后，后续的random取值是可以被预见的，后三次取值将会是固定值
sign(dp 或 numeric)	返回参数的符号：-1代表负数；0代表0；1代表正数	sign(-1.5)	-1
sqrt(dp 或 numeric)	平方根	sqrt(9.0)	3
trunc(dp 或 numeric)	截断小数,不四舍五入	trunc(2.3)	2
trunc(v numeric, s int)	截断 s 位小数	trunc(12.356, 3)	12.35

数学函数之三角函数：

函数	描述	例子	结果
acos(x)	反余弦	acos(1) , acos(-1)	0 , 3.14159265358979
asin(x)	反正弦	asin(0) , asin(1)*2	0, 3.14159265358979
atan(x)	反正切	atan(1)
atan2(x,y)	x/y的反正切
cos(x)	余弦
cot(x)	余切
sin(x)	正弦
tan(x)	正切