postgresql数据类型

发布时间：2020-12-13 18:08:22 所属栏目：百科来源：网络整理

导读：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

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,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'01'::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,y2))
box ((x1,y2))
circle <(x,y),r>
path 闭合路径（与多边形相似） ((x1,...)
path 开放路径 [(x1,...]

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

select '1,1'::point;
select '(1,1)'::point;
select lseg '1,1,2,2';
select lseg '(1,1),(2,2)';
select lseg '(1,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,2';
lseg
---------------
[(1,2)]
(1 row)
testdb2=# select lseg '(1,2)';
lseg
---------------
[(1,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,null]'::jsonb;
json | jsonb
-----------------------+--------------------------
[9,null] | [9,null]
(1 row)

testdb2=# select json'{"name":"osdba","age":40,"sex":true,"money":232.32}';
json
-----------------------------------------------------
{"name":"osdba","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,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,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,3}'); insert into testtab05 values(2,'{4,6}'); insert into testtab05 values(3,'{7,8,9}'); testdb2=# select * from testtab05; id | col1 ----+--------- 1 | {1,3} 2 | {4,6} 3 | {7,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,"you,here}'); select * from testtab6; testdb2=# select * from testtab6; id | col1 ----+----------------------------- 1 | {how,where} 2 | {this,here} 3 | {"how,why"} 4 | {"who's bread","It's ok."} 5 | {"{this",here} 6 | {this,""you",here} select typname,typdelim from pg_type where typname in ('int4','int8','bool','char','box'); testdb2=# select typname,'box'); typname | typdelim ---------+---------- bool |,char |,int8 |,int4 |,box | ; (5 rows) 除box外，其它类型都使用逗号作为分隔符。 ---------------------------------------

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!