PostgreSQL -- 数据类型

x33g5p2x  于10个月前 转载在 其他  
字(9.0k)|赞(0)|评价(0)|浏览(178)

主要参考菜鸟教程
数据类型是在创建表时,为每个字段设置的;
使用 CREATE TYPE 命令创建;

1. 创建语法回顾

CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)

2. 数值类型

名字存储长度描述范围
smallint2 字节小范围整数-32768 到 +32767
integer4 字节常用的整数-2147483648 到 +2147483647
bigint8 字节大范围整数-9223372036854775808 到 +9223372036854775807
decimal可变长用户指定的精度,精确小数点前 131072 位;小数点后 16383 位
numeric可变长用户指定的精度,精确小数点前 131072 位;小数点后 16383 位
real4 字节可变精度,不精确6 位十进制数字精度
double precision8 字节可变精度,不精确15 位十进制数字精度
smallserial2 字节自增的小范围整数1 到 32767
serial4 字节自增整数1 到 2147483647
bigserial8 字节自增的大范围整数1 到 9223372036854775807

3. 货币类型

  • money 类型存储带有固定小数精度的货币金额。
  • numeric、int 和 bigint 类型的值可以转换为 money,不建议使用浮点数来处理处理货币类型,因为存在舍入错误的可能性。
名字存储容量描述范围
money8 字节货币金额-92233720368547758.08 到 +92233720368547758.07

4. 字符类型

名字描述
varchar(n)character varying(n)变长,有长度限制,最大为 10485760(1G)
char(n)character(n)定长,不足补空白,最大为 10485760(1G)
text变长,无长度限制

textvarchar 几乎无性能差别,区别仅在于存储结构的不同;
*
长度确定的字符串使用 char
*
长度不定的使用 varchartext
*
在 PostgreSQL 数据库中,character(n) 通常是这三个中最慢的,因为额外存储成本,故而应选择 textvarchar
*
栗子:
*
创建一个表 character_tests

