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 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'\\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,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外，其它类型都使用逗号作为分隔符。 ---------------------------------------

总结

以上是内存溢出为你收集整理的postgresql数据类型全部内容，希望文章能够帮你解决postgresql数据类型所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。