PostgreSQL教程FG044-PG布尔/枚举/几何类型：特殊数据类型详解

本文档风哥主要介绍PostgreSQL教程044相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

1. 布尔类型

PostgreSQL的BOOLEAN类型存储逻辑真值，占用1字节空间。

from oracle:www.itpux.com

提示：PostgreSQL支持多种布尔值表示方式，TRUE可以用’true’、’yes’、’on’、’1’表示，FALSE可以用’false’、’no’、’off’、’0’表示。

— 创建布尔类型测试表
CREATE TABLE fgedu_boolean_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
is_active BOOLEAN DEFAULT true,
is_verified BOOLEAN,
is_admin BOOLEAN DEFAULT false
);

— 插入测试数据（多种布尔值表示方式）
INSERT INTO fgedu_boolean_test (name, is_active, is_verified, is_admin) VALUES
(‘用户A’, TRUE, ‘true’, ‘yes’),
(‘用户B’, FALSE, ‘false’, ‘no’),
(‘用户C’, ‘on’, ‘1’, ‘off’),
(‘用户D’, ‘off’, ‘0’, ‘1’),
(‘用户E’, NULL, NULL, false);

— 查询数据
SELECT * FROM fgedu_boolean_test;

执行结果：

id | name | is_active | is_verified | is_admin
—-+——–+———–+————-+———-
1 | 用户A | t | t | t
2 | 用户B | f | f | f
3 | 用户C | t | t | f
4 | 用户D | f | f | t
5 | 用户E | | | f
(5 rows)

布尔运算：

— 布尔运算
SELECT
name,
is_active,
is_verified,
is_active AND is_verified AS both_true,
is_active OR is_verified AS either_true,
NOT is_active AS not_active,
is_active IS TRUE AS is_true,
is_active IS FALSE AS is_false,
is_active IS NULL AS is_null
FROM fgedu_boolean_test;

执行结果：

name | is_active | is_verified | both_true | either_true | not_active | is_true | is_false | is_null
——–+———–+————-+———–+————-+————+———+———-+——–
用户A | t | t | t | t | f | t | f | f
用户B | f | f | f | f | t | f | t | f
用户C | t | t | t | t | f | t | f | f
用户D | f | f | f | f | t | f | t | f
用户E | | | | | t | f | f | t
(5 rows)

布尔聚合函数：

— 布尔聚合函数
SELECT
BOOL_AND(is_active) AS all_active,
BOOL_OR(is_active) AS any_active,
COUNT(*) FILTER (WHERE is_active IS TRUE) AS active_count,
COUNT(*) FILTER (WHERE is_active IS FALSE) AS inactive_count,
COUNT(*) FILTER (WHERE is_active IS NULL) AS null_count
FROM fgedu_boolean_test;

执行结果：

all_active | any_active | active_count | inactive_count | null_count
————+————+————–+—————-+————
f | t | 2 | 2 | 1
(1 row)

2. 枚举类型

枚举类型（ENUM）用于存储一组预定义的字符串值。

— 创建枚举类型
CREATE TYPE fgedu_order_status AS ENUM (
‘pending’, — 待处理
‘confirmed’, — 已确认
‘processing’, — 处理中
‘shipped’, — 已发货
‘delivered’, — 已送达
‘cancelled’ — 已取消
);

CREATE TYPE fgedu_priority AS ENUM (‘low’, ‘medium’, ‘high’, ‘urgent’);

— 查看枚举类型
SELECT enumlabel, enumsortorder
FROM pg_enum
WHERE enumtypid = ‘fgedu_order_status’::regtype
ORDER BY enumsortorder;

执行结果：

创建使用枚举类型的表：

