Postgresql学习笔记之——数据类型概括

在学习Postgresql前先了解一下它所支持的数据类型，以便于了解如何在数据库中存储数据。
一、Postgresql数据库支持很多类型，大概分类有如下几种：

分类名称	说明	与其他数据库对比
布尔类型	支持标准的boolean数据类型	与mysql的BOOL、BOOLEAN类型相同，占用空间为1字节
数值类型	整数类型有2字节的smallint、4字节的int、8字节的bigint。十进制精确类型有numeric，浮点类型有real、double precision。还有8字节的货币类型 money	无mysql的unsigned整数类型，也没有1字节长度的tinyint整数类型和3字节长度的mediumint整数类型
字符类型	有varchar(n)、char(n)、text三种类型	Postgresql中的varchar最大可以存储1GB，mysql中最大只能是64KB，而text类型相当于mysql中的LONGTEXT类型
二进制类型	只有一种bytea	对应mysql的BLOB、LONGBLOB类型
位串类型	位串就是一串1和0的字符串，有bit(n)、bit varying(n)两种	其他数据库没有此类型
日期和时间类型	有date、time、timestamp，而time和timestamp又分是否包括时区的两种类型	在Postgresql中可以精确到秒以下，如毫秒。mysql中只能精确到秒
枚举类型	枚举类型是一种包含一系列有序静态值集合的数据类型，等于某些编程语言中的enum类型	Postgresql使用枚举类型前需要先使用create type 创建这个类型。mysql中也有枚举类型ENUM
几何类型	包括了点（point）、直线（line）、线段（lseg）、路径（path）、多边形（polygon）、圆（cycle）等类型	PG特有类型，相当于数据库内置的自定义类型
网络地址类型	有cidr、inet、macaddr三种类型	PG特有类型
数组类型	可以存储一个数组	PG特有类型
复合类型	把已有的简单类型组合成用户自定义的类型	类似其他种类数据库的自定义类型
xml类型	可以存储XML数据的类型
json类型	可以存储json数据的类型
range类型	范围类型，存放范围数据
对象标识符类型	Postgresql内部标识对象的类型，如oid类型、regproc类型、regclass类型
伪类型	伪类型不能作为字段的数据类型，但是可以声明一个函数的参数或者结果类型。有any、anyarray、anyelement、cstring、internal、language_hander、record、trigger、void、opaque
其他类型	一些不易区分的类型，如UUID、pg_lsn

为了提高SQL的兼容性，部分类型可以简写，如integer类型可以用int、int4表示，smallint可以用int2表示；char varying可以用varchar表示；numeric(m,n)可以用decimal(m,n)表示。

二、类型的输入与转换
1.简单的类型，像数字或者字符串，习惯的正常输入，数据库可以自动识别：

postgres=# select 1, 1.123, 'hello world';
 ?column? | ?column? |  ?column?   
----------+----------+-------------
        1 |    1.123 | hello world
(1 row)

2.其他复杂的数据类型，可以通过前缀类型名加上单引号括起来的值的格式进行输入：

postgres=# select bit '10011101', int'1'+ int'4';
   bit    | ?column? 
----------+----------
 10011101 |        5
(1 row)

实际上所有类型都可以通过上面的方法输入。

3.Postgresql支持标准SQL的类型转换函数CAST进行类型转换：

postgres=# select CAST('1200' as int), CAST('2020-02-15' as date);
 int4 |    date    
------+------------
 1200 | 2020-02-15
(1 row)

此外，Postgresql还有一种更简洁的类型转换方式，使用双冒号加数据类型：

postgres=# select '13'::int, '2020-02-15'::date;
 int4 |    date    
------+------------
   13 | 2020-02-15
(1 row)

Postgresql数据库的数据类型与常接触的Oracle、MySQL数据库的数据类型在常用数据类型上差别不太，如字符、数字、日期等，但它有自己的特殊的数据类型，如位串类型、网络地址类型、数组类型等。这些特有的数据类型的使用要在数据库表数据存储时多使用才能具体了解其使用方法。