PostgreSQL基本操作-EW帮帮网

1 开始

记得在我刚开始工作的时候，最流行的还是MySQL，现在一天铺天盖地都是PostgreSQL。查了一下原因，大概是2个。1，在2009年的时候被Oracle收购，开源社区对这种商业公司不放心。2，2010年，PostgreSQL9突然爆发了。不管是特性还是性能都猛涨，对json，GIS还有时间数据支持更强了，更符合现在业务的需求。所以现在东风压倒西风了。

这里对比一下MySQL和PostgreSQL的特性：

功能	MySQL	PostgreSQL
JSON 支持	支持，但功能相对弱	非常强大，支持 JSONB、索引、函数等
并发控制	行级锁（InnoDB），不支持 MVCC	多版本并发控制（MVCC），性能更好
存储过程/触发器	支持，但功能较弱	更加强大，支持多语言（PL/pgSQL、Python 等）
地理信息（GIS）	有基本支持（MySQL Spatial）	完整支持 PostGIS，是业界最强大的 GIS 数据库
全文搜索	有，但功能有限	内建强大全文搜索功能（支持向量检索）
窗口函数、CTE	新版本支持（8.0+）	早期就支持，成熟稳定
分区表	有支持（8.0+），但灵活性不高	支持更强大，结合继承特性灵活建模
扩展性	插件机制有限	支持用户自定义类型、操作符、索引方法等

应用对比：

场景	更适合的数据库	原因说明
高并发网站、博客系统	MySQL	响应快、部署简单、资源占用少
数据仓库、BI系统	PostgreSQL	查询优化好、支持复杂分析、窗口函数
GIS 地图系统	PostgreSQL（PostGIS）	强大的地理信息支持
事务性金融系统	PostgreSQL	更严格的数据一致性、更强事务处理能力
嵌入式系统、物联网	MySQL（或 SQLite）	MySQL 占用资源小，嵌入容易
对 JSON 支持要求高的系统	PostgreSQL	支持 JSONB 索引、操作符等，结构化数据更强

2 PostgreSQL在物联网的应用

IoT 数据本质上是 时间序列数据（Time-series），PostgreSQL 社区有专业扩展：

✅ TimescaleDB（PostgreSQL 插件）：支持高效写入、压缩、聚合、窗口函数等。
⏱ 插入百万级点位毫不费力，内建时间窗口、聚合、滑动统计等查询语法。

IoT 常用于交通监控、智能农业、智慧城市等场景，需要定位/GIS 支持。

PostgreSQL + PostGIS 提供：

经纬度点/区域存储
轨迹/路径计算
空间索引（R-Tree）
范围查询（例如：找出某一区域内活跃设备）

MySQL 虽然支持 GIS，但功能不够全面，也缺乏深度分析支持。

高并发、高写入能力（结合 TimescaleDB）

PostgreSQL 对并发连接处理更好，支持高吞吐写入。
TimescaleDB 在百亿级数据下依旧保持良好性能。
支持批量插入、分区表、数据压缩等优化策略。

特性	PostgreSQL + Timescale/PostGIS	MySQL
JSON 存储查询	✅ 强	❌ 弱
时序数据优化	✅ 专业插件	❌ 基础
地理空间支持	✅ PostGIS（强大）	❌ 基本
插件生态	✅ 丰富	❌ 有限
高级分析能力	✅ CTE、窗口、聚合函数	❌ 有限

3 PostgreSQL的基操

3.1 安装

sudo apt update
sudo apt install postgresql postgresql-contrib
# 启动服务
sudo systemctl start postgresql

# 设置开机启动
sudo systemctl enable postgresql

# 重启服务
sudo systemctl restart postgresql

查看状态

sudo systemctl status postgresql

3.2 使用

PostgreSQL的用户和linux的用户是一致的。所以默认首先要切换到postgres用户，然后用psql操作。

创建用户：

CREATE USER tommy WITH PASSWORD 'mypassword';

创建数据库：

CREATE DATABASE mydb OWNER tommy;

给用户权限：

GRANT ALL PRIVILEGES ON DATABASE mydb TO tommy;

之后就可以切换到tommy用户来操作。

在psql中的基本操作：

-- 查看所有数据库
\l

-- 切换数据库
\c mydb

-- 查看当前数据库的表
\dt

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT
);

-- 插入数据
INSERT INTO users (name, age) VALUES ('Alice', 30);

-- 查询
SELECT * FROM users;

-- 删除表
DROP TABLE users;

我这边列出的数据库：