1. 数字类型

1.1 数字类型：

1.2 数字类型说明：

1. smallint、integer、bigint类型详细说明：
smallint、integer、bigint都是整数类型，例如定义integer类型的表：
create table test_integer (id1 integer,id2 int4);
2. decimal和numeric类型详细说明：
decimal和numeric是等效的，可以存储指定精度的多位数据
比如带小数位的数据，适用于要求计算准确的数值运算。
例如numeric(2,1) 即这个数不超过2位数，且小数点后面最多有一位，多出来的小数会被四舍五入。
create table test (num numeric(2,1));   
insert into test values (2.2);
 num 
-----
 2.2
insert into test values (2.26);
 num 
-----
 2.3
3.  real和double precision是浮点数据类型
   numeric 可以存储最多1000位精度的数字并且准确地进行计算。
   非常适合用于货币金额和其它要求计算准确的数量。
   不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢的多。
4.  smallserial、serial和bigserial类型是自增serial类型，如下创建一张测试表：
postgres=# create table test_serial (id serial,flag text);
CREATE TABLE
postgres=# insert into test_serial (flag) values ('a');
INSERT 0 1
postgres=# insert into test_serial (flag) values ('b');
INSERT 0 1
postgres=# insert into test_serial (flag) values ('c');
INSERT 0 1
postgres=# select * from test_serial;
 id | flag 
----+------
  1 | a
  2 | b
  3 | c
(3 rows)

< http://www.javashuo.com/article/p-zwuoxwat-cq.html>

1.3 数字类型操作符和数学函数

1. 例如计算加、减、乘、除、模取余：
postgres=# select 1+2,2*3,4/2,8%3;
 ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------
        3 |        6 |        2 |        2
2. 按模取余：
postgres=# select mod(8,3);
 mod 
-----
   2
3. 四舍五入：
postgres=# select round(10.2),round(10.9);
 round | round 
-------+-------
    10 |    11
4. 返回大于或等于给出参数的最小整数：
postgres=# select ceil(3.6),ceil(-3.6);
 ceil | ceil 
------+------
    4 |   -3
(1 row)
5. 返回小于或等于给出参数的最大整数：
postgres=# select floor(3.6),floor(-3.6);
 floor | floor 
-------+-------
     3 |    -4
(1 row)

2.字符类型

2.1 字符类型：

2.2 字符类型说明

1. 创建一张测试表，并插入一条测试数据：
create table test_char(col1 varchar(4),col2 character(4));
insert into test_char(col1,col2) values('a','a');
2. 查看两个字段值的字符串长度：
postgres=# select char_length(col1),char_length(col2) from test_char;
 char_length | char_length 
-------------+-------------
           1 |           1
3. 查看两个字段实际占用的物理空间大小：
postgres=# select octet_length(col1),octet_length(col2) from test_char;
 octet_length | octet_length 
--------------+--------------
            1 |            4
(1 row)

2.3 字符类型函数

① 计算字符串占用的字符数：
postgres=# select char_length('abcd');
 char_length 
-------------
           4
② 计算字符串占用的字节数：
postgres=# select octet_length('abcd');
 octet_length 
--------------
            4
③ 指定字符在字符串的位置：
postgres=# select position('a' in 'abcd');
 position 
----------
        1
④ 提取字符串中的子串：
postgres=# select substring('abcdedfdfdd' from 4 for 4);
 substring 
-----------
 dedf
⑤ 拆分字符串，split_part函数：
# 以@为分隔符，提取第二列：
postgres=# select split_part('abc@def@ghr','@',2);
 split_part 
------------
 def

3.时间/日期类型

3.1 时间类型：

3.1.1 时间类型转换

① 系统自带的now()函数，默认返回的类型为timestamp [ (p) ] with time zone类型，如下所示：
postgres=# select now();
              now              
-------------------------------
 2022-02-21 12:55:44.192936+08
② 将timestimep[ (p) ] with time zone类型转换为timestimep without time zone类型：
postgres=# select now()::timestamp without time zone;
            now             
----------------------------
 2022-02-22 12:30:30.851688
③ 转换成date类型：
postgres=# select now()::date;
    now     
------------
 2022-02-22
④ 转换成time without time zone类型：
postgres=# select now()::time without time zone;
       now       
-----------------
 12:31:44.490888
⑤ 转换成time with time zone类型：
postgres=# select now()::time with time zone;
        now         
