Postgresql 简明教程

PostgreSQL - Data Type

在本章中，我们将讨论 PostgreSQL 中使用的数据类型。创建表时，对于每一列，您需要指定一种数据类型，即您希望存储在表字段中的数据类型。

这有几个好处：

  1. Consistency - 相同数据类型列上的操作提供一致的结果，并且通常是最快的。

  2. Validation - 正确使用数据类型意味着在数据类型范围内验证数据格式并拒绝数据。

  3. Compactness - 由于一列只可以存储一种类型的值，所以它将以紧凑的方式存储。

  4. Performance - 正确使用数据类型可提供最高效的数据存储。存储的值可以快速处理，从而提高性能。

PostgreSQL 支持多种数据类型。此外，用户可以使用 CREATE TYPE SQL 命令创建自己的自定义数据类型。PostgreSQL 中有不同的数据类型类别。它们将在下面讨论。

Numeric Types

数值类型由两字节、四字节和八字节整数、四字节和八字节浮点数以及可选精度的十进制数组成。下表列出了可用的类型。

Name

Storage Size

Description

Range

smallint

2 bytes

small-range integer

-32768 to +32767

integer

4 bytes

typical choice for integer

-2147483648 to +2147483647

bigint

8 bytes

large-range integer

-9223372036854775808 to 9223372036854775807

decimal

variable

user-specified precision,exact

小数点前最多 131072 位数字；小数点后最多 16383 位数字

numeric

variable

user-specified precision,exact

小数点前最多 131072 位数字；小数点后最多 16383 位数字

real

4 bytes

variable-precision,inexact

6 decimal digits precision

double precision

8 bytes

variable-precision,inexact

15 decimal digits precision

smallserial

2 bytes

small autoincrementing integer

1 to 32767

serial

4 bytes

autoincrementing integer

1 to 2147483647

bigserial

8 bytes

large autoincrementing integer

1 to 9223372036854775807

Monetary Types

money 类型以固定的分数精度存储货币金额。numeric、int 和 bigint 数据类型的值可以转换为 money。由于存在舍入误差的可能性，因此不建议使用浮点数来处理货币。

Name

Storage Size

Description

Range

money

8 bytes

currency amount

-92233720368547758.08 to +92233720368547758.07

Character Types

下表列出了 PostgreSQL 中提供的通用字符类型。

S. No.

Name & Description

1

character varying(n), varchar(n) 带有长度限制的可变长度

2

character(n), char(n) fixed-length, blank padded

3

text variable unlimited length

Binary Data Types

bytea 数据类型允许存储二进制字符串，如下表所示。

Name

Storage Size

Description

bytea

1 或 4 个字节加上实际的二进制字符串

variable-length binary string

Date/Time Types

PostgreSQL 支持全套 SQL 日期和时间类型，如下表所示。日期根据公历计算。在此，所有类型的分辨率为 1 microsecond / 14 digits ，除了 date 类型，其分辨率为 day 。

Name

Storage Size

Description

Low Value

High Value

timestamp [(p)] [without time zone ]

8 bytes

日期和时间（没有时区）

4713 BC

294276 AD

TIMESTAMPTZ

8 bytes

日期和时间，有时区

4713 BC

294276 AD

date

4 bytes

日期（无时间）

4713 BC

5874897 AD

time [ (p)] [ without time zone ]

8 bytes

时间（无日期）

00:00:00

24:00:00

time [ (p)] with time zone

12 bytes

仅时间，带时区

00:00:00+1459

24:00:00-1459

interval [fields ] [(p) ]

12 bytes

time interval

-178000000 years

178000000 years

Boolean Type

PostgreSQL 提供标准 SQL 类型 Boolean。Boolean 数据类型可以具有 true、false 状态以及第三个状态 unknown，它由 SQL null 值表示。

Name

Storage Size

Description

boolean

1 byte

true 或 false 状态