— 创建订单表
CREATE TABLE fgedu_orders (
order_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
order_status fgedu_order_status DEFAULT ‘pending’,
priority fgedu_priority DEFAULT ‘medium’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入测试数据
INSERT INTO fgedu_orders (customer_name, order_status, priority) VALUES
(‘风哥1号’, ‘pending’, ‘high’),
(‘风哥2号’, ‘confirmed’, ‘medium’),
(‘王五’, ‘processing’, ‘urgent’),
(‘赵六’, ‘shipped’, ‘low’),
(‘钱七’, ‘delivered’, ‘medium’);

— 查询数据
SELECT * FROM fgedu_orders;

执行结果：

枚举类型比较：

— 枚举类型比较（按定义顺序）
SELECT
customer_name,
order_status,
order_status > ‘pending’ AS after_pending,
order_status < 'shipped' AS before_shipped FROM fgedu_orders;

执行结果：

customer_name | order_status | after_pending | before_shipped
—————+————–+—————+—————-
风哥1号 | pending | f | t
风哥2号 | confirmed | t | t
王五 | processing | t | t
赵六 | shipped | t | f
钱七 | delivered | t | f
(5 rows)

枚举类型函数：

— 枚举类型函数
SELECT
ENUM_FIRST(NULL::fgedu_order_status) AS first_value,
ENUM_LAST(NULL::fgedu_order_status) AS last_value,
ENUM_RANGE(NULL::fgedu_order_status) AS all_values;

执行结果：

first_value | last_value | all_values
————-+————+———————————————–
pending | cancelled | {pending,confirmed,processing,shipped,delivered,cancelled}
(1 row)

修改枚举类型：

— 添加新的枚举值
ALTER TYPE fgedu_order_status ADD VALUE ‘returned’ AFTER ‘delivered’;

— 查看更新后的枚举值
SELECT enumlabel FROM pg_enum
WHERE enumtypid = ‘fgedu_order_status’::regtype
ORDER BY enumsortorder;

执行结果：

enumlabel
—————
pending
confirmed
processing
shipped
delivered
returned
cancelled
(7 rows)

3. 几何类型概述

PostgreSQL提供了丰富的几何类型，用于存储和操作空间数据。

风哥提示：

— 查看几何类型
SELECT
‘point’ AS type_name,
‘点（x,y）’ AS description,
’16字节’ AS storage
UNION ALL
SELECT ‘line’, ‘无限直线’, ’32字节’
UNION ALL
SELECT ‘lseg’, ‘线段’, ’32字节’
UNION ALL
SELECT ‘box’, ‘矩形’, ’32字节’
UNION ALL
SELECT ‘path’, ‘路径（开放/封闭）’, ’16+16n字节’
UNION ALL
SELECT ‘polygon’, ‘多边形’, ’40+16n字节’
UNION ALL
SELECT ‘circle’, ‘圆’, ’24字节’;

执行结果：

4. 点与线段

POINT类型表示平面上的一个点，LINE表示无限直线，LSEG表示线段。

— 创建点与线段测试表
CREATE TABLE fgedu_points (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
location POINT
);

CREATE TABLE fgedu_lines (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
line_seg LSEG,
infinite_line LINE
);

— 插入点数据
INSERT INTO fgedu_points (name, location) VALUES
(‘原点’, POINT(0, 0)),
(‘点A’, POINT(3, 4)),
(‘点B’, POINT(6, 8)),
(‘点C’, POINT(1, 1));

— 插入线段数据
INSERT INTO fgedu_lines (name, line_seg, infinite_line) VALUES
(‘线段1’, LSEG(POINT(0,0), POINT(3,4)), LINE(POINT(0,0), POINT(1,1))),
(‘线段2’, LSEG(POINT(1,1), POINT(4,5)), LINE(1, -1, 0));

— 查询数据
SELECT * FROM fgedu_points;
SELECT * FROM fgedu_lines;

执行结果：

更多学习教程公众号风哥教程itpux_com

id | name | location
—-+——+———-
1 | 原点 | (0,0)
2 | 点A | (3,4)
3 | 点B | (6,8)
4 | 点C | (1,1)
(4 rows)

id | name | line_seg | infinite_line
—-+——-+—————-+——————-
1 | 线段1 | [(0,0),(3,4)] | {1,-1,0}
2 | 线段2 | [(1,1),(4,5)] | {1,-1,0}
(2 rows)

点与线段运算：

— 点与线段运算
SELECT
p1.name AS point1,
p2.name AS point2,
p1.location <-> p2.location AS distance,
p1.location @> p2.location AS contains
FROM fgedu_points p1
CROSS JOIN fgedu_points p2
WHERE p1.id < p2.id;

执行结果：

point1 | point2 | distance | contains
——–+——–+——————+———-
原点 | 点A | 5 | f
原点 | 点B | 10 | f
原点 | 点C | 1.414213562373095 | f
点A | 点B | 5 | f
点A | 点C | 3.605551275463989 | f
点B | 点C | 7.071067811865476 | f
(6 rows)

5. 矩形与多边形

BOX类型表示矩形，POLYGON类型表示多边形。

— 创建矩形与多边形测试表
CREATE TABLE fgedu_shapes (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
shape_box BOX,
shape_polygon POLYGON
);

— 插入矩形数据
INSERT INTO fgedu_shapes (name, shape_box) VALUES
(‘矩形1’, BOX(POINT(0,0), POINT(4,3))),
(‘矩形2’, BOX(POINT(1,1), POINT(5,4))),
(‘矩形3’, BOX(POINT(2,2), POINT(6,5)));

— 插入多边形数据
INSERT INTO fgedu_shapes (name, shape_polygon) VALUES
(‘三角形’, POLYGON(POINT(0,0), POINT(4,0), POINT(2,3))),
(‘四边形’, POLYGON(POINT(0,0), POINT(4,0), POINT(4,3), POINT(0,3))),
(‘五边形’, POLYGON(POINT(0,2), POINT(2,0), POINT(4,2), POINT(3,4), POINT(1,4)));

— 查询数据
SELECT id, name, shape_box, shape_polygon FROM fgedu_shapes;

执行结果：

id | name | shape_box | shape_polygon
—-+———+—————–+——————————————
1 | 矩形1 | (4,3),(0,0) |
2 | 矩形2 | (5,4),(1,1) |
3 | 矩形3 | (6,5),(2,2) |
4 | 三角形 | | ((0,0),(4,0),(2,3))
5 | 四边形 | | ((0,0),(4,0),(4,3),(0,3))
6 | 五边形 | | ((0,2),(2,0),(4,2),(3,4),(1,4))
(6 rows)

矩形运算：

学习交流加群风哥QQ113257174

— 矩形运算
SELECT
s1.name AS rect1,
s2.name AS rect2,
s1.shape_box && s2.shape_box AS overlaps,
s1.shape_box @> s2.shape_box AS contains,
s1.shape_box <-> s2.shape_box AS distance
FROM fgedu_shapes s1
CROSS JOIN fgedu_shapes s2
WHERE s1.id < s2.id AND s1.shape_box IS NOT NULL AND s2.shape_box IS NOT NULL;

执行结果：

rect1 | rect2 | overlaps | contains | distance
——-+——-+———-+———-+———-
矩形1 | 矩形2 | t | f | 0
矩形1 | 矩形3 | t | f | 0
矩形2 | 矩形3 | t | f | 0
(3 rows)

6. 圆与路径

CIRCLE类型表示圆，PATH类型表示路径。

— 创建圆与路径测试表
CREATE TABLE fgedu_circles (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
circle_shape CIRCLE
);

CREATE TABLE fgedu_paths (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
path_shape PATH,
is_closed BOOLEAN
);

— 插入圆数据
INSERT INTO fgedu_circles (name, circle_shape) VALUES
(‘圆1’, CIRCLE(POINT(0,0), 5)),
(‘圆2’, CIRCLE(POINT(3,4), 3)),
(‘圆3’, CIRCLE(POINT(10,10), 8));

— 插入路径数据
INSERT INTO fgedu_paths (name, path_shape, is_closed) VALUES
(‘开放路径1’, PATH(POINT(0,0), POINT(3,4), POINT(6,0)), false),
(‘封闭路径1’, PATH(POINT(0,0), POINT(4,0), POINT(4,3), POINT(0,3), POINT(0,0)),
true);

— 查询数据
SELECT * FROM fgedu_circles;
SELECT * FROM fgedu_paths;

执行结果：

id | name | circle_shape
—-+——+——————
1 | 圆1 | <(0,0),5>
2 | 圆2 | <(3,4),3>
3 | 圆3 | <(10,10),8>
(3 rows)

圆运算：

— 圆运算
SELECT
c1.name AS circle1,
c2.name AS circle2,
c1.circle_shape && c2.circle_shape AS overlaps,
c1.circle_shape @> c2.circle_shape AS contains,
c1.circle_shape <-> c2.circle_shape AS distance
FROM fgedu_circles c1
CROSS JOIN fgedu_circles c2
WHERE c1.id < c2.id;

执行结果：

circle1 | circle2 | overlaps | contains | distance
———+———+———-+———-+——————-
圆1 | 圆2 | t | f | 0
圆1 | 圆3 | f | f | 9.142135623730951
圆2 | 圆3 | f | f | 4.201194710321741
(3 rows)

7. 几何运算与函数

常用几何函数：

— 几何计算函数
SELECT
AREA(BOX(POINT(0,0), POINT(4,3))) AS box_area,
AREA(CIRCLE(POINT(0,0), 5)) AS circle_area,
CENTER(BOX(POINT(0,0), POINT(4,3))) AS box_center,
DIAMETER(CIRCLE(POINT(0,0), 5)) AS circle_diameter,
RADIUS(CIRCLE(POINT(0,0), 5)) AS circle_radius;

执行结果：

box_area | circle_area | box_center | circle_diameter |
circle_radius
——————-+———————-+————+—————–+—————
12.00000000000000 | 78.5398163397448300 | (2,1.5) | 10 | 5
(1 row)

几何边界函数：

— 几何边界函数
SELECT
BOX(CIRCLE(POINT(0,0), 5)) AS circle_bounding_box,
BOUNDARY(BOX(POINT(0,0), POINT(4,3))) AS box_boundary,
NPOINTS(POLYGON(POINT(0,0), POINT(4,0), POINT(2,3))) AS
polygon_points;

执行结果：

circle_bounding_box | box_boundary | polygon_points
———————+——————–+—————-
(5,5),(-5,-5) | ((0,0),(4,0),(4,3),(0,3),(0,0)) | 3
(1 row)

几何转换函数：

学习交流加群风哥微信: itpux-com

— 几何转换函数
SELECT
POLYGON(CIRCLE(POINT(0,0), 3)) AS circle_to_polygon,
CIRCLE(POLYGON(POINT(0,0), POINT(3,0), POINT(3,3), POINT(0,3)))
AS polygon_to_circle;

执行结果：

更多视频教程www.fgedu.net.cn

circle_to_polygon | polygon_to_circle
——————————————————————————————-+————————
((3,0),(2.897777478867072,0.776457135308076),(2.598076211353316,1.5),…
| <(1.5,1.5),2.121320343>
(1 row)

8. 实战案例：位置服务

创建位置服务系统：

— 创建商店表
CREATE TABLE fgedu_stores (
store_id SERIAL PRIMARY KEY,
store_name VARCHAR(100),
address VARCHAR(200),
location POINT,
delivery_range CIRCLE,
is_active BOOLEAN DEFAULT true
);

— 创建用户位置表
CREATE TABLE fgedu_fgedu_locations (
loc_id SERIAL PRIMARY KEY,
fgedu_id INTEGER,
fgedu_name VARCHAR(100),
current_location POINT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入商店数据
INSERT INTO fgedu_stores (store_name, address, location,
delivery_range) VALUES
(‘风哥便利店1号店’, ‘北京市海淀区中关村大街1号’, POINT(116.31, 39.98),
CIRCLE(POINT(116.31, 39.98), 0.05)),
(‘风哥便利店2号店’, ‘北京市朝阳区建国门外大街’, POINT(116.45, 39.92),
CIRCLE(POINT(116.45, 39.92), 0.05)),
(‘风哥便利店3号店’, ‘北京市西城区金融街’, POINT(116.36, 39.91),
CIRCLE(POINT(116.36, 39.91), 0.03)),
(‘风哥超市旗舰店’, ‘北京市东城区王府井大街’, POINT(116.41, 39.92),
CIRCLE(POINT(116.41, 39.92), 0.08));

— 插入用户位置数据
INSERT INTO fgedu_fgedu_locations (fgedu_id, fgedu_name,
current_location) VALUES
(1, ‘风哥1号’, POINT(116.32, 39.97)),
(2, ‘风哥2号’, POINT(116.46, 39.93)),
(3, ‘王五’, POINT(116.35, 39.90)),
(4, ‘赵六’, POINT(116.42, 39.91));

执行结果：

INSERT 0 4
INSERT 0 4

查找附近商店：

— 查找用户附近的商店（在配送范围内）
SELECT
u.fgedu_name,
u.current_location,
s.store_name,
s.address,
ROUND((u.current_location <-> s.location)::NUMERIC, 4) AS
distance
FROM fgedu_fgedu_locations u
CROSS JOIN fgedu_stores s
WHERE s.is_active = true
AND u.current_location <@ s.delivery_range ORDER BY u.fgedu_name, distance;

执行结果：

计算最近商店：

— 为每个用户找最近的商店
SELECT DISTINCT ON (u.fgedu_name)
u.fgedu_name,
u.current_location,
s.store_name,
s.address,
ROUND((u.current_location <-> s.location)::NUMERIC,
4) AS distance
FROM fgedu_fgedu_locations u
CROSS JOIN fgedu_stores s
WHERE s.is_active = true
ORDER BY u.fgedu_name, u.current_location <->
s.location;

执行结果：

配送范围分析：

— 分析商店配送范围重叠
SELECT
s1.store_name AS store1,
s2.store_name AS store2,
s1.delivery_range && s2.delivery_range AS
ranges_overlap,
ROUND((s1.location <-> s2.location)::NUMERIC, 4) AS
distance_between
FROM fgedu_stores s1
CROSS JOIN fgedu_stores s2
WHERE s1.store_id < s2.store_id ORDER BY distance_between;

执行结果：

9. 清理环境

清理测试表和类型：

— 删除所有测试表
DROP TABLE IF EXISTS fgedu_boolean_test;
DROP TABLE IF EXISTS fgedu_orders;
DROP TABLE IF EXISTS fgedu_points;
DROP TABLE IF EXISTS fgedu_lines;
DROP TABLE IF EXISTS fgedu_shapes;
DROP TABLE IF EXISTS fgedu_circles;
DROP TABLE IF EXISTS fgedu_paths;
DROP TABLE IF EXISTS fgedu_stores;
DROP TABLE IF EXISTS
fgedu_fgedu_locations;

— 删除枚举类型
DROP TYPE IF EXISTS fgedu_order_status;
DROP TYPE IF EXISTS fgedu_priority;

执行结果：

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TYPE
DROP TYPE

风哥教程风哥教程风哥教程总结：PostgreSQL提供了丰富的特殊数据类型：布尔类型用于逻辑判断，枚举类型用于存储预定义值，几何类型用于空间数据处理。布尔类型支持多种表示方式和聚合函数；枚举类型按定义顺序比较，支持动态添加值；几何类型支持点、线、矩形、圆、多边形等，可用于位置服务和空间分析。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG044-PG布尔/枚举/几何类型：特殊数据类型详解

1. 布尔类型

2. 枚举类型

3. 几何类型概述

4. 点与线段

5. 矩形与多边形

6. 圆与路径

7. 几何运算与函数

8. 实战案例：位置服务

9. 清理环境

相关推荐

联系我们