postgresql数据类型

select * from t_user;


--类型输入与转换
select 1,1.343, 'hello world';
--类型名+单引号
select  int'1' + int'2';
--cast方式
select cast(5 as int),cast('2014--7-17' as date);
--双冒号的方式
select '5'::int,'2017-09-22'::date;

---布尔类型
boolean t: TRUE,tRue,'tRuE','true','t','y','yes','1'
boolean f:FALSE,fAlse,'fALsE','false','f','n','no','0'

----数值类型
--整型
smallint 2个字节， -2的15次方 到 2 的 15次方
int 4个字节，-2的31次方 到 2 的 31次方
bigint 8个字节，-2的63次方 到 2 的63次方

--精确的小数类型
numeric；和decimal等效；
numeric(precision,scale)  ,等价于decimal(m,n)
精度recision必须为正，标度scale可以为0或正。

------------------------------------------------
create table t_number(id1 numeric(3),id2 numeric(3,0),id3 numeric(3,2),id4 numeric);
insert into t_number values(3.1,3.4,3.135,3.135);
select * from t_number;
--3;3;3.14;3.135
insert into t_number values(3.1,3.4,13.135,3.135);
ERROR:  numeric field overflow
DETAIL:  A field with precision 3, scale 2 must round to an absolute value less than 10^1.
********** 错误 **********
ERROR: numeric field overflow
SQL 状态: 22003
详细:A field with precision 3, scale 2 must round to an absolute value less than 10^1.
---------------------------------------------------
当字段声明了标度，超过小数点位数的标度会四舍五入，而没有声明精度也没有声明标度的，会原样存储；
对于声明了精度的数值，如果insert的数值大于声明的精度范围，会报错。

--浮点类型
real和double precision是不精确的、变精度的类型，注意如下：
要求精度的，要用numeric类型；
Infinity 正无穷大
-Infinity 负无穷大
NaN 不是一个数字

--序列类型
serial和bigserial与 mysql 中的自增字段一个意思。
postgresql与oracle一样有序列，mysql没有序列。
create table t(id serial);
等价于：
create sequence t_id_seq;
create table t(id integer not null default nextval('t_id_seq'));
alter sequence t_id_seq owned by t.id;

----字符串类型
archar(n)
text
字符串函数

--二进制数据类
bytea 
二进制数据类型转义表示
二进制数据类型函数

select E'\\001'::bytea;

--位串类型
位串就是一串1和0的字符串。可直观的操作二进制位。ql位类型：
bit(n); 必须匹配精确长度n，否则报错；
bit varying(n); 数据最长n的变长类型，超过了会报错；


--日期时间类型
timestamp  8字节  日期和时间
timestamp with time zone 8字节  日期和时间，带时区
date 4字节 只用于日期
time 8字节 只用于一日之内的时间
time with time zone 12字节 只用于一日之内的时间，带时区

--日期输入
tpye 'value'

testdb2=# create table date_test(col1 date);
CREATE TABLE
testdb2=# insert into date_test values(date '2017-10-2');;
INSERT 0 1
testdb2=# select * from date_test;
    col1
------------
 2017-10-02
(1 row)
日期加减天数计算
cast(cast(b.f_date as text) as date )+ 365
两个日期计算
select date'20170302' - date'20170228'
日期转换
select to_char(current_date,'yyyymmdd');--date 转 text
select to_char(current_date - 1,'yyyymmdd');--date 转 text
select cast(to_char(current_date - 1,'yyyymmdd') as int);--date转ext,再转integer


time '07:00' - time '04:00';
--时间输入
time被默认为time without time zone的类型，这样即使字符串中有时区，也被忽略。
testdb2=# select time '04:05:06 PST';
   time
----------
 04:05:06
(1 row)
testdb2=# select time with time zone '04:05:06 PST';
   timetz
-------------
 04:05:06-08
(1 row)
testdb2=#

--特殊值
epoch
testdb2=# select date'epoch';
    date
------------
 1970-01-01
 now 当前事务开始时间
 today 今日午夜
 tomorrow 明日午夜
 yesterday 昨日午夜
 allballs 适用time类型  00:00:00.00 UTC

