PostgreSQL
# env
以安装 16 版本为例子
brew search postgresqlbrew install postgresql@16brew services start postgresql@16export PATH="/usr/local/opt/postgresql@16/bin:$PATH"psql --version: 验证
# 入门指导
# 检查 PostgreSQL 服务状态
# 或者查看 Homebrew 安装信息
brew info postgresql@16
# 或
brew info postgresql
# 检查 PostgreSQL 是否在运行
brew services list | grep postgresql
# 或者检查进程
ps aux | grep postgres
# 检查 PostgreSQL 版本和安装路径
psql --version
which psql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# 数据库初始化
# 1. 停止 PostgreSQL 服务（如果正在运行）
brew services stop postgresql@16
# 或
brew services stop postgresql
# 2. 初始化数据库集群
initdb /usr/local/var/postgresql@16
# 或根据你的安装路径
initdb $(brew --prefix)/var/postgresql@16
# 2.1 也可以选择重新初始化
initdb -D /usr/local/var/postgresql@16 --locale=C --encoding=UTF8
# 或
initdb -D $(brew --prefix)/var/postgresql@16 --locale=C --encoding=UTF8
# 3. 启动服务
brew services start postgresql@16
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 使用 createdb 命令（如果服务已运行但无用户）
# 1. 检查是否有任何可用的数据库
psql -l
# 2. 如果服务运行但没有用户，尝试使用系统用户连接
psql -d postgres
# 3. 如果上面失败，尝试直接连接（不指定用户）
psql
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 创建用户&数据库
# 1. 尝试使用系统用户连接（Homebrew 安装的 PostgreSQL 通常允许）
psql -d postgres
# 2. 如果成功连接，创建 postgres 用户
CREATE ROLE postgres WITH LOGIN SUPERUSER;
CREATE DATABASE postgres OWNER postgres;
# 3. 或者创建你的用户
CREATE ROLE yingci WITH LOGIN SUPERUSER;
CREATE DATABASE yingci OWNER yingci;
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# psql 命令
psql: PostgreSQL interactive terminal
help\h: 显示 SQL 命令的说明\copyright: 显示发行条款\?显示 pgsql 命令的说明\g或者以分号(;)结尾以执行查询
\du: 查看所有用户\l: 查看所有数据库\c {dbname}: 切换当前数据库\dt: display tables\d {tablename}: 查看指定表SHOW data_directory;: 查看数据目录\connect {user_name};: 切换到您的用户名\conninfo: 查看当前连接的完整信息\q: quit
# pgsql 命令说明
psql --help: help, 查看使用 option 等
# psql
# \?
一般性
\bind [PARAM]... set query parameters
\copyright 显示PostgreSQL的使用和发行许可条款
\crosstabview [COLUMNS] execute query and display result in crosstab
\errverbose 以最冗长的形式显示最近的错误消息
\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);
\g with no arguments is equivalent to a semicolon
\gdesc 描述查询结果,而不执行它
\gexec 执行策略,然后执行其结果中的每个值
\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] 就像\g,但强制扩展输出模式
\q 退出 psql
\watch [[i=]SEC] [c=N] execute query every SEC seconds, up to N times
帮助
\? [commands] 显示反斜线命令的帮助
\? options 显示 psql 命令行选项的帮助
\? variables 显示特殊变量的帮助
\h [NAME] SQL命令语法上的说明,用*显示全部命令的语法说明
查询缓存区
\e [FILE] [LINE] 使用外部编辑器编辑查询缓存区(或文件)
\ef [FUNCNAME [LINE]] 使用外部编辑器编辑函数定义
\ev [VIEWNAME [LINE]] 用外部编辑器编辑视图定义
\p 显示查询缓存区的内容
\r 重置(清除)查询缓存区
\s [文件] 显示历史记录或将历史记录保存在文件中
\w 文件 将查询缓存区的内容写入文件
输入/输出
\copy ... 执行 SQL COPY,将数据流发送到客户端主机
\echo [-n] [STRING] 将字符串写到标准输出(-n表示没有换行符)
\i 文件 从文件中执行命令
\ir FILE 与 \i类似, 但是相对于当前脚本的位置
\o [文件] 将全部查询结果写入文件或 |管道
\qecho [-n] [STRING] 将字符串写入\o输出流(-n表示无换行)
\warn [-n] [STRING] 将字符串写入标准错误(-n 表示无换行)
条件
\if EXPR 开始条件块
\elif EXPR 当前条件块内的备选方案
\else 当前条件块内的最终备选方案
\endif 条件块的结尾
资讯性
(选项: S = 显示系统对象, + = 其余的详细信息)
\d[S+] 列出表,视图和序列
\d[S+] 名称 描述表,视图,序列,或索引
\da[S] [模式] 列出聚合函数
\dA[+] [模式] 列出访问方法
\dAc[+] [AMPTRN [TYPEPTRN]] 列出运算符
\dAf[+] [AMPTRN [TYPEPTRN]] 列出运算符集合
\dAo[+] [AMPTRN [OPFPTRN]] 列出运算符集合
\dAp[+] [AMPTRN [OPFPTRN]] 列出运算符集合所支持的功能
\db[+] [模式] 列出表空间
\dc[S+] [模式] 列表转换
\dconfig[+] [PATTERN] list configuration parameters
\dC[+] [模式] 列出类型强制转换
\dd[S] [模式] 显示没有在别处显示的对象描述
\dD[S+] [模式] 列出共同值域
\ddp [模式] 列出默认权限
\dE[S+] [模式] 列出引用表
\des[+] [模式] 列出外部服务器
\det[+] [模式] 列出引用表
\deu[+] [模式] 列出用户映射
\dew[+] [模式] 列出外部数据封装器
\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]
列出 [only agg/normal/procedure/trigger/window] 函数
\dF[+] [模式] 列出文本搜索配置
\dFd[+] [模式] 列出文本搜索字典
\dFp[+] [模式] 列出文本搜索解析器
\dFt[+] [模式] 列出文本搜索模版
\dg[S+] [模式] 列出角色
\di[S+] [模式] 列出索引
\dl[+] list large objects, same as \lo_list
\dL[S+] [模式] 列出所有过程语言
\dm[S+] [模式] 列出所有物化视图
\dn[S+] [模式] 列出所有模式
\do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]
列出运算符
\dO[S+] [模式] 列出所有校对规则
\dp[S] [PATTERN] list table, view, and sequence access privileges
\dP[itn+] [PATTERN] 列出[仅表/索引]分区关系[n=nested]
\drds [ROLEPTRN [DBPTRN]] list per-database role settings
\drg[S] [PATTERN] list role grants
\dRp[+] [模式] 列出复制发布
\dRs[+] [模式] 列出复制订阅
\ds[S+] [模式] 列出序列
\dt[S+] [模式] 列出表
\dT[S+] [模式] 列出数据类型
\du[S+] [模式] 列出角色
\dv[S+] [模式] 列出视图
\dx[+] [模式] 列出扩展
\dX [PATTERN] 列出扩展统计信息
\dy[+] [PATTERN] 列出所有事件触发器
\l[+] [模式] 列出所有数据库
\sf[+] FUNCNAME 显示一个函数的定义
\sv[+] VIEWNAME 显示一个视图的定义
\z[S] [PATTERN] same as \dp
大对象
\lo_export LOBOID FILE write large object to file
\lo_import FILE [COMMENT]
read large object from file
\lo_list[+] list large objects
\lo_unlink LOBOID delete a large object
格式化
\a 在非对齐模式和对齐模式之间切换
\C [字符串] 设置表的标题,或如果没有的标题就取消
\f [字符串] 显示或设定非对齐模式查询输出的字段分隔符
\H 切换HTML输出模式 (目前是 关闭)
\pset [NAME [VALUE]] 设置表输出选项
(border|columns|csv_fieldsep|expanded|fieldsep|
fieldsep_zero|footer|format|linestyle|null|
numericlocale|pager|pager_min_lines|recordsep|
recordsep_zero|tableattr|title|tuples_only|
unicode_border_linestyle|unicode_column_linestyle|
unicode_header_linestyle
\t [开|关] 只显示记录 (目前是关闭)
\T [字符串] 设置HTML <表格>标签属性, 或者如果没有的话取消设置
\x [on|off|auto] 切换扩展输出模式(目前是 关闭)
连接
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
连接到新数据库(当前是"yangjianfei")
\conninfo 显示当前连接的相关信息
\encoding [编码名称] 显示或设定客户端编码
\password [USERNAME] 安全地为用户更改口令
操作系统
\cd [目录] 更改目前的工作目录
\getenv PSQLVAR ENVVAR fetch environment variable
\setenv NAME [VALUE] 设置或清空环境变量
\timing [开|关] 切换命令计时开关 (目前是关闭)
\! [命令] 在 shell中执行命令或启动一个交互式shell
变量
\prompt [文本] 名称 提示用户设定内部变量
\set [名称 [值数]] 设定内部变量,若无参数则列出全部变量
\unset 名称 清空(删除)内部变量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
# 创建与连接
USER_NAME=$(whoami)
psql -U $USER_NAME -d postgres -c "CREATE DATABASE $USER_NAME;"
psql -U $USER_NAME -d $USER_NAME
1
2
3
2
3
# 用户管理
# -- 1. 创建超级用户（完整权限）
CREATE ROLE admin WITH LOGIN SUPERUSER PASSWORD 'strong_password';
# -- 2. 创建普通用户（有限权限）
CREATE ROLE app_user WITH LOGIN PASSWORD 'password123';
# -- 3. 创建只能创建数据库的用户
CREATE ROLE db_creator WITH LOGIN CREATEDB PASSWORD 'password';
# -- 4. 创建可以创建其他用户的用户
CREATE ROLE user_manager WITH LOGIN CREATEROLE PASSWORD 'password';
# -- 5. 创建只读用户
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';
# -- 然后需要单独授予 SELECT 权限
# -- 6. 创建你的用户（推荐）
CREATE ROLE yingci WITH LOGIN SUPERUSER PASSWORD 'your_password';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| 属性 | 说明 | 权限范围 |
|---|---|---|
SUPERUSER | 超级用户 | 所有权限，可绕过权限检查 |
CREATEDB | 创建数据库 | 可创建新数据库 |
CREATEROLE | 创建角色 | 可创建其他用户/角色 |
LOGIN | 登录权限 | 可以连接到数据库 |
REPLICATION | 复制权限 | 可用于流复制 |
BYPASSRLS | 绕过行级安全 | 绕过行级安全策略 |
# 系统信息函数
PostgreSQL 提供了多个系统信息函数，用于查询当前会话和数据库的信息。
# 用户和数据库信息
-- 当前用户信息。 最后一定要带;
SELECT current_user; -- 当前执行 SQL 的用户名
SELECT session_user; -- 会话用户名（连接时使用的用户名）
SELECT user; -- current_user 的别名
-- 当前数据库信息
SELECT current_database(); -- 当前连接的数据库名
-- 组合查询
SELECT
current_user AS 当前用户,
current_database() AS 当前数据库;#### 连接信息
-- 服务器连接信息
SELECT inet_server_addr(); -- 服务器 IP 地址
SELECT inet_server_port(); -- 服务器端口号
-- 客户端连接信息
SELECT inet_client_addr(); -- 客户端 IP 地址
SELECT inet_client_port(); -- 客户端端口号
-- 连接 ID
SELECT pg_backend_pid(); -- 当前会话的后端进程 ID#### 版本和配置信息
-- 版本信息
SELECT version(); -- PostgreSQL 版本详细信息
-- 配置参数
SHOW data_directory; -- 数据目录
SHOW config_file; -- 配置文件路径
SHOW server_version; -- 服务器版本号#### 时间和日期信息
-- 当前时间
SELECT now(); -- 当前时间戳（带时区）
SELECT current_timestamp; -- 当前时间戳（now() 的别名）
SELECT current_date; -- 当前日期
SELECT current_time; -- 当前时间
-- 时区信息
SELECT timezone('UTC', now()); -- 转换为 UTC 时间
SHOW timezone; -- 显示当前时区#### 常用查询示例
-- 查看完整的连接信息
SELECT
current_user AS 用户,
current_database() AS 数据库,
inet_client_addr() AS 客户端IP,
inet_server_addr() AS 服务器IP,
now() AS 当前时间;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# python的支持
import pandas as pd
from sqlalchemy import create_engine, types
import os
# 方式1：使用系统用户（推荐，无需密码）
username = os.getenv('USER')
engine = create_engine(f'postgresql://{username}@localhost:5432/postgres')
# 方式2：使用指定用户（需要密码时）
# engine = create_engine('postgresql://用户名:密码@localhost:5432/数据库名')
# 定义表结构
dtype_mapping = {
'ts_code': types.VARCHAR(20),
'com_name': types.VARCHAR(200),
'com_id': types.VARCHAR(50),
'chairman': types.VARCHAR(100),
'manager': types.VARCHAR(100),
'secretary': types.VARCHAR(100),
'reg_capital': types.NUMERIC(20, 2),
'setup_date': types.DATE,
'province': types.VARCHAR(50),
'city': types.VARCHAR(50),
'introduction': types.TEXT,
'website': types.VARCHAR(200),
'email': types.VARCHAR(100),
'office': types.VARCHAR(500),
'business_scope': types.TEXT,
'employees': types.INTEGER,
'main_business': types.TEXT,
'exchange': types.VARCHAR(20)
}
# 存储数据（使用 begin() 自动管理事务， 不需要手动执行 conn.commit() & conn.close() 了）
try:
with engine.begin() as conn:
_stock_company.to_sql(
name='stock_company',
con=conn,
if_exists='replace', # 'replace': 替换表, 'append': 追加数据, 'fail': 表存在则报错
index=False,
dtype=dtype_mapping,
chunksize=1000,
method='multi'
)
print(f"成功存储 {len(_stock_company)} 条数据")
# 验证数据（可选）
df_check = pd.read_sql('SELECT COUNT(*) as count FROM stock_company', engine)
print(f"表中现有数据条数：{df_check['count'].iloc[0]}")
except Exception as e:
print(f"存储数据失败：{e}")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# 远程访问
todo
# link
上次更新: 2025/12/10, 21:08:58