精心总结的PG数据类型介绍及数据类型转换

概述

续上次的PG数据类型介绍,下面的类型相对比较少见,不过建议还是要做一下了解。


一、JSON/JSONB类型

postgresql不只是关系型数据库,同时还支持非关系数据类型json。 json属于重量级的非常规数据类型。

1、列表

JSON数据类型可以用来存储JSON(JavaScript对象符号)数据。这样的数据也可以被存储为文本,但json数据类型具有的优点是检查每个存储的值是否为有效的JSON值。也有相关的支持功能可以直接用来处理JSON数据类型,如下所示:

精心总结的PG数据类型介绍及数据类型转换

2、简单应用

--1.创建
CREATE TABLE test_json1(
	id serial PRIMARY key,
	name json
);
--2.插入数据
INSERT INTO test_json1(name) VALUES ('{"col1":1,"col2":"jack","col3":"male"}');
--3.查询
SELECT * FROM test_json1;
--4. 通过 "->"操作符可以查询json数据的键值
SELECT name -> 'col2' AS name FROM test_json1 WHERE id = 1;
--5.如果以文本格式返回json字段键值可以使用 "->>"操作符
SELECT name ->> 'col2' FROM test_json1 WHERE id = 1;

3、jsonb 与json差异

json存储格式为文本而jsonb存储为二进制。json类型以文本存储并储存的内容和输入数据一样,当检索json数据时必须重新解析,而jsonb以二进制形式存储已解析好的数据。当检索jsonb数据时不需要重新解析,因此json写入比jsonb快,但检索比jsonb慢。

json保证输入和输出顺序,jsonb不保证加粗样式

jsonb类型会去掉输入数据中键值的空格,另外会删除重复的键,仅保留最后一个。

--1.删除json数据的键/值。
SELECT '{"a":1,"b":2}'::jsonb - 'a';

--jsonb与json函数
--扩展最外层的json对象称为一组 键/值得结果集。
SELECT * FROM json_each('{"a":"foo","b":"bar"}');
/*
key	value
a	"foo"
b	"bar"
*/

--以文本格式返回
SELECT * FROM json_each_text('{"a":"foo","b":"bar"}');
/*
key	value
a	foo
b	bar
*/

--row_to_json()函数
-- 能够将行作为json对象返回,此函数常用来生成json测试数据。
-- 比如将普通表转换成json类型表。
create TABLE test_copy(
	id int4,
	name varchar(20)
);
INSERT INTO test_copy(id,name) VALUES(1,'a'),(2,'b');

-- 将普通表转成json类型表
SELECT row_to_json(test_copy) FROM test_copy;
/**
row_to_json
{"id":1,"name":"a"}
{"id":2,"name":"b"}
*/

-- 返回最外层的json对象中的键的集合
SELECT * FROM json_object_keys('{"a":"foo","b":"bar"}');
/*
json_object_keys
a
b
*/

-- jsonb 键/值的追加、删除、更新
-- jsonb 追加 通过 "||"操作符
SELECT '{"name":"jack","age":"31"}'::jsonb || '{"sex":"male"}'::jsonb;
-- {"age": "31", "sex": "male", "name": "jack"}

-- json 键/值删除有两种方式 : "-" 和 "#-"
SELECT '{"age": "31", "sex": "male", "name": "jack"}' ::jsonb - 'sex';
-- {"age": "31", "name": "jack"}

-- 第二种:"#-" 通常用于嵌套json数据删除的场景
SELECT '{"name":"meki","contact":{"phone":"1234","fax":"0101"}}'::jsonb #- '{contact,fax}'::text[];
-- {"name": "meki", "contact": {"phone": "1234"}}

-- 键值更新:一种方式通过 "||" ;另外一种是 jsonb_set
SELECT '{"name":"frank","age":"11"}'::jsonb || '{"age":"12"}'::jsonb;
-- {"age": "12", "name": "frank"}

