您现在的位置是：网站首页> 编程资料编程资料
PostgreSQL教程(四)：数据类型详解_PostgreSQL_
2023-05-27
421人已围观
简介 PostgreSQL教程(四)：数据类型详解_PostgreSQL_
一、数值类型：
下面是PostgreSQL所支持的数值类型的列表和简单说明：
1. 整数类型：
类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint。而只有在integer的范围不够的时候才使用bigint，因为前者(integer)绝对快得多。
2. 任意精度数值：
类型numeric可以存储最多1000位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢很多。
numeric字段的最大精度和最大比例都是可以配置的。要声明一个类型为numeric的字段，你可以用下面的语法：
NUMERIC(precision,scale)
比如数字23.5141的精度为6，而刻度为4。
在目前的PostgreSQL版本中，decimal和numeric是等效的。
3. 浮点数类型：
数据类型real和double是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储后再把数据打印出来可能显示一些缺失。
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类型创建的序列在其所属字段被删除时，该序列也将被自动删除，但是其它情况下是不会被删除的。因此，如果你想用同一个序列发生器同时给几个字段提供数据，那么就应该以独立对象的方式创建该序列发生器。
二、字符类型：
下面是PostgreSQL所支持的字符类型的列表和简单说明：
SQL 定义了两种基本的字符类型，varchar(n)和char(n)，这里的n是一个正整数。两种类型都可以存储最多n个字符长的字串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长度。如果没有长度声明，char等于char(1)，而varchar则可以接受任何长度的字串。
MyTest=> CREATE TABLE testtable(first_col varchar(2));
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES('333'); --插入字符串的长度，超过其字段定义的长度，因此报错。
ERROR: value too long for type character varying(2)
--插入字符串中，超出字段定义长度的部分是空格，因此可以插入，但是空白符被截断。
MyTest=> INSERT INTO testtable VALUES('33 ');
INSERT 0 1
MyTest=> SELECT * FROM testtable;
first_col
-----------
33
(1 row)
这里需要注意的是，如果是将数值转换成char(n)或者varchar(n)，那么超长的数值将被截断成n个字符，而不会抛出错误。
MyTest=> select 1234::varchar(2);
varchar
---------
12
(1 row)
最后需要提示的是，这三种类型之间没有性能差别，只不过是在使用char类型时增加了存储尺寸。虽然在某些其它的数据库系统里，char(n)有一定的性能优势，但在PostgreSQL里没有。在大多数情况下，应该使用text或者varchar。
三、日期/时间类型：
下面是PostgreSQL所支持的日期/时间类型的列表和简单说明：
1. 日期/时间输入:
任何日期或者时间的文本输入均需要由单引号包围，就象一个文本字符串一样。
1). 日期:
以下为合法的日期格式列表：
2). 时间：
以下为合法的时间格式列表：
3). 时间戳：
时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区。因此，1999-01-08 04:05:06和1999-01-08 04:05:06 -8:00都是有效的数值。
2. 示例：
1). 在插入数据之前先查看datestyle系统变量的值：
MyTest=> show datestyle;
DateStyle
-----------
ISO, YMD
(1 row)
2). 创建包含日期、时间和时间戳类型的示例表：
MyTest=> CREATE TABLE testtable (id integer, date_col date, time_col time, timestamp_col timestamp);
CREATE TABLE
3). 插入数据：
MyTest=> INSERT INTO testtable(id,date_col) VALUES(1, DATE'01/02/03'); --datestyle为YMD
INSERT 0 1
MyTest=> SELECT id, date_col FROM testtable;
id | date_col
----+------------
1 | 2001-02-03
(1 row)
MyTest=> set datestyle = MDY;
SET
MyTest=> INSERT INTO testtable(id,date_col) VALUES(2, DATE'01/02/03'); --datestyle为MDY
INSERT 0 1
MyTest=> SELECT id,date_col FROM testtable;
id | date_col
----+------------
1 | 2001-02-03
2 | 2003-01-02
MyTest=> INSERT INTO testtable(id,time_col) VALUES(3, TIME'10:20:00'); --插入时间。
INSERT 0 1
MyTest=> SELECT id,time_col FROM testtable WHERE time_col IS NOT NULL;
id | time_col
----+----------
3 | 10:20:00
(1 row)
MyTest=> INSERT INTO testtable(id,timestamp_col) VALUES(4, DATE'01/02/03');
INSERT 0 1
MyTest=> INSERT INTO testtable(id,timestamp_col) VALUES(5, TIMESTAMP'01/02/03 10:20:00');
INSERT 0 1
MyTest=> SELECT id,timestamp_col FROM testtable WHERE timestamp_col IS NOT NULL;
id | timestamp_col
----+---------------------
4 | 2003-01-02 00:00:00
5 | 2003-01-02 10:20:00
(2 rows)
四、布尔类型：
PostgreSQL支持标准的SQL boolean数据类型。boolean只能有两个状态之一：真(True)或 假(False)。该类型占用1个字节。
"真"值的有效文本值是：
TRUE
't'
'true'
'y'
'yes'
'1'
而对于"假"而言，你可以使用下面这些：
FALSE
'f'
'false'
'n'
'no'
'0'
见如下使用方式：
MyTest=> CREATE TABLE testtable (a boolean, b text);
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES(TRUE, 'sic est');
INSERT 0 1
MyTest=> INSERT INTO testtable VALUES(FALSE, 'non est');
INSERT 0 1
MyTest=> SELECT * FROM testtable;
a | b
---+---------
t | sic est
f | non est
(2 rows)
MyTest=> SELECT * FROM testtable WHERE a;
a | b
---+---------
t | sic est
(1 row)
MyTest=> SELECT * FROM testtable WHERE a = true;
a | b
---+---------
t | sic est
(1 row)
五、位串类型：
位串就是一串1和0的字串。它们可以用于存储和视觉化位掩码。我们有两种类型的SQL位类型：bit(n)和bit varying(n); 这里的n是一个正整数。bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的数据都是错误的。类型bit varying数据是最长n的变长类型；更长的串会被拒绝。写一个没有长度的bit等效于bit(1)，没有长度的bit varying相当于没有长度限制。
针对该类型，最后需要提醒的是，如果我们明确地把一个位串值转换成bit(n)，那么它的右边将被截断或者在右边补齐零，直到刚好n位，而不会抛出任何错误。类似地，如果我们明确地把一个位串数值转换成bit varying(n)，如果它超过n位，那么它的右边将被截断。 见如下具体使用方式：
MyTest=> CREATE TABLE testtable (a bit(3), b bit varying(5));
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES (B'101', B'00');
INSERT 0 1
MyTest=> INSERT INTO testtable VALUES (B'10', B'101');
ERROR: bit string length 2 does not match type bit(3)
MyTest=> INSERT INTO testtable VALUES (B'10'::bit(3), B'101');
INSERT 0 1
MyTest=> SELECT * FROM testtable;
a | b
-----+-----
101 | 00
100 | 101
(2 rows)
MyTest=> SELECT B'11'::bit(3);
bit
-----
110
(1 row)
六、数组：
1. 数组类型声明：
1). 创建字段含有数组类型的表。
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[] --还可以定义为integer[4]或integer ARRAY[4]
);
2). 插入数组数据：
MyTest=# INSERT INTO sal_emp VALUES ('Bill', '{11000, 12000, 13000, 14000}');
INSERT 0 1
MyTest=# INSERT INTO sal_emp VALUES ('Carol', ARRAY[21000, 22000, 23000, 24000]);
INSERT 0 1
MyTest=# SELECT * FROM sal_emp;
name | pay_by_quarter
--------+---------------------------
Bill | {11000,12000,13000,14000}
Carol | {21000,
相关内容
- PostgreSQL教程(三)：表的继承和分区表详解_PostgreSQL_
- PostgreSQL教程(十)：性能提升技巧_PostgreSQL_
- PostgreSQL教程(九)：事物隔离介绍_PostgreSQL_
- PostgreSQL教程(八)：索引详解_PostgreSQL_
- PostgreSQL教程(七)：函数和操作符详解（3）_PostgreSQL_
- PostgreSQL教程(六)：函数和操作符详解（2）_PostgreSQL_
- PostgreSQL教程(五)：函数和操作符详解（1）_PostgreSQL_
- PostgreSQL教程(四)：数据类型详解_PostgreSQL_
- PostgreSQL教程(三)：表的继承和分区表详解_PostgreSQL_
- PostgreSQL教程(二)：模式Schema详解_PostgreSQL_
点击排行
本栏推荐