Enumerated Type

枚举 (enum) 类型是一种包含静态有序值集的数据类型。它们相当于许多编程语言中支持的枚举类型。

与其他类型不同，枚举类型需要使用 CREATE TYPE 命令来创建。此类型用于存储静态有序值集。例如，罗盘方向（即 NORTH、SOUTH、EAST 和 WEST）或如下所示的星期几：

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

枚举创建后，可以像任何其他类型一样使用。

Geometric Type

几何数据类型表示二维空间对象。最基本类型，点，构成了所有其他类型。

Name

Storage Size

Representation

Description

point

16 bytes

Point on a plane

(x,y)

line

32 bytes

无限线（未完全实现）

x1,y1),(x2,y2

lseg

32 bytes

Finite line segment

x1,y1),(x2,y2

box

32 bytes

Rectangular box

x1,y1),(x2,y2

path

16+16n bytes

闭合路径（类似多边形）

((x1,y1),…​)

path

16+16n bytes

Open path

[(x1,y1),…​]

polygon

40+16n

多边形（类似闭合路径）

((x1,y1),…​)

circle

24 bytes

Circle

<(x,y),r>（圆心和半径）

Network Address Type

PostgreSQL 提供数据类型来存储 IPv4、IPv6 和 MAC 地址。最好使用这些类型而不是纯文本类型来存储网络地址，因为这些类型提供了输入错误检查和专用运算符和函数。

Name

Storage Size

Description

cidr

7 or 19 bytes

IPv4 and IPv6 networks

inet

7 or 19 bytes

IPv4 和 IPv6 主机和网络

macaddr

6 bytes

MAC addresses

Bit String Type

二进制字符串类型用于存储位掩码。它们可能是 0 或 1。有两种 SQL 位类型： bit(n) 和 bit varying(n) ，其中 n 是一个正整数。

Text Search Type

此类型支持全文搜索，它是一种在自然语言文档集合中搜索最能匹配查询的文档的活动。为此，有两种数据类型：

S. No.

Name & Description

1

tsvector 这是经过标准化以合并相同单词的不同变体（称为“词素”）的唯一单词的有序列表。

2

tsquery 它存储要搜索的词素，并将它们与布尔运算符 &（AND）匹配。

UUID Type

UUID（通用唯一标识符）被写成一串小写十六进制数字，分为几组，并用连字符分隔，具体来说，一组八位数字，后面跟着三组四位数字，再跟着一组 12 位数字，总共 32 位数字表示 128 位。

UUID 的一个示例是 - 550e8400-e29b-41d4-a716-446655440000

XML Type

XML 数据类型可用于存储 XML 数据。要存储 XML 数据，首先必须使用 xmlparse 函数创建 XML 值，如下所示：

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

JSON Type

json 数据类型可用于存储 JSON（JavaScript 对象表示法）数据。此类数据也可以存储为文本，但 json 数据类型具有检查每个存储值是有效 JSON 值的优点。还有可用的相关支持函数，可直接用于处理 JSON 数据类型，如下所示。

Example

Example Result

array_to_json('{{1,5},{99,100}}'::int[])

[[1,5],[99,100]]

row_to_json(row(1,'foo'))

{"f1":1,"f2":"foo"}

Array Type

PostgreSQL 提供将表的列定义为变长多维数组的机会。任何内置或用户定义的基本类型、枚举类型或复合类型的数组都可以创建。

Declaration of Arrays

数组类型可以声明为

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

或使用关键字“ARRAY”如下声明：

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

Inserting values

数组值可以作为一个文字常量插入，用大括号将元素值括起来，并用逗号分隔。以下是一个示例：

INSERT INTO monthly_savings
VALUES (‘Manisha’,
‘{20000, 14600, 23500, 13250}’,
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

Accessing Arrays

下面显示访问数组的示例。下面给出的命令将选择第二季度储蓄高于第四季度的那些人。

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Modifying Arrays

下面显示修改数组的示例。

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

或使用 ARRAY 表达式语法 −

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

Searching Arrays

下面显示搜索数组的示例。

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

如果数组已知的大小，则可以使用上面给出的搜索方法。否则，以下示例显示了在不知道大小的情况下如何搜索。

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Composite Types

此类型表示字段名称及其数据类型的列表，即表的行或记录的结构。

Declaration of Composite Types

以下示例显示如何声明复合类型

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

可以在创建表中使用此数据类型，如下所示 −

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Composite Value Input

复合值可以作为文字常量插入，将字段值括在括号内并用逗号分隔它们。示例如下所示 −

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

这对于上述定义的 inventory_item 有效。只要表达式中有多个字段，ROW 关键字实际上是可选的。

Accessing Composite Types

要访问复合列的字段，请使用一个点，后接字段名称，很像从表名中选择一个字段。例如，要从我们的 on_hand 示例表中选择一些子字段，查询如下所示 −

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

你甚至可以使用表名（比如在多表查询中），如下所示 −

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Range Types

范围类型表示使用数据范围的数据类型。范围类型可以是离散范围（例如，所有整数值 1 到 10）或连续范围（例如，上午 10:00 到上午 11:00 之间的任何时间点）。

可用的内置范围类型包括以下范围 −

  1. int4range − 整数范围

  2. int8range − bigint 范围

  3. numrange − numeric 范围

  4. tsrange − 无时区的 timestamp 范围

  5. tstzrange − 时区的 timestamp 范围

  6. daterange − 日期范围

可以创建自定义范围类型以使新类型的范围可用，例如将 inet 类型用作基础的 IP 地址范围，或将 float 数据类型用作基础的 float 范围。

范围类型使用 [ ] 和 ( ) 字符分别支持包含和不包含的范围边界。例如，“[4,9)” 表示从 4 及其本身开始至但不到 9 的所有整数。

Object Identifier Types

对象标识符 (OID) 由 PostgreSQL 在内部用作各种系统表的初级关键字。如果指定了 WITH OIDS 或启用了 default_with_oids 配置变量，则只有在这些情况下，才将 OID 添加到用户创建的表中。下表列出多条别名类型。除了专门的输入和输出例程外，OID 别名类型没有自己的操作。

Name

References

Description

Value Example

oid

any

numeric object identifier

564182

regproc

pg_proc

function name

sum

regprocedure

pg_proc

function with argument types

sum(int4)

regoper

pg_operator

operator name

+

regoperator

pg_operator

operator with argument types

*(integer,integer) or -(NONE,integer)

regclass

pg_class

relation name

pg_type

regtype

pg_type

data type name

integer

regconfig

pg_ts_config

text search configuration

English

regdictionary

pg_ts_dict

text search dictionary

simple

Pseudo Types

PostgreSQL 类型系统包含许多特殊用途的条目，这些条目统称为伪类型。伪类型不能用作列数据类型，但可以用作声明函数的参数或结果类型。

下表列出了一些现有的伪类型。

S. No.

Name & Description

1

any 表示函数接受任何输入数据类型。

2

anyelement 表示函数接受任何数据类型。

3

anyarray 表示函数接受任何数组数据类型。

4

anynonarray 表示函数接受任何非数组数据类型。

5

anyenum 表示函数接受任何枚举数据类型。

6

anyrange 表示函数接受任何范围数据类型。

7

cstring 表示函数接受或返回一个以 NULL 结尾的 C 字符串。

8

internal 表示函数接受或返回一个服务器内部数据类型。

9

声明了一个过程语言调用处理程序返回 language_handler。

10

fdw_handler 声明了一个外部数据包装器处理程序返回 fdw_handler。

11

record 标识一个返回未指定行类型的函数。

12

trigger 声明了一个触发器函数返回 trigger。

13

void 表示函数没有返回值。