PostgreSQL数据类型

数据类型指定要在表字段中存储哪种类型的数据。在创建表时，对于每列必须使用数据类型。
PotgreSQL中主要有三种类型的数据类型。此外，用户还可以使用CREATE TYPE SQL命令创建自己的自定义数据类型。

以下是PostgreSQL中主要有三种类型的数据类型：

数值数据类型
字符串数据类型
日期/时间数据类型

数值数据类型

数字数据类型用于指定表中的数字数据。

字符串数据类型

String数据类型用于表示字符串类型值。

日期/时间数据类型

日期/时间数据类型用于表示使用日期和时间值的列。

一些其他数据类型

布尔类型：

货币类型：

几何类型：

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

二进制类型：

PostgreSQL中的bytea字段类型可以以二进制的形式存储数据，这样做的好处就是可以将原本存储在网站目录下的文件存储到数据库中，坏处就是如果文件过多、过大的话，就会导致数据库的数据量大大增加，备份和恢复的时候就会浪费大量的时间，而且数据也有可能会出错。个人觉得，在文件量小的情况下，使用这种存储方式还是很方便的。

PostgreSQL 本身支持一种二进制的方式来存储数据类型为bytea, 使用这个类型存储数据有什么好处。

有人问是否可以在一个字段中存储1个G 的数据。一个数据库中字段存储数据是无可厚非的，但实际上存储数据的方式和大小决定了一个数据库是否能进行正常的运作，软件的设计中也有相关的限制，数据库本身可以理解为一个软件，既然是软件，既然有相关的数据结构的设计，则什么是适合的什么是不适合的都有相关的定论。

PostgreSQL 本身支持一种二进制的方式来存储数据类型为bytea, 使用这个类型存储数据有什么好处。

1 可以存储任意大的数据
2 数据已块的方式读取，速度快

实际上，存储大容量的数据在数据库中是会对其进行压缩的，而数据的压缩虽然从存储上是有利的，但数据的提取中就会遇到我只需要其中一块的数据，但由于数据是压缩的，所以必须全面解压数据后，才能提取另一部分的数据，这就造成了数据提取的缓慢和资源的浪费消耗等问题。

所以POSTGRESQL 提出了一个方式来存储数据并不进行压缩，将其存储在数据库整体之外的方式。那具体这样做的好处和特点，是什么我们可以测试一下

首先我们创建两个表一个表使用了 storage external 的方式来存储我们的data_save字段，另一个我们采用本身PG的方式来存储我们看看有什么不同

我们可以清晰的看到使用了stroage extenal 的表在存储229MB 容量的数据到一个字段的情况下，使用了这个技术要比不使用快 2 – 5秒，经过多次试验，另外根据插入的数据越大，之间的差距也是越来越大。

另外我们需要看到到底存储这些数据的物理空间有没有不同

我们到目前数据库的存储的物理位置，同时都存储一个229MB的文件到一个字段中的结果

可以看到如果使用独有的列外排的技术，则数据基本上没有太大的压缩，最后实际上存储的数据215MB ，而如果使用默认的方式来存储数据则实际的数据变为63MB，压缩的比率在3.6.

json /jsonb类型：

postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。

CREATE TABLE "test"."test_json1" (
  "id" serial2,
  "name" json,
  PRIMARY KEY ("id")
)；INSERT INTO "test_json1" ("name")  VALUES('{"col1":2,"col2":"fp","col3":"male"}') ；INSERT INTO "test_json1" ("name")  VALUES('{"col1":1,"col2":"francs","col3":"female"}')

PostgreSQL 支持两种JSON 数据类型： json 和jsonb ，两种类型在使用上几乎完全相同，两者主要区别为以下： json 存储格式为文本而jsonb 存储格式为二进制，由于存储格式的不同使得两种json 数据类型的处理效率不一样， json 类型以文本存储并且存储的内容和输人
数据一样，当检索json 数据时必须重新解析，而jsonb 以二进制形式存储已解析好的数据，当检索jsonb 数据时不需要重新解析，因此json 写人比jsonb 快，但检索比jsonb 慢。

（1）jsonb 输出的键的顺序和输入不一样，而json 的输出键的顺序和输入完全一样。

（2）jsonb 类型会去掉输入数据中键值的空格，而json是保持原样

因此在大多数应用场景下建议使用jsonb ，除非有特殊的需求，比如对json 的键顺序有特殊的要求。

首先，我们看一下 json：

SELECT '{"username":"pedro","age":23}'::json;

                 json
    -------------------------------
     {"username":"pedro","age":23}

在 Postgresql 中::符号用于类型转换，该语句将字符串'{"username":"pedro","age":23}'，通过类型转换为json，得到了 json 数据结果。

前面，我们谈到 json 以文本格式存储数据，且插入较快，那么是不是真的如此了？

SELECT '{"username":"pedro",    "age":     23}'::json;

                      json
    ----------------------------------------
     {"username":"pedro",    "age":     23}
    Time: 0.221 ms

使用 jsonb：

从结果可以看出 json 确实以文本格式存储了数据，多余的空格依旧存在，那么再看 jsonb：

SELECT '{"username":"pedro",    "age":     23}'::jsonb;

                  jsonb
    ----------------------------------
     {"age": 23, "username": "pedro"}
    Time: 0.265 ms

可以看到，jsonb 处理多余的空格，因此消耗的时候多了那么一点，在实际的测试中，json 的插入性能确实比 jsonb 要高。

92IT

Just love IT.

PostgreSQL数据类型