--------------------
 12:32:12.679392+08

3.2 时间/日期类型操作符

① 日期相加：
postgres=# select date '2017-07-29' + interval'1 days';
      ?column?       
---------------------
 2017-07-30 00:00:00
② 日期相减：
postgres=# select date '2017-07-29' - interval'1 days';
      ?column?       
---------------------
 2017-07-28 00:00:00
③ 日期相乘：（例如100秒乘1秒）
postgres=# select 100* interval '1 second';
 ?column? 
----------
 00:01:40
④ 日期相除：（例如将1小时除3）
postgres=# select interval '1 hour' / double precision '3';
 ?column? 
----------
 00:20:00

3.3 时间、日期类型常用函数：

① 显示当前时间：
postgres=# select current_date,current_time;
 current_date |    current_time  
--------------+--------------------
 2021-06-02   | 19:14:25.254973+08
② extract函数，可以从日期、时间、数据类型中抽取年、月、日、时、分、秒信息：
# 语法：
extract (field from source)
# field值可以为century、year、month、day、hour、minute、second等，source类型为timestamp、time、interval的值表达式，例如取年份，代码如下：
postgres=# select extract(year from now());
 date_part 
-----------
      2021
③ 对于timestamp类型，取月份和月份里的第几天：
postgres=# select extract(month from now()),extract(day from now());
 date_part | date_part 
-----------+-----------
         6 |         2
④ 取小时，分钟：
postgres=# select extract(hour from now()),extract(minute from now());
 date_part | date_part 
-----------+-----------
        19 |        31
⑤ 取秒：
postgres=# select extract(second from now());
 date_part 
-----------
 25.230762
⑥ 取当前日期所在年份中的第几周：
postgres=# select extract(week from now());
 date_part 
-----------
        22
⑦ 当天属于当前年份的第几天：
postgres=# select extract(doy from now());
 date_part 
-----------
       153

4.布尔类型

4.1 布尔类型：

4.2 布尔类型说明：

①    true状态的有效值可以是TRUE、t、true、y、yes、on、1；false状态的有效值为FALSE、f、fase、n、no、off、0：
# 创建表并插入数据：
create table test_boolean(cola boolean,colb boolean);
insert into test_boolean (cola,colb) values ('true','false');
insert into test_boolean (cola,colb) values ('t','f')
insert into test_boolean (cola,colb) values ('TRUE','FALSE');
insert into test_boolean (cola,colb) values ('yes','no');
insert into test_boolean (cola,colb) values ('y','n');
insert into test_boolean (cola,colb) values ('1','0');
insert into test_boolean (cola,colb) values (null,null);
# 以上插入的是TRUE、t、true、y、yes、on、1和FALSE、f、fase、n、no、off、0，但最终显示结果都会以t或f进行显示，而插入的null值，则为空：

删除库下所有表：

DO $$ DECLARE r RECORD; BEGIN — if the schema you operate on is not”current”, you will want to — replace current_schema() in query with ‘schematodeletetablesfrom’ — and update the generate ‘DROP…’ accordingly. FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP EXECUTE ‘DROP TABLE IF EXISTS ‘ || quote_ident(r.tablename) || ‘ CASCADE’; END LOOP; END $$;

5.网络地址类型

5.1 网络地址类型：

5.2 网络地址演示说明：

①    说明：
inet和cidr类型存储的网络地址格式为address/y，其中address表示IPv4或IPv6网络地址，y表示网络掩码位数，如果y省略，IPv4掩码为32，IPv6掩码为128。inet和cidr类型都会对数据合法性进行检查，如果数据不合法则报错：

②    inet和cidr类型的差别：

cidr类型的输出默认带子网掩码，而inet不一定：

cidr类型对IP地址和子网掩码合法性进行检查而inet不会：

5.3 网络地址操作符：

5.4 网络地址操作符

①    取IP地址，返回文本格式：
select host(cidr '192.168.1.0/24');
②    取IP地址和网络掩码，返回文本格式：
select text(cidr '192.168.1.0/24');
③    取网络地址子网掩码，返回文本格式：
select netmask(cidr '192.168.1.0/24');

6.范围类型

6.1 范围类型：

6.2 范围类型说明：

①    用户可以通过create type自定义范围类型，integer范围类型举例：
②    daet范围类型举例：

6.3 范围类型操作符：

