目录

  • 1.字符串类型：char(n)，varchar(n)，text
  • 2.数值类型：smallint，int，bigint，decimal，numeric，real，double precision，smallserial，serial，bigserial
  • 3.时间和日期类型：date，time，timestamp，interval
  • 4.布尔类型：boolean
  • 5.枚举类型：enum
  • 6.复合类型：composite
  • 7.数组类型：array
  • 声明数组：
  • 插入值：
  • 访问数组：
  • 修改数组
  • 数组中检索
  • 8.网络地址类型：cidr，inet，macaddr，macaddr8
  • 9.几何类型：point，line，lseg，box，path，polygon，circle
  • 10.JSON类型：json，jsonb
  • 11.其他类型


1.字符串类型：char(n)，varchar(n)，text

数据类型

别名

说明

character(n)

char(n)

定长字符串，不足补空格

character varying(n)

varchar(n)

变长字符串

text

变长字符串

2.数值类型：smallint，int，bigint，decimal，numeric，real，double precision，smallserial，serial，bigserial

数据类型

别名

说明

范围

smallint

int2

有符号2字节整数

:-32768 到 +32767

integer

int、int4

有符号4字节整数

:-2147483648 到 +2147483647

bigint

int8

有符号8字节整数

:-9223372036854775808 到 +9223372036854775807

decimal(p,s)

可选精度的精确数字

小数点前 131072 位；小数点后 16383 位

numeric(p,s)

可选精度的精确数字

小数点前 131072 位；小数点后 16383 位

real

float4

单精度浮点数（4字节）

6 位十进制数字精度

double precision

float8

双精度浮点数（8字节）

15 位十进制数字精度

smallserial

serial2

自增2字节整数

1 到 32767

serial

serial4

自增4字节整数

1 到 2147483647

bigserial

serial8

自增8字节整数

1 到 9223372036854775807

3.时间和日期类型：date，time，timestamp，interval

数据类型

别名

说明

date

日历日期（年月日）

time(n) without time zone

time(n)

时间（无时区）

time(n) with time zone

timetz

时间，包括时区

timestamp(n) without time zone

timestamp(n)

日期和时间（无时区）

timestamp(n) with time zone

timestamptz

日期和时间，包括时区

interval fields(n)

interval(n)

时间跨度

4.布尔类型：boolean

数据类型

别名

说明

boolean

bool

逻辑布尔值（真/假）

5.枚举类型：enum

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

  • PostgreSQL 中的枚举类型类似于 C 语言中的 enum类型。
  • 与其他类型不同的是，枚举类型需要使用 CREATE TYPE 命令创建的。
  • 像其他类型一样，一旦创建，枚举类型可以用于表和函数定义。

创建枚举类型：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

使用枚举类型：

CREATE TABLE person (
	name text,
    current_mood mood
);

插入数据：

INSERT INTO person VALUES ('Moe', 'happy');

查询数据：

SELECT * FROM person WHERE current_mood = 'happy';

查询结果：

name | current_mood 
------+--------------
 Moe  | happy
(1 row)

6.复合类型：composite

复合类型，也叫组合类型，将简单类型组合在一起，形成自定义类型。

  • 数据表的列可以定义为组合类型。

创建复合类型，声明类型包含的字段和字段类型：

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

将表字段声明为组合类型：

CREATE TABLE on_hand (
    item    inventory_item,
    count   integer
);

插入数据：

（ROW 语句用于构建组合类型的值。）

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

查询数据：

SELECT * FROM on_hand;

查询结果：

item          | count
 ------------------------+-------
 ("fuzzy dice",42,1.99) |  1000

补充：创建数据表的同时，会创建同名的 TYPE，如下所示：

-- 建表相当于同时执行了 CREATE TYPE complex AS (r double precision, i double precision);
CREATE TABLE complex (r double precision, i double precision);


-- 使用复合类型 complex
CREATE TABLE my_complex (name text, value complex);

-- 插入数据
INSERT INTO my_complex (name, value) VALUES ('one', ROW(1.0, 1.0));

-- 查询数据
SELECT * FROM my_complex;

-- 查询结果
name | value
------+-------
one  | (1,1)

查询复合类型，用()包围复合类型的列名，用.指向复合类型的 “域”：

SELECT (value).r FROM my_complext WHERE (value).i = 1;

查询结果：

r
---
1

7.数组类型：array

PostgreSQL 允许将字段定义为变长的多维数组。

数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

声明数组：

创建表的时候，我们可以声明数组，方式如下：

CREATE TABLE sal_emp {
	name            text,
	pay_by_quarter  integer[],
	schedule        text[][]
};

pay_by_quarter 为一维整型数组，schedule 为二维文本类型数组。

我们也可以使用 “ARRAY” 关键字，如下所示：

CREATE TABLE sal_emp (
	name            text,
    pay_by_quarter  integer ARRAY[4],
    schedule        text[][]
);

插入值：

插入值使用花括号，元素在 {} 使用逗号隔开：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

访问数组：

现在我们可以在这个表上运行一些查询。

首先，我们演示如何访问数组的一个元素。这个查询检索在第二季度薪水变化的雇员名：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

查询结果：

name
-------
 Carol
(1 row)

数组的下标数字是写在方括弧内的。

修改数组

我们可以对数组的值进行修改：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

或者使用 ARRAY 构造器语法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

数组中检索

要搜索一个数组中的数值，你必须检查该数组的每一个值。

比如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

或者，可以使用 generate_subscripts 函数。例如：

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

8.网络地址类型：cidr，inet，macaddr，macaddr8

数据类型

别名

说明

cidr

IPv4 或 IPv6 网络地址

inet

IPv4 或 IPv6 主机地址

macaddr

MAC（媒体访问控制）地址

macaddr8

MAC（媒体访问控制）地址（EUI-64格式）

9.几何类型：point，line，lseg，box，path，polygon，circle

数据类型

别名

说明

point

平面上的几何点

line

平面上的无限线

lseg

平面上的线段

box

平面上的长方形盒子

path

平面上的几何路径

polygon

平面上的闭合几何路径

circle

在平面上画圈

10.JSON类型：json，jsonb

数据类型

别名

说明

json

文本 JSON 数据

jsonb

二进制 JSON 数据，分解

11.其他类型

数据类型

别名

说明

bit(n)

定长位串

bit varying(n)

varbit(n)

变长位串

bytea

二进制数据（“字节数组”）

money

货币金额

pg_lsn

PostgreSQL日志序列号

pg_snapshot

用户级事务 ID 快照

tsquery

文本搜索查询

tsvector

文本搜索文档

txid_snapshot

用户级事务 ID 快照（已弃用；请参阅pg_snapshot）

uuid

通用唯一标识符

xml

XML 数据

此外，PostgreSQL还支持自定义数据类型和扩展数据类型。自定义数据类型允许用户创建自己的数据类型，而扩展数据类型则是一种插件机制，允许第三方开发人员扩展 PostgreSQL 的数据类型。