postgres=# CREATE TABLE character_tests (
postgres(# id serial PRIMARY KEY,
postgres(# x CHAR (1),
postgres(# y VARCHAR (10),
postgres(# z TEXT
postgres(# );
CREATE TABLE
  • 给 character_tests 表中插入一些示例数据(我们定义了 char 的长度,所以过长的字符会报错)
postgres=# INSERT INTO character_tests (x, y, z)
postgres-# VALUES
postgres-# (
postgres(# 'Y',
postgres(# 'This is a test for varchar',
postgres(# 'This is a very long text for the PostgreSQL text column'
postgres(# );
错误:  对于可变字符类型来说,值太长了(10)
  • 给 character_tests 表中插入正确的数据(对插入成功返回的数据还不理解,应该表示成功了)
postgres=# INSERT INTO character_tests (x, y, z)
postgres-# VALUES
postgres-# (
postgres(# 'Y',
postgres(# 'varchar(n)',
postgres(# 'This is a very long text for the PostgreSQL text column'
postgres(# );
INSERT 0 1
  • 查询 character_tests 表中的数据:
postgres=# SELECT * FROM character_tests
postgres-# ;
 id | x |     y      |                            z                            
----+---+------------+---------------------------------------------------------
  1 | Y | varchar(n) | This is a very long text for the PostgreSQL text column
(1 行记录)

5. 日期/时间类型

AD 表示公元后,2021AD 表示公元 2021 年;
BC 表示公元前,230BC 表示公元前 230 年。
1 毫秒/ 14 位。这个就不太理解,时间戳一共 41 位,所以,可能是 1 毫秒可以走 14 位;

名字存储空间描述最低值最高值分辨率
timestamp [ (p) ] [ without time zone ]8 字节日期和时间(无时区)4713 BC294276 AD1 毫秒 / 14 位
timestamp [ (p) ] with time zone8 字节日期和时间(有时区)4713 BC294276 AD1 毫秒 / 14 位
data4 字节只用于日期4713 BC5874897 AD1 天
time [ (p) ] [ without time zone ]8 字节只用于一日内时间(无时区)00:00:0024:00:001 毫秒 / 14 位
time [ (p) ] with time zone12 字节只用于一日内时间(有时区)00:00:00+145924:00:00-14591 毫秒 / 14 位
interval [ fields ] [ (p) ]12 字节时间间隔-178000000 年178000000 年1 毫秒 / 14 位

6. 布尔类型

名称存储空间描述
boolean1 字节true / false
  • boolean 有 true(真)或 false(假)两个状态, 第三种 unknown (未知)状态,用 NULL 表示。

7. 枚举类型

  • 枚举类型是包含静态和值的有序集合的数据类型;

  • 与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建;
    这一块回顾之前创建数据类型方式:x CHAR (1)

  • 创建枚举类型的命令:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
  • 栗子:
  • 创建枚举类型 mood
postgres=# CREATE TYPE mood AS ENUM('sad', 'ok', 'happy');
CREATE TYPE
  • 创建表 person
postgres=# CREATE TABLE person(
postgres(# name text,
postgres(# current_mood mood
postgres(# );
CREATE TABLE
  • 插入数据
postgres=# INSERT INTO person VALUES('Alice', 'ok');
INSERT 0 1
  • 查看表中心情为 ok 的人
postgres=# SELECT * FROM person WHERE current_mood = 'ok';
 name  | current_mood 
-------+--------------
 Alice | ok
(1 行记录)

8. 几何类型

  • 最基本的几何类型:点(point),是其他类型的基础。
名字存储空间说明表现形式
point16 字节平面中的点(x,y)
line32 字节(无穷)直线(未完全实现)((x1,y1),(x2,y2))
lseg32 字节(有限)线段((x1,y1),(x2,y2))
box32 字节矩形((x1,y1),(x2,y2))
path16+16n 字节闭合路径(与多边形类似)((x1,y1),…)
path16+16n 字节开放路径[(x1,y1),…]
polygon40+16n 字节多边形(与闭合路径相似)((x1,y1),…)
circle24 字节<(x,y),r> (圆心和半径)

9. 网络地址类型

  • PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。
  • 这些类型提供输入错误检查和特殊的操作和功能。
名字存储空间描述
cidr7 或 19 字节IPv4 或 IPv6 网络
inet7 或 19 字节IPv4 或 IPv6 主机和网络
macaddr6 字节MAC 地址
  • 在对 inet 或 cidr 数据类型进行排序的时候, IPv4 地址总是排在 IPv6 地址前面。

10. 位串类型

  • 位串就是一串 1 和 0 的字符串。
  • 它们可以用于存储和直观化位掩码。
名字描述说明
bit(n)bit 等效 bit(1)bit 类型的数据必须准确匹配长度 n,存储短些或者长一些的数据都是错误的
bit varying(n)bit varying 表示无长度限制bit varying 类型数据是最长 n 的变长类型,更长的串会被拒绝

11. 文本搜索类型

  • 全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。
名字描述
tsvectortsvector 的值是一个无重复值的 lexemes 排序列表, 即一些同一个词的不同变种的标准化。
tsquerytsquery 存储用于检索的词汇,并且使用布尔操作符 &(AND),|(OR)和!(NOT) 来组合它们,括号用来强调操作符的分组。

12. UUID 类型

  • UUID 数据类型用来存储 RFC 4122,ISO/IEF 9834-8:2005 以及相关标准定义的通用唯一标识符

  • 这个标识符是一个由算法产生的 128 位标识符,使它不可能在已知使用相同算法的模块中和其他方式产生的标识符相同。

  • 对分布式系统而言,这种标识符比序列能更好的提供唯一性保证,因为序列只能在单一数据库中保证唯一。

  • UUID 被写成一个小写十六进制数字的序列,由分字符分成几组:

  • 一组 8 位数字 + 3 组 4 位数字 + 一组 12 位数字;

  • 总共 32 个数字代表 128 位;

  • 一个这种标准的 UUID 例子如下:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

13. XML 类型

  • xml 数据类型可以用于存储 XML 数据。
  • 将 XML 数据存到 text 类型中的优势在于它能够为结构良好性来检查输入值, 并且还支持函数对其进行类型安全性检查。
  • 要使用这个数据类型,编译时必须使用 configure --with-libxml

13.1 创建 XML 值

  • 使用函数 xmlparse从字符数据产生 xml 类型的值:
XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

14. JSON 类型

  • json 数据也可以存储为 text,但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。
实例结果
array_to_json('{{1,5},{99,100}}'::int[])[[1,5],[99,100]]
row_to_json(row(1,'foo')){“f1”:1,“f2”:“foo”}

15. 数组类型

  • PostgreSQL 允许将字段定义成变长的多维数组。
  • 数组类型可以是任何基本类型或用户定义类型,枚举类型或复合类型。

15.1 声明数组

  • 创建表的时候,可以声明数组,方式如下:
postgres=# CREATE TABLE sal_emp(
postgres(# name text,
postgres(# pay_by_quarter integer[],
postgres(# schedule text[][]
postgres(# );
CREATE TABLE
  • pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。
  • 也可以使用 ARRAY 关键字,如下所示:
postgres=# CREATE TABLE sal_emp(
postgres(# name text,
postgres(# pay_by_quarter integer ARRAY[4],
postgres(# schedule text[][]
postgres(# );
CREATE TABLE

15.2 插入值

  • 插入值使用花括号 {},元素在 {} 使用逗号隔开:
postgres=# INSERT INTO sal_emp VALUES
(                         
'Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}'
);
INSERT 0 1

postgres=# INSERT INTO sal_emp VALUES
postgres-# (
postgres(# 'Carol',
postgres(# '{20000, 25000, 25000, 25000}',
postgres(# '{{"breakfast", "consulting"}, {"meeting", "lunch"}}'
postgres(# );
INSERT 0 1

15.3 访问数组

  • 查询检索在第二季度薪水变化的雇员名:
postgres=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
 name  
-------
 Carol
(1 行记录)

15.4 修改数组

postgres=# UPDATE sal_emp SET pay_by_quarter = '{19999, 39999, 39999, 39999}' WHERE name = 'Carol';
  • 也可以使用 ARRAY 构造器语法:
postgres=# UPDATE sal_emp SET pay_by_quarter = ARRAY[19999, 39999, 39999, 39999] WHERE name = 'Carol';

15.5 数组中检索

  • 要搜索一个数组中的数值,必须检查该数组的每一个值。
postgres=# SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 20000 OR
postgres-# pay_by_quarter[2] = 20000 OR
postgres-# pay_by_quarter[3] = 20000 OR
postgres-# pay_by_quarter[4] = 20000; 
 name  |      pay_by_quarter       |                 schedule                 
-------+---------------------------+------------------------------------------
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(1 行记录)
  • 也可以用下面的语句找出数组中所有元素值都等于 1000 的行:
postgres=# SELECT * FROM sal_emp WHERE 1000 = ALL (pay_by_quarter);
 name |     pay_by_quarter        |                 schedule               
------+---------------------------+------------------------------------------
 Bill | {10000,10000,10000,10000} | {{breakfast,consulting},{meeting,lunch}}
(1 行记录)

16. 复合类型

  • 复合类型表示一行或者一条记录的结构;
  • 它实际上只是一个字段名和它们的数据类型的列表。
  • PostgreSQL 允许像简单数据类型那样使用复合类型。
  • 比如,一个表的某个字段可以声明为一个复合类型。

16.1 声明复合类型

postgres=# CREATE TYPE inventory_item AS (
postgres(# name text,
postgres(# supplier_id integer,
postgres(# price numeric
postgres(# );
CREATE TYPE
  • 用 inventory_item 类型创建表:
postgres=# CREATE TABLE on_hand(
postgres(# item inventory_item,
postgres(# count integer
postgres(# );
CREATE TABLE
  • 插入数据:
postgres=# INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
INSERT 0 1
  • 复合类型值输入:
  • 要以文本常量书写复合类型值,在圆括弧里包围字段值并且用逗号分隔他们。
  • 可以在任何字段值周围放上双引号,如果值本身包含逗号或者圆括弧, 必须用双引号括起。

16.2 访问复合类型

  • 要访问复合类型字段的一个域,需要一个点以及域的名字, 非常类似从一个表名字里选出一个字段
  • 格式:.name
  • 实际上,因为实在太像从表名字中选取字段, 所以经常需要用圆括弧来避免分析器混淆。
  • 比如,你可能需要从on_hand 例子表中选取一些子域,像下面这样:
postgres=# SELECT item.name FROM on_hand WHERE item.price > 9.99;
错误:  对于表"item",丢失FROM子句项
第1行SELECT item.name FROM on_hand WHERE item.price > 9.99;
            ^
  • 这样将不能工作,因为根据 SQL 语法,item 是从一个表名字选取的, 而不是一个字段名字。你必须像下面这样写:
postgres=# SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
 name 
------
(0 行记录)
  • 如果你也需要使用表名字(比如,在一个多表查询里),那么这么写:
postgres=# SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
 name 
------
(0 行记录)
  • 现在圆括弧对象正确地解析为一个指向item字段的引用,然后就可以从中选取子域。

17. 范围类型

  • 范围数据类型代表着某一元素类型在一定范围内的值。

  • PostgreSQL 内置的范围类型有:

  • int4range — integer 的范围;

  • int8range —bigint的范围;

  • numrange —numeric的范围;

  • tsrange —timestamp without time zone的范围;

  • tstzrange —timestamp with time zone的范围;

  • daterange —date的范围;

18. 对象标识符类型

  • PostgreSQL 在内部使用对象标识符(OID)作为各种系统表的主键。
  • 同时,系统不会给用户创建的表增加一个 OID 系统字段(除非在建表时声明了WITH OIDS 或者配置参数default_with_oids设置为开启)。
  • oid 类型代表一个对象标识符。
  • 除此以外 oid 还有几个别名:regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig 和regdictionary。
名字引用描述数值栗子
oid任意数字化的对象标识符564182
regprocpg_proc函数名字sum
regprocedurepg_proc带参数类型的函数sum(int4)
regoperpg_operator操作符名+
regoperatorpg_operator带参数类型的操作符/*(integer,integer) 或 -(NONE,integer)
regclasspg_class关系名pg_type
regtypepg_type数据类型名integer
regconfigpg_ts_config文本搜索配置english
regdictionarypg_ts_dict文本搜索字典simple

19. 伪类型

  • PostgreSQL 类型系统包含一系列特殊用途的条目, 它们按照类别来说叫做伪类型。
  • 伪类型不能作为字段的数据类型, 但是它可以用于声明一个函数的参数或者结果类型。
  • 伪类型在一个函数不只是简单地接受并返回某种 SQL 数据类型的情况下很有用。
名字描述
any表示一个函数接受任何输入数据类型。
anyelement表示一个函数接受任何数据类型。
anyarray表示一个函数接受任意数组数据类型。
anynonarray表示一个函数接受任意非数组数据类型。
anyenum表示一个函数接受任意枚举数据类型。
anyrange表示一个函数接受任意范围数据类型。
cstring表示一个函数接受或者返回一个空结尾的 C 字符串。
internal表示一个函数接受或者返回一种服务器内部的数据类型。
language_handler一个过程语言调用处理器声明为返回 language_handler。
fdw_handler一个外部数据封装器声明为返回 fdw_handler。
record标识一个函数返回一个未声明的行类型。
trigger一个触发器函数声明为返回 trigger。
void表示一个函数不返回数值。
opaque一个已经过时的类型,以前用于所有上面这些用途。

相关文章