testdb2=# select current_time;
       timetz
--------------------
 02:23:10.071294+08
(1 row)

--枚举类型
select * from pg_enum;

--几何类型
point (x,y)
line ((x1,y1),(x2,y2))
lseg ((x1,y1),(x2,y2))
box ((x1,y1),(x2,y2))
circle <(x,y),r>
path 闭合路径（与多边形相似） ((x1,y1),...)
path 开放路径 [(x1,y1),...]

类型名称 '表现形式'
'表现形式'::类型名称

select '1,1'::point;
select '(1,1)'::point;
select lseg '1,1,2,2';
select lseg '(1,1),(2,2)';
select lseg '(1,1),(2,2)';

testdb2=# select '1,1'::point;
 point
-------
 (1,1)
(1 row)

testdb2=# select '(1,1)'::point;
 point
-------
 (1,1)
(1 row)
testdb2=# select lseg '1,1,2,2';
     lseg
---------------
 [(1,1),(2,2)]
(1 row)
testdb2=# select lseg '(1,1),(2,2)';
     lseg
---------------
 [(1,1),(2,2)]
(1 row)
testdb2=# select lseg '(1,1),(2,2)';
     lseg
---------------
 [(1,1),(2,2)]
(1 row)

testdb2=#
--几何类型操作符


--网络地址类型
cidr
inet
macaddr

--复合类型
类似C的结构体，结构体中再定义几种属性
--XML类型
用于存储xml数据
testdb2=# select xml '<osdba>hello world</osdba>';
            xml
----------------------------
 <osdba>hello world</osdba>
(1 row)
testdb2=#

---JSON类型
存储json类型的数据
要求数据库编码utf-8
json类型是把输入的数据原封不动地存入数据库中，使用时需要重新解析数据；json不支持建索引
jsonb类型是在存放时就把json数据解析成二进制格式，使用时不需要再次解析，性能会更高。jsonb支持建索引

当把一个Json字符串转换成Jsonb类型时，json字符串内的数据类型实际被转换成了postgresql数据库中的类型：
string -> text 注意字符集的限制
number -> numeric  
boolean -> boolean
null -> (none)

testdb2=# select '9'::json,'"osdba:"'::json,'true'::json,'null'::json;
 json |   json   | json | json
------+----------+------+------
 9    | "osdba:" | true | null
(1 row)

testdb2=# select json'"osdba"',json'9',json'true',json'null';
  json   | json | json | json
---------+------+------+------
 "osdba" | 9    | true | null
(1 row)
testdb2=#  select jsonb'"osdba"',jsonb'9',jsonb'true',jsonb'null';
  jsonb  | jsonb | jsonb | jsonb
---------+-------+-------+-------
 "osdba" | 9     | true  | null
(1 row)
testdb2=# select '[9,true,"osdba",null]'::json,'[9,true,"osdba",null]'::jsonb;
         json          |          jsonb
-----------------------+--------------------------
 [9,true,"osdba",null] | [9, true, "osdba", null]
(1 row)


testdb2=# select json'{"name":"osdba","age":40,"sex":true,"money":232.32}';
                        json
-----------------------------------------------------
 {"name":"osdba","age":40,"sex":true,"money":232.32}
(1 row)


--json类型操作的操作符
json和jsonb的操作符


操作符 右操作数类型 描述 示例 结果
-> int 获取JSON数组元素（索引从0开始） select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2; {"c":"baz"}
-> text 通过键获取值 select '{"a": {"b":"foo"}}'::json->'a'; {"b":"foo"}
->> int
获取JSON数组元素为 text


select '[1,2,3]'::json->>2; 3
->> text 通过键获取值为text select '{"a":1,"b":2}'::json->>'b'; 2
#> text[]
在指定的路径获取JSON对象


select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'; {"c": "foo"}
#>> text[]
在指定的路径获取JSON对象为 text


select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'; 3
 

jsonb额外操作符


操作符 右操作数类型 描述 示例 结果
@> jsonb 左侧json最上层的值是否包含右边json对象
select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb;

select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;

f

t

