PostgreSQL入门之数据类型(1)

目录

 

说明

1、数值类型

1.1整数类型

1.2任意精度数值

1.3浮点数类型

1.4 Serial(序号)类型

2、字符类型

3、日期/时间类型

3.1日期 

3.2 时间

3.3 时间戳

4、布尔类型

5、货币类型

5、几何类型

6、枚举类型

---

说明

打算通过两篇章来介绍PostgreSQL的数据类型：

第一篇章主要介绍数值类型、字符类型、日期/时间类型、布尔类型、货币类型、几何类型及枚举类型。

第二篇主要介绍json类型、网络地址类型、位串类型、XML类型、数组类型、复合类型等。

数据类型是我们在创建表的时候为字段设定的类型。

PostgreSQL提供了丰富的数据类型，不仅内置了许多常用的数据类型（比如数值、字符等类型），还考虑到企业级特性增加了特殊的数据类型，并且还可以使用CREATE TYPE命令在数据库中创建新的数据类型。

下面来具体介绍。

1、数值类型

数值类型有2字节、4字节或8字节以及4字节或8字节的浮点数和可选精度的十进制数组成。如下表:

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

 

1.1整数类型

类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint。而只有在integer的范围不够的时候才使用bigint，因为前者(integer)绝对快得多。 

1.2任意精度数值

类型numeric可以存储最多1000位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢很多。
   numeric字段的最大精度和最大比例都是可以配置的。要声明一个类型为numeric的字段，你可以用下面的语法：
   NUMERIC(precision,scale)    (精度,刻度)
   比如数字23.5141的精度为6，而刻度为4。
   在目前的PostgreSQL版本中，decimal和numeric是等效的。

1.3浮点数类型

数据类型real和double是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储后再把数据打印出来可能显示一些缺失。

1.4 Serial(序号)类型

serial和bigserial类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。
 CREATE TABLE tablename (colname SERIAL);
  等价于
  CREATE SEQUENCE tablename_colname_seq;
  CREATE TABLE tablename(colname integer DEFAULT 

nextval('tablename_colname_seq') NOT NULL);
  这样，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。应用了一个NOT NULL约束以确保空值不会被插入。在大多数情况下你可能还希望附加一个UNIQUE或者PRIMARY KEY约束避免意外地插入重复的数值，但这个不是自动发生的。因此，如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。
    还需要另外说明的是，一个serial类型创建的序列在其所属字段被删除时，该序列也将被自动删除，但是其它情况下是不会被删除的。因此，如果你想用同一个序列发生器同时给几个字段提供数据，那么就应该以独立对象的方式创建该序列发生器。

2、字符类型

名字	存储长度	描述
character varing(n)、varchar(n)	可变长	有长度限制
character(n)、 char(n)	定长	不足补空白
text	可变长	无长度限制

定义了两种基本的字符类型，varchar(n)和char(n)，这里的n是一个正整数。两种类型都可以存储最多n个字符长的字串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长度。如果没有长度声明，char等于char(1)，而varchar则可以接受任何长度的字串。

这三种类型之间没有性能差别，只不过是在使用char类型时增加了存储尺寸。虽然在某些其它的数据库系统里，char(n)有一定的性能优势，但在PostgreSQL里没有。在大多数情况下，应该使用text或者varchar。

3、日期/时间类型

名字	存储空间	描述	最低值	最高值	分辨率
timestamp [ (p) ] [ without time zone ]	8 字节	日期和时间(无时区)	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone	8 字节	日期和时间，有时区	4713 BC	294276 AD	1 毫秒 / 14 位
date	4 字节	只用于日期	4713 BC	5874897 AD	1 天
time [ (p) ] [ without time zone ]	8 字节	只用于一日内时间	00:00:00	24:00:00	1 毫秒 / 14 位
time [ (p) ] with time zone	12 字节	只用于一日内时间，带时区	00:00:00+1459	24:00:00-1459	1 毫秒 / 14 位
interval [ fields ] [ (p) ]	12 字节	时间间隔	-178000000 年	178000000 年	1 毫秒 / 14 位

