PostgreSQL-常用命令汇总

业余砖家 / 2023-08-21 / 原文

1. 连接到 PostgreSQL 数据库：

psql -h 主机名 -p 端口号 -U 用户名 -d 数据库名

2. 列出所有数据库：

3. 切换到指定数据库：

\c 数据库名

4. 查看当前数据库中的所有表：

\dt

5. 查看表的结构：

\d 表名

6. 执行 SQL 查询：

SELECT * FROM 表名;

7. 创建数据库：

CREATE DATABASE 数据库名;

8. 创建表：

CREATE TABLE 表名 (

列名1 数据类型,

列名2 数据类型,

...

);

9. 插入数据到表中：

INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

10. 更新表中的数据：

UPDATE 表名 SET 列名 = 新值 WHERE 条件;

11. 删除表中的数据：

DELETE FROM 表名 WHERE 条件;

12. 删除表：

DROP TABLE 表名;

13. 退出 psql：

14. 查看帮助：

15. 查看表空间信息：

\db

16. 查看索引信息：

\d 索引

17. 列出所有角色和用户：

\dg

18. 查看表的权限分配情况

\dp 表名

19. COPY导入导出：

postgres的COPY命令可以快速的导出/导入数据到postgresql数据库中，支持常用的文件格式，如：txt、sql、csv、压缩文件、二进制格式等。特别适合批量导出和导入数据，速度比较快。
COPY TO把一个表的所有内容都拷贝到一个文件；
COPY FROM从一个文件里拷贝数据到一个表里(把数据附加到表中已经存在的内容里)。

注意：COPY只能用于表，不能用于视图。

相关参数：

(1) FROM：指定源文件或输入数据流。可以是本地文件路径或服务器文件路径，也可以是一个输入流。如果是一个输入流，则必须使用STDIN关键字。

(2) DELIMITER：指定分隔符。默认为制表符（" \t "），可以使用其他字符或字符串。例如：DELIMITER ‘;’ 表示用分号作为分隔符。

(3) HEADER：指定是否包含标题行。默认情况下不包含标题行，如果源文件包含标题行，则需要使用HEADER关键字。

(4) NULL：指定NULL值的表示方式。默认情况下，需要使用"\N"表示NULL值，如果输入数据使用其他的表示方式，可以使用这个选项。例如：NULL ‘null’ 表示用"null"作为NULL值的表示方法。

(5) ENCODING：指定编码方式。默认是UTF8编码。如果需要使用其他编码方式，可以使用ENCODING关键字。例如：ENCODING ‘GBK’ 表示采用GBK编码方式。

(6) QUOTE：指定数据中使用的引号。默认情况下，使用双引号（"）作为引号。如果输入数据使用其他的引号，可以使用这个选项。例如：QUOTE ‘’’ 表示使用单引号（’）作为引号。

(7) ESCAPE：指定转义字符。默认情况下，使用反斜杠（""）作为转义字符。如果需要采用其他转义字符，可以使用ESCAPE关键字。例如：ESCAPE ‘|’ 表示使用竖线（|）作为转义字符。

(8) FORMAT：输入数据的格式。默认为文本格式（TEXT）。可选的格式包括二进制格式（BINARY）和CSV格式（CSV）。例如：FORMAT binary 表示采用二进制格式。

(9) FILENAME：指定输出文件的路径。不是必需的参数，只有在导出数据到文件时才需要使用。

(10) ON CONFLICT：指定冲突解决策略。只有在导入数据到表时才需要使用。通常情况下，如果目标表中已经存在要插入的行，会报错。这时，可以使用ON CONFLICT关键字指定冲突解决策略。例如：ON CONFLICT DO NOTHING 表示如果目标表中已经存在要插入的行，则不执行任何操作，跳过这个行；ON CONFLICT DO UPDATE SET 列名=EXCLUDED.列名表示如果目标表中已经存在要插入的行，则更新该行数据。其中，EXCLUDED表示要插入的行。

(11) HEADER：指定表头所在的行数。只有在导入CSV格式文件时才需要使用。默认为1，表示第一行是表头行。如果表头行不是第一行，需要使用HEADER_ROW关键字指定表头所在的行数。例如：HEADER_ROW 2 表示表头位于第二行。

(12) FORMAT：指定自定义格式。只有在采用自定义格式时才需要使用。在使用自定义格式时，需要使用FORMAT关键字指定格式类型。例如：FORMAT text 表示要导出的数据是文本格式。