<@ jsonb 左侧json对象是否包含于右侧json最上层的值内 select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb; t
? text text是否作为左侧Json对象最上层的键 select '{"a":1, "b":2}'::jsonb ? 'b'; t
?| text[] text[]中的任一元素是否作为左侧Json对象最上层的键 select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']; t
?& text[] text[]中的所有元素是否作为左侧Json对象最上层的键 select '["a", "b"]'::jsonb ?& array['a', 'b']; t
|| jsonb 连接两个json对象，组成一个新的json对象 select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; ["a", "b", "c", "d"]
- text 删除左侧json对象中键为text的键值对 select '{"a": "b"}'::jsonb - 'a'; {}
- integer
删除数组指定索引处的元素，如果索引值为负数，则从右边计算索引值。


如果最上层容器内不是数组，则抛出错误。


select '["a", "b"]'::jsonb - 1; ["a"]
#- text[]
删除指定路径下的域或元素（如果是json数组，且整数值是负的，

则索引值从右边算起）

select '["a", {"b":1}]'::jsonb #- '{1,b}'; ["a", {}]

--json类型的函数

--json类型的索引
json类型的列上无法直接建索引，但可在json类型的列上建函数索引；
jsonb类型的列上可以直接建索引，一般考虑建GIN索引，而不是BTree索引。因为GIN索引可以高效的从JSONB内部的KEY/VALUE对搜索数据，而BTree索引只是比较整个JSONB大小的方式。
比较原则如下：
Object > Array > Boolean > Number -> String -> Null
n个k/v对的Object > n-1个k/v对的Object
n个元素的array > n-1个元素的array
object内部多个比较顺序：key-1,value-1,key-2,value-2,。。。
键值直接的比较是按存储顺序进行的：
{"aa":1,"a1":1} > {"b":1,"b1":1}

在JSONB上建GIN索引的两种方式：
1.使用默认的jsonb_ops操作符创建
create index idx_name on table_name USING gin (index_col);
jsonb数据中每个key和value都是是作为一个单独的索引项的；

2.使用jsonb_path_ops操作符创建
create index idex_name on table_name USING gin (index_col jsonb_path_ops);

为每个value创建了一个索引项；

----数组类型
声明数组
create table  testtab04(id int, col int[], col2 int[10], col3 text[][]);
在定义数组类型中填写的数字是没有意义的，不会限制数组的长度；定义时指定数组的维度也没有意义，数组的维度是根据实际插入的数据来确定的。
输入数组值


create table testtab05(id int , col1 int[]);


insert into testtab05 values(1,'{1,2,3}');
insert into testtab05 values(2,'{4,5,6}');
insert into testtab05 values(3,'{7,8,9}');




testdb2=# select * from testtab05;
 id |  col1   
----+---------
  1 | {1,2,3}
  2 | {4,5,6}
  3 | {7,8,9}
(3 rows)


create table testtab6(id int, col1 text[]);


insert into testtab6 values(1,'{how,who,where}');
insert into testtab6 values(2,'{this,you,here}');
--有逗号，可以使用双引号
insert into testtab6 values(3,'{"how,,hh","who","where,why"}');
--如果字符串中有单引号，可使用两个单引号表示一个单引号
insert into testtab6 values(4,'{"who''s bread","It''s ok."}');
--如果字符串中有 { 和 }，放到双引号中即可
insert into testtab6 values(5,'{"{this","you}haha","here"}');
--如果字符串中有 双引号，需在双引号前加反斜杠
insert into testtab6 values(6,'{this,\"you,here}');

select * from testtab6;
testdb2=# select * from testtab6;
 id |            col1             
----+-----------------------------
  1 | {how,who,where}
  2 | {this,you,here}
  3 | {"how,,hh",who,"where,why"}
  4 | {"who's bread","It's ok."}
  5 | {"{this","you}haha",here}
  6 | {this,"\"you",here}
  
 
select typname,typdelim from pg_type where typname in ('int4','int8','bool','char','box');
testdb2=# select typname,typdelim from pg_type where typname in ('int4','int8','bool','char','box');
 typname | typdelim 
---------+----------
 bool    | ,
 char    | ,
 int8    | ,
 int4    | ,
 box     | ;
(5 rows)
除box外，其它类型都使用逗号作为分隔符。
---------------------------------------