①    以下所包含的是否都大于3，如果是则显示f：
②    以下所包含，左边的是否大于右边的，如果不是则显示f：
③    等于操作符：
④    计算交叉：
⑤    范围是否为空：
⑥    提取上边界：
⑦    提取下边界：

6.4 给范围类型创建索引：

①    范围类型数据支持创建GIST索引，GIST索引支持的操作符有“=” “&&” “<@” “@>” “<<” “-|-” “&>” “&<”等：
create index idx_ip_address_range on ip_address using gist (ip_range);

6.5 演示说明：

①    创建一个json类型的表，并插入数据：
create table test_json1 (id serial primary key,name json);
insert into test_json1 (name) values ('{"col1":1,"col2":"francs","col3":"male"}');
insert into test_json1 (name) values ('{"col1":2,"col2":"fp","col3":"male"}');
②    查看表test_json1中的数据：
③    查看json数据的键值：
select name -> 'col2' from test_json1 where id=1;
④    以文本的形式显示json数据的键值：
select name ->> 'col2' from test_json1 where id = 1;

7.jsonb/json类型

7.1 jsonb/json差异

①    jsonb的输入和输出键值的顺序不一样，而json则完全一样：
select '{"bar":"baz","balance":7.77,"active":false}'::jsonb;
select '{"bar":"baz","balance":7.77,"active":false}'::json;
②    jsonb类型会去掉数据中键值的空格：
select '{"id":1, "name":"francs"}'::jsonb;
③    jsonb会删除重复的键：
select '{"id":1,"name":"francs","name":"test"}'::jsonb;

7.2 jsonb/json操作符

①    以文本格式显示json类型的字段键值可以使用“->>”操作符：
select name ->> 'col2' from test_json1 where id=1;
②    字符串是否作为顶层键值：
select '{"a":1,"b":2}'::jsonb ? 'a';
③    删除json数据的键值：
select '{"a":1,"b":2}'::jsonb - 'a';

7.3 jsonb/json函数

①    扩展最外层的json对象成为一组键/值结果集：
select * from json_each('{"a":"foo","b":"bar"}');
②    以文本形式返回结果：
select * from json_each_text('{"a":"foo","b":"bar"}');
③    row_to_json()函数，例如将一个普通表转换为json格式：
create table test_copy (id serial not null,name varchar(30));
insert into test_copy (name) values ('foo');
select row_to_json(test_copy) from test_copy;
④    返回最外层的json对象中的键的集合：
select * from json_object_keys('{"a":"foo","b":"bar"}');

7.4 jsonb键/值的追加、删除、更新：

①    jsonb键/值追加可通过“||”操作符，例如增减sex键：
select '{"name":"francs","age":"31"}'::jsonb || '{"sex":"male"}'::jsonb;
②    删除键/值：
# 方法一，通过“-”删除：
select '{"name":"james","emai1":"james@localhost"}'::jsonb - 'emai1';
select '["red","green","blue"]'::jsonb -0;
# 方法二，通过“#-”删除指定键/值，通常用于有嵌套json数据删除场景：
# 如删除嵌套contact中的fax值：
select '{"name":"James","contact":{"phone":"01234 567890","fax":"01987 543210"}}'::jsonb #- '{contact,fax}'::text[];
# 删除嵌套aliases中的位置为1的键/值：
select '{"name":"James","aliases":["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];
③    键/值更新：
# 方法一，使用“||”操作符，如更改age列：
select '{"name":"francs","age":"31"}'::jsonb || '{"age":"32"}'::jsonb;
# 方法二，使用jsonb_set函数：
# 如更新age列：
select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);
# 如增加set列：
select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{set}','"male"'::jsonb,true);

8.数据类型转换

8.1 通过CAST函数进行转换

①    将varchar字符类型转换成text类型：
select cast(varchar'123' as text);
②    将varchar字符类型转换成int4类型：
select cast(varchar'123' as int4);

8.2 通过 :: 操作符经行转换

①    转换为int4和numeric类型：
select 1::int4,3/2::numeric;
②    通过SQL查询某个表的字段名称：
# 方法一，在使用类型转换之前需要两步完成：
# 首先获取指定表的OID：
select oid,relname from pg_class where relname = 'test_json1';
# 在通过OID查询某个表的字段：
select attname from pg_attribute where attrelid='33036' and attnum > 0;
# 方法二，使用类型转换后可一步完成：
select attname,attrelid,attnum from pg_attribute where attrelid='test_json1'::regclass and attnum >0;