openGauss 常用数据类型-数值数据类型

1. 整数类型

1) smallint 2字节，取值范围：-32768-32767 （大概±3万）

2) integer 4字节，取值范围：-2147483648-2147483647 （大概±2亿）

3) bigint 8字节， 取值范围：-9223372036854775808-9223372036854775807 （大概±9亿亿）

注意：整数类型输入小数时会被转成整数存储（四舍五入）

mydb=# create table test_int (id1 smallint,id2 int, id3 bigint);
CREATE TABLE
mydb=# insert into test_int values(6.6,66.66,666.666);
INSERT 0 1
mydb=# insert into test_int values(6,66,666);
INSERT 0 1
mydb=# insert into test_int values(1.1,11.11,111.111);
INSERT 0 1
mydb=# insert into test_int values(1,11,111);
INSERT 0 1
mydb=# select * from test_int;
 id1 | id2 | id3 
-----+-----+-----
   7 |  67 | 667
   6 |  66 | 666
   1 |  11 | 111
   1 |  11 | 111
(4 rows)

2. 任意精度数据类型

numberic(precision,scale)（缩写number）、decimal(precision,scale)
precision 总位数
scale 小数位数
整数位数= precision-scale
整数位超出会报错，小数位超出会进行四舍五入缩进存储

mydb=# create table test(c1 number(4,2),c2 decimal(4,2));
CREATE TABLE

mydb=# insert into test values(55.55,66.66);
INSERT 0 1
mydb=# insert into test values(55.555,66.666);
INSERT 0 1
mydb=# insert into test values(-55.555,66.666);
INSERT 0 1
mydb=# insert into test values(-155.555,66.666);
ERROR:  numeric field overflow
DETAIL:  A field with precision 4, scale 2 must round to an absolute value less than 10^2.
CONTEXT:  referenced column: c1
mydb=# insert into test values(-99.555,66.666);
INSERT 0 1
mydb=# insert into test values(-99.995,66.666);
ERROR:  numeric field overflow
DETAIL:  A field with precision 4, scale 2 must round to an absolute value less than 10^2.
CONTEXT:  referenced column: c1
mydb=# insert into test values(-99.95,99.666);
INSERT 0 1
mydb=# insert into test values(-99.95,99.966);
INSERT 0 1
mydb=# insert into test values(-99.95,99.996);
ERROR:  numeric field overflow
DETAIL:  A field with precision 4, scale 2 must round to an absolute value less than 10^2.
CONTEXT:  referenced column: c2
mydb=# insert into test values(55.5555555,66.6666666);
INSERT 0 1
mydb=# select * from test;
   c1   |  c2   
--------+-------
  55.55 | 66.66
  55.56 | 66.67
 -55.56 | 66.67
 -99.56 | 66.67
 -99.95 | 99.67
 -99.95 | 99.97
  55.56 | 66.67
(7 rows)

3. 浮点数据类型

1) real 4字节，非精确数值数据类型，需要精准运算的值不应该使用该数据类型；

2) float、double 8字节，非精确数值数据类型，需要精准运算的值不应该使用该数据类型；

3) float§ 指定精度p,p的范围是【1，53】，二进制位表示的最大可接受精度，但测试好像这个p没啥作用。

从这实验可以看到float(p) 指定的p不同，但输出结果都一样
mydb=# select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 (openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

mydb=# create table test (f1 float(1),f2 float(2),f3 float(3));
CREATE TABLE
mydb=# insert into test values(9.8,9.8,9.8);
INSERT 0 1
mydb=# insert into test values(9.87,9.87,9.87);
INSERT 0 1
mydb=# insert into test values(9.876,9.876,9.876);
INSERT 0 1
mydb=# insert into test values(9.8765,9.8765,9.8765);
INSERT 0 1
mydb=# insert into test values(9.87656,9.87656,9.87656);
INSERT 0 1
mydb=# insert into test values(9.876567,9.876567,9.876567);
INSERT 0 1
mydb=# insert into test values(9.8765678,9.8765678,9.8765678);
INSERT 0 1
mydb=# insert into test values(9.87656789,9.87656789,9.87656789);
INSERT 0 1
mydb=# select * from test;
   f1    |   f2    |   f3    
---------+---------+---------
     9.8 |     9.8 |     9.8
    9.87 |    9.87 |    9.87
   9.876 |   9.876 |   9.876
  9.8765 |  9.8765 |  9.8765
 9.87656 | 9.87656 | 9.87656
 9.87657 | 9.87657 | 9.87657
 9.87657 | 9.87657 | 9.87657
 9.87657 | 9.87657 | 9.87657
(8 rows)

4. 序列类型

1) smallserial 2 字节，范围是 1-32767

2) serial 4 字节，范围是 1-2147483647

3) bigserial 8 字节，范围是 1-9223372036854775807

注意： 序列值就是以整数类型存储可自动增长的序列值，生成的序列定义是一样的，差别在于存储该序列值的数据类型
smallserial 对应存储类型为smallint, serial 对应存储类型为int,bigserial 对应存储类型为bigint,

如下提示为序列列创建了隐式序列
mydb=# create table test (s1 smallserial,s2 serial,s3 bigserial,c1 bigint);
NOTICE:  CREATE TABLE will create implicit sequence "test_s1_seq" for serial column "test.s1"
NOTICE:  CREATE TABLE will create implicit sequence "test_s2_seq" for serial column "test.s2"
NOTICE:  CREATE TABLE will create implicit sequence "test_s3_seq" for serial column "test.s3"
CREATE TABLE
三个序列定义是一样的

mydb=# \d test_s1_seq;
         Sequence "public.test_s1_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | test_s1_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
 uuid          | bigint  | 0
Owned by: public.test.s1

mydb=# \d test_s2_seq;
         Sequence "public.test_s2_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | test_s2_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
 uuid          | bigint  | 0
Owned by: public.test.s2

mydb=# \d test_s3_seq;
         Sequence "public.test_s3_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | test_s3_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
 uuid          | bigint  | 0
Owned by: public.test.s3

mydb=# select setval('test_s1_seq',99999,true); #将test_s1_seq值设置为99999，设置成功
 setval 
--------
  99999
(1 row)

mydb=# select * from test_s1_seq;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called | uuid 
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+------
 test_s1_seq   |      99999 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | t         |    0
(1 row)
如下将
mydb=# insert into test (c1) values (1);
ERROR:  smallint out of range
CONTEXT:  referenced column: s1
#值虽然插入失败，但序列还是正常增长的；
mydb=# select * from test_s1_seq;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called | uuid 
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+------
 test_s1_seq   |     100000 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t         |    0
(1 row)

mydb=# insert into test (s1,c1) values (1,1);
INSERT 0 1
mydb=# select * from test;
 s1 | s2 | s3 | c1 
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  1
  1 |  3 |  3 |  1
(3 rows)

5. 货币类型

money 数据类型会根据环境变量lc_monetary 值的不同，显示不同的货币符号。
如下当lc_monetary设置为en_US.utf8时，显示为$符号，当lc_monetary设置为zh_CN.utf8时，显示为￥

openGauss=# create table test (c1 money);
CREATE TABLE
openGauss=# show lc_monetary;
 lc_monetary 
-------------
 en_US.utf8
(1 row)

openGauss=# insert into test values(100);
INSERT 0 1
openGauss=# select * from test;
   c1    
---------
 $100.00
(1 row)

openGauss=# set lc_monetary="zh_CN.utf8";
SET
openGauss=# select * from test;          
    c1    
----------
 ￥100.00
(1 row)