--jsonb_set(target jsonb,path text[],new_value [,create_missing booean)
-- create_missing值为false表示如果键不存在则不添加
SELECT jsonb_set('{"name":"frank","age":"11"}'::jsonb,'{sex}','"male"'::jsonb,true);
-- {"age": "11", "sex": "male", "name": "frank"}

二、复合类型

此类型代表一个字段名和数据类型,即结构的一个表中的行或记录列表。

1、复合类型声明

下面的例子演示如何声明一个复合类型:

CREATE TYPE inventory_item AS (
 name text,
 supplier_id integer,
 price numeric
); 
此数据类型可用于在创建表如下所示:
CREATE TABLE on_hand (
 item inventory_item,
 count integer
);

2、复合值输入

复合值可以插入文字常量,封装领域括号内的值,并用逗号将它们隔开。

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

此有效的定义同上的inventory_item的。行关键字实际上是可选的表达式中,只要有一个以上的字段。

3、访问复合类型

要访问一个复合列的字段,字段名,使用点很像选择字段从一个表名。例如,要选择一些子字段,on_hand示例表的查询如下:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99; 
--甚至可以使用表名(例如,在一个多表查询),像这样:
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

三、其他类型

1、货币类型

货币类型存储的货币金额与一个固定的分数精度。可以转换为金钱的数字,int和bigint数据类型的值。不推荐使用浮点数来处理金钱的潜力,由于舍入误差。

精心总结的PG数据类型介绍及数据类型转换

2、二进制数据类型

bytea数据类型允许存储二进制字符串,如下面的表格中说明。

精心总结的PG数据类型介绍及数据类型转换

3、布尔类型

PostgreSQL提供了标准的SQL类型布尔值。布尔类型可以有几种状态:true,false,和第三状态null,这是SQL空值表示。

精心总结的PG数据类型介绍及数据类型转换

4、枚举类型

枚举(枚举)类型的数据类型,包括静态,有序设置的值。在许多编程语言支持枚举类型,它们是相等。

Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This type is used to store a static, ordered set of values, for example compass directions, i.e. NORTH, SOUTH, EAST, and WEST or days of the week as below:

枚举一旦产生,它们可以像任何其他类型。

5、几何类型

几何数据类型表示二维空间对象。最根本的不同点是形成的所有其他类型的基础。

精心总结的PG数据类型介绍及数据类型转换

6、网络地址类型

PostgreSQL提供的数据类型来存储的IPv4,IPv6的地址和MAC地址。这是更好地使用这些类型,而不是纯文本类型存储网络地址,因为这些类型提供输入错误检查和特殊的操作和函数。

精心总结的PG数据类型介绍及数据类型转换

7、位串类型

位串类型用于存储位掩码。他们要么是0或1。 SQL位类型有两种:(n)的位而变位(n)的,其中n是一个正整数

8、文本搜索类型

这个类型支持全文检索,这是通过自然语言文档的集合的搜索,找到那些最符合查询活动。这有两种数据类型:

精心总结的PG数据类型介绍及数据类型转换

9、UUID类型

一个UUID(通用唯一标识符)写成小写的十六进制数字序列,由连字号,特别是一组8位数字,然后由三组4位数字,然后由一组12位数字分开几组,总32位,128位代表。

一个UUID的例子是: 550e8400-e29b-41d4-a716-446655440000

10、XML Type

xml数据类型可以用来存储XML数据。对于存储XML数据,首先创建XML值函数XMLPARSE如下:

XMLPARSE (DOCUMENT '

PostgreSQL Tutorial 
 ...
')

XMLPARSE (CONTENT 'xyzbarfoo')

11、范围类型

范围类型的数据类型,采用了一系列数据。范围类型可以是离散的范围(例如,所有的整数值1到10)或连续范围(例如任何时间点的上午10:00到上午11:00)。

内置的范围类型范围包括:

  • int4range - Range of integer
  • int8range - Range of bigint
  • numrange - Range of numeric
  • tsrange - Range of timestamp without time zone
  • tstzrange - Range of timestamp with time zone
  • daterange - Range of date

可以创建自定义的范围类型,做出新的类型的适用范围,如使用int类型为基础的IP地址范围,或者使用浮点数据类型为基础的浮动范围。

范围类型支持包容性和排他性的范围边界分别使用[]和()个字符,例如: [4,9]'代表所有从包括4但不包括9的整数。

12、对象标识符类型

对象标识符(OID)内部使用PostgreSQL作为各种系统表的主键。 OIDS IfWITH指定或default_with_oids配置变量,只有在这样的情况下启用的OID被添加到用户创建的表。下表列出了几个别名类型。 OID别名类型有没有自己的操作,除了专门的输入和输出过程。

精心总结的PG数据类型介绍及数据类型转换

13、伪类型

PostgreSQL类型系统包含了一些特殊用途的统称为伪类型的项。一个伪类型不能被用作列的数据类型,但它可以用来声明一个函数的参数或结果类型。下表列出了现有的伪类型。

精心总结的PG数据类型介绍及数据类型转换

精心总结的PG数据类型介绍及数据类型转换


四、数据类型转换

postgresql数据类型转换主要有三种方式:

  • 通过格式化函数
  • CAST函数
  • ::操作符

1、格式化函数

精心总结的PG数据类型介绍及数据类型转换

2、通过CAST函数进行转换

3、通过 ::操作符进行转换


觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~

精心总结的PG数据类型介绍及数据类型转换

相关推荐