任何日期或者时间的文本输入均需要由单引号包围，就象一个文本字符串一样。

3.1日期 

以下为合法的日期格式列表

例子	描述
January 8, 1999	在任何datestyle输入模式下都无歧义
1999-01-08	ISO-8601格式，任何方式下都是1999年1月8号，(建议格式)
1/8/1999	歧义，在MDY下是1月8号；在 DMY模式下读做8月1日
1/18/1999	在MDY模式下读做1月18日，其它模式下被拒绝
01/02/03	MDY模式下的2003年1月2日；DMY模式下的2003年2月1日；YMD 模式下的2001年2月3日
1999-Jan-08	任何模式下都是1月8日
Jan-08-1999	任何模式下都是1月8日
08-Jan-1999	任何模式下都是1月8日
99-Jan-08	在YMD模式下是1月8日，否则错误
08-Jan-99	1月8日，除了在YMD模式下是错误的之外
Jan-08-99	1月8日，除了在YMD模式下是错误的之外
19990108	ISO-8601; 任何模式下都是1999年1月8日
990108	ISO-8601; 任何模式下都是1999年1月8日

 

 

3.2 时间

以下为合法的时间格式列表

例子	描述
04:05:06.789	ISO 8601
04:05:06	ISO 8601
04:05	ISO 8601
040506	ISO 8601
04:05 AM	与04:05一样；AM不影响数值
04:05 PM	与16:05一样；输入小时数必须 <= 12
04:05:06.789-8	ISO 8601
04:05:06-08:00	ISO 8601
04:05-08:00	ISO 8601
040506-08	ISO 8601

 

 

 

 

 

3.3 时间戳

时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区。因此，1999-01-08 04:05:06和1999-01-08 04:05:06 -8:00都是有效的数值。

4、布尔类型

PostgreSQL支持标准的boolean数据类型。

boolean有true(真)和false(假)两种状态，第三种unknown(未知)状态用null表示。

名称	存储格式	描述
boolean	1字节	true/falase

 

5、货币类型

money类型存储带固定小数精度的货币金额。

numeric、int和bigint类型的值可以转换为money,不建议使用浮点数来处理货币类型，以为存在舍入错误的可能性。

名称	存储格式	描述	范围
money	8 字节	货币金额	-92233720368547758.08 到 +92233720368547758.07

 

 

 

5、几何类型

几何数据类型表示二维的平面物体。

最基本的类型：点。它是其他类型的基础。

名字	存储空间	说明	表现形式
point	16 字节	平面中的点	(x,y)
line	32 字节	(无穷)直线(未完全实现)	((x1,y1),(x2,y2))
lseg	32 字节	(有限)线段	((x1,y1),(x2,y2))
box	32 字节	矩形	((x1,y1),(x2,y2))
path	16+16n 字节	闭合路径(与多边形类似)	((x1,y1),...)
path	16+16n 字节	开放路径	[(x1,y1),...]
polygon	40+16n 字节	多边形(与闭合路径相似)	((x1,y1),...)
circle	24 字节	圆	<(x,y),r> (圆心和半径)

 

 

 

6、枚举类型

枚举类型是一个包含静态和值得有序集合的数据类型。

PostgreSQL中的枚举类型类似于C语言中的enum。

需要使用CREATE TYPE命令创建

如下例子

创建星期枚举类型：

CREATE TYPE week AS enum ('Mon','Tue','Wed',

'Thu','Fri','Sat','Sun');

     创建表列类型为枚举week

     CREATE  TABLE t_week(id serial,create_mood week);

     插入数据：由于字段create_mood数据类型为枚举类

型，则字段的值只能为枚举类型week设定的值。

   INSERT INTO t_week(create_mood) values('Mon');

   如上插入的值为Mon,是枚举中的值，可插入成功。如下,插入的值MMM不是枚举week中的值，则会报错：

   INSERT INTO t_week(create_mood) values('MMM');

ERROR:  22P02: invalid input value for enum week: "MMM"