在学习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数据库的数据类型在常用数据类型上差别不太,如字符、数字、日期等,但它有自己的特殊的数据类型,如位串类型、网络地址类型、数组类型等。这些特有的数据类型的使用要在数据库表数据存储时多使用才能具体了解其使用方法。