PostgreSQL最常用的函数 迷南。 2024-03-27 09:45 16阅读 0赞 **基本的** 切换和连接 $ sudo -u postgres psql 列出所有数据库 postgres=\# \\l 连接到名为 postgres 的数据库 postgres=\# \\c postgres 断开 postgres=\# \\q postgres=\# \\! **psql 命令** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">参数</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">示例</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">说明</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">[-d] <database></span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -d mydb</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">连接到数据库</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-U</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -U john mydb</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">以特定用户身份连接</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-h -p</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -h localhost -p 5432 mydb</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">连接到主机/端口</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-U -h -p -d</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -U admin -h 192.168.1.5 -p 2506 -d mydb</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">连接远程 PostgreSQL</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-W</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -W mydb</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">强制密码</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-c</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -c '\c postgres' -c '\dt'</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">执行 SQL 查询或命令</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-H</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -c "\l+" -H postgres > database.html</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">生成 HTML 报告</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-l</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -l</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出所有数据库</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-f</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql mydb -f file.sql</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">从文件执行命令</span></p></td> </tr> <tr> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">-V</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">psql -V</span></p></td> <td style="width:246px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">打印 psql 版本</span></p></td> </tr> </tbody> </table> **获得帮助** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\h</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">SQL 命令语法帮助</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\h DELETE</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">DELETE SQL 语句语法</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\?</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">PostgreSQL 命令列表</span></p></td> </tr> </tbody> </table> 在 PostgreSQL 控制台中运行 **PostgreSQL 工作** **Recon 观察** 显示版本 SHOW SERVER\_VERSION; 显示系统状态 \\conninfo 显示环境变量 SHOW ALL; 列出用户 SELECT rolname FROM pg\_roles; 显示当前用户 SELECT current\_user; 显示当前用户的权限 \\du 显示当前数据库 SELECT current\_database(); 显示数据库中的所有表 \\dt 列出函数 \\df <schema> **Databases 数据库** 列出数据库 \\l 连接到数据库 \\c <database\_name> 显示当前数据库 SELECT current\_database(); 创建数据库 CREATE DATABASE <database\_name> WITH OWNER<username>; 删除数据库 DROP DATABASE IF EXISTS <database\_name>; 重命名数据库 ALTER DATABASE <old\_name> RENAME TO<new\_name>; **Tables 表** 列出当前数据库中的表 \\dt SELECT table\_schema,table\_name FROMinformation\_schema.tables ORDER BY table\_schema,table\_name; 全局列表 \\dt \*.\*. SELECT \* FROM pg\_catalog.pg\_tables 列表表架构 \\d <table\_name> \\d+ <table\_name> SELECT column\_name, data\_type,character\_maximum\_length FROM INFORMATION\_SCHEMA.COLUMNS WHERE table\_name = '<table\_name>'; 创建表 CREATE TABLE <table\_name>( <column\_name> <column\_type>, <column\_name> <column\_type> ); 创建表,主键自增 CREATE TABLE <table\_name> ( <column\_name> SERIAL PRIMARY KEY ); 删除表 DROP TABLE IF EXISTS <table\_name> CASCADE; **Permissions 权限** 成为 postgres 用户,如果您有权限错误 sudo su - postgres psql 授予 对数据库的所有权限 GRANT ALL PRIVILEGES ON DATABASE <db\_name> TO<user\_name>; 授予数据库连接权限 GRANT CONNECT ON DATABASE <db\_name> TO<user\_name>; 授予架构权限 GRANT USAGE ON SCHEMA public TO <user\_name>; 授予函数权限 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user\_name>; 授予在所有表上选择、更新、插入、删除的权限 GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user\_name>; 在表上授予权限 GRANT SELECT, UPDATE, INSERT ON <table\_name> TO<user\_name>; 授予对表的选择权限 GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user\_name>; **Columns 列** 添加栏目 ALTER TABLE <table\_name> IF EXISTS ADD <column\_name> <data\_type> \[<constraints>\]; 更新栏 ALTER TABLE <table\_name> IF EXISTS ALTER <column\_name> TYPE<data\_type> \[<constraints>\]; 删除列 ALTER TABLE <table\_name> IF EXISTS DROP <column\_name>; 将列更新为自增主键 ALTER TABLE <table\_name> ADD COLUMN <column\_name> SERIAL PRIMARY KEY; 使用自动递增的主键插入表中 INSERT INTO <table\_name> VALUES (DEFAULT, <value1>); INSERT INTO <table\_name>(<column1\_name>,<column2\_name>) VALUES ( <value1>,<value2> ); **Data 数据** 选择 所有数据 SELECT \* FROM <table\_name>; 读取一行数据 SELECT \* FROM <table\_name> LIMIT 1; 搜索数据 SELECT \* FROM <table\_name> WHERE<column\_name> = <value>; 插入 数据 INSERT INTO <table\_name> VALUES(<value\_1>, <value\_2> ); 更新 数据 UPDATE <table\_name> SET <column\_1> = <value\_1>, <column\_2> = <value\_2> WHERE <column\_1> = <value>; 删除 所有数据 DELETE FROM <table\_name>; 删除特定数据 DELETE FROM <table\_name> WHERE <column\_name> = <value>; **Users 用户** 列出角色 SELECT rolname FROM pg\_roles; 创建用户 CREATE USER <user\_name> WITH PASSWORD '<password>'; 删除用户 DROP USER IF EXISTS <user\_name>; 更改 用户密码 ALTER ROLE <user\_name> WITH PASSWORD '<password>'; **Schema** 列出 Schemas \\dn SELECT schema\_name FROMinformation\_schema.schemata; SELECT nspname FROMpg\_catalog.pg\_namespace; 创建架构 CREATE SCHEMA IF NOT EXISTS <schema\_name>; 删除模式 DROP SCHEMA IF EXISTS <schema\_name> CASCADE; **PostgreSQL 命令** **表** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\d <table></span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">描述表</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\d+ <table></span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">详细描述表格</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dt</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出当前模式中的表</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dt *.*</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出所有模式中的表</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dt <schema>.*</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出架构的表</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dp</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出表访问权限</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\det[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出外部表</span></p></td> </tr> </tbody> </table> **查询缓冲区** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\e [FILE]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">编辑查询缓冲区(或文件)</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\ef [FUNC]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">编辑函数定义</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\p</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">显示内容</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\r</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">重置(清除)查询缓冲区</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\s [FILE]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">显示历史记录或保存到文件</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\w FILE</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">将查询缓冲区写入文件</span></p></td> </tr> </tbody> </table> **信息** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\l[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出所有数据库</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dn[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出架构</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\di[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出索引</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\du[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出角色</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\ds[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出序列</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\df[antw][S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出函数</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\deu[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出用户映射</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dv[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列表视图</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dl</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出大对象</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dT[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出数据类型</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\da[S]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出聚合</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\db[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出表空间</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dc[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出转化</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dC[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出演员表</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\ddp</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出默认权限</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dd[S]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">显示对象描述</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dD[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出域</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\des[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出国外服务器</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dew[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出外部数据包装器</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dF[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出文本搜索配置</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dFd[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出文本搜索词典</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dFp[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出文本搜索解析器</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dFt[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出文本搜索模板</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dL[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出程序语言</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\do[S]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出运算符</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dO[S+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出排序规则</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\drds</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出每个数据库的角色设置</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\dx[+]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">列出扩展</span></p></td> </tr> </tbody> </table> S:显示系统对象,+:附加细节 **连接** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\c [DBNAME]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">连接到新数据库</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\encoding [ENCODING]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">显示或设置客户端编码</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\password [USER]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">更改密码</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\conninfo</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">显示信息</span></p></td> </tr> </tbody> </table> **格式化** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\a</span></p></td> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">在未对齐和对齐之间切换</span></p></td> </tr> <tr> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\C [STRING]</span></p></td> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">设置表格标题,如果没有则取消设置</span></p></td> </tr> <tr> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\f [STRING]</span></p></td> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">显示或设置未对齐的字段分隔符</span></p></td> </tr> <tr> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\H</span></p></td> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">切换 HTML 输出模式</span></p></td> </tr> <tr> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\t [on|off]</span></p></td> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">仅显示行</span></p></td> </tr> <tr> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\T [STRING]</span></p></td> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">设置或取消设置 HTML <table> 标签属性</span></p></td> </tr> <tr> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\x [on|off]</span></p></td> <td style="width:276px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">切换扩展输出</span></p></td> </tr> </tbody> </table> **输入输出** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\copy ...</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">导入/导出表 另见: 复制</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\echo [STRING]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">打印字符串</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\i FILE</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">执行文件</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\o [FILE]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">将所有结果导出到文件</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\qecho [STRING]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">输出流的字符串</span></p></td> </tr> </tbody> </table> **变量** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\prompt [TEXT] NAME</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">设置变量</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\set [NAME [VALUE]]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">设置变量 (如果没有参数,则列出所有变量)</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\unset NAME</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">删除变量</span></p></td> </tr> </tbody> </table> **杂项** <table style="table-layout:fixed;width:28px;"> <tbody> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\cd [DIR]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">更改目录</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\timing [on|off]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">切换时间</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\! [COMMAND]</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">在shell中执行</span></p></td> </tr> <tr> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">\! ls -l</span></p></td> <td style="width:369px;vertical-align:top;height:52px;"><p style="text-align:left;"><span style="font-size:9pt;">在shell中列出所有</span></p></td> </tr> </tbody> </table> **大对象** * \\lo\_exportLOBOID FILE * \\lo\_importFILE \[COMMENT\] * \\lo\_list * \\lo\_unlinkLOBOID **各种各样的** **备份** 使用 pg\_dumpall 备份所有数据库 $ pg\_dumpall -U postgres> all.sql 使用 pg\_dump 备份数据库 $ pg\_dump -d mydb -fmydb\_backup.sql * \-a 只转储数据,而不是模式(schema) * \-s 只转储模式,不转储数据 * \-c 在重新创建之前删除数据库 * \-C 还原前创建数据库 * \-t 仅转储命名表 * \-F 格式(c:自定义,d:目录,t:tar) 使用 pg\_dump -? 获取完整的选项列表 **恢复** 使用 psql 恢复数据库 $ psql -U user mydb <mydb\_backup.sql 使用 pg\_restore 恢复数据库 $ pg\_restore -d mydbmydb\_backup.sql -c * \-U 指定数据库用户 * \-c 在重新创建之前删除数据库 * \-C 还原前创建数据库 * \-e 如果遇到错误退出 * \-F 格式(c:自定义,d:目录,t:tar,p:纯文本sql(默认)) 使用 pg\_restore -? 获取完整的选项列表 **远程访问** 获取 postgresql.conf 的位置 $ psql -U postgres -c 'SHOW config\_file' 附加到 postgresql.conf listen\_addresses = '\*' 附加到 pg\_hba.conf(与 postgresql.conf 相同的位置) host all all 0.0.0.0/0 md5 host all all ::/0 md5 重启 PostgreSQL 服务器 $ sudo systemctl restartpostgresql **导入/导出 CSV** 将表格导出为 CSV 文件 \\copy table TO '<path>' CSV \\copytable(col1,col1) TO '<path>' CSV \\copy(SELECT...) TO '<path>' CSV 将 CSV 文件导入表格 \\copy tableFROM '<path>' CSV \\copytable(col1,col1) FROM '<path>' CSV ALTER STABLE table ALTER COLUMN userid TYPE integer USING(userid::integer); ALTER STABLE table ALTER COLUMN reviewerid TYPE int4 USING(reviewerid::integer);
还没有评论,来说两句吧...