PostgreSQL 速查表

切换并连接

$ sudo -u postgres psql

列出所有数据库

postgres=# \l

连接到名为 postgres 的数据库

postgres=# \c postgres

断开连接

postgres=# \q
postgres=# \!

在 PostgreSQL 控制台中运行

显示版本

SHOW SERVER_VERSION;

显示系统状态

\conninfo

显示环境变量

SHOW ALL;

列出用户

SELECT rolname FROM pg_roles;

显示当前用户

SELECT current_user;

显示当前用户的权限

\du

显示当前数据库

SELECT current_database();

显示数据库中的所有表

\dt

列出函数

\df <模式名>

列出数据库

\l

连接到数据库

\c <数据库名>

显示当前数据库

SELECT current_database();

创建数据库

CREATE DATABASE <数据库名> WITH OWNER <用户名>;

删除数据库

DROP DATABASE IF EXISTS <数据库名>;

重命名数据库

ALTER DATABASE <旧名称> RENAME TO <新名称>;

列出当前数据库中的表

\dt

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

全局列出表

\dt *.*.

SELECT * FROM pg_catalog.pg_tables

列出表结构

\d <表名>
\d+ <表名>

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<表名>';

创建表

CREATE TABLE <表名>(
  <列名> <列类型>,
  <列名> <列类型>
);

创建带自增主键的表

CREATE TABLE <表名> (
  <列名> SERIAL PRIMARY KEY
);

删除表

DROP TABLE IF EXISTS <表名> CASCADE;

如果遇到权限错误，成为 postgres 用户

sudo su - postgres
psql

授予数据库所有权限

GRANT ALL PRIVILEGES ON DATABASE <数据库名> TO <用户名>;

授予数据库连接权限

GRANT CONNECT ON DATABASE <数据库名> TO <用户名>;

授予模式权限

GRANT USAGE ON SCHEMA public TO <用户名>;

授予函数权限

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <用户名>;

授予对所有表的选择、更新、插入、删除权限

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <用户名>;

授予对表的权限

GRANT SELECT, UPDATE, INSERT ON <表名> TO <用户名>;

授予对表选择的权限

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <用户名>;

添加列

ALTER TABLE <表名> IF EXISTS
ADD <列名> <数据类型> [<约束>];

更新列

ALTER TABLE <表名> IF EXISTS
ALTER <列名> TYPE <数据类型> [<约束>];

删除列

ALTER TABLE <表名> IF EXISTS
DROP <列名>;

更新列为自增主键

ALTER TABLE <表名>
ADD COLUMN <列名> SERIAL PRIMARY KEY;

向带自增主键的表中插入数据

INSERT INTO <表名>
VALUES (DEFAULT, <值1>);


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

选择所有数据

SELECT * FROM <表名>;

读取一行数据

SELECT * FROM <表名> LIMIT 1;

搜索数据

SELECT * FROM <表名> WHERE <列名> = <值>;

插入数据

INSERT INTO <表名> VALUES( <值1>, <值2> );

更新数据

UPDATE <表名>
SET <列1> = <值1>, <列2> = <值2>
WHERE <列1> = <值>;

删除所有数据

DELETE FROM <表名>;

删除特定数据

DELETE FROM <表名>
WHERE <列名> = <值>;

列出角色

SELECT rolname FROM pg_roles;

创建用户

CREATE USER <用户名> WITH PASSWORD '<密码>';

删除用户

DROP USER IF EXISTS <用户名>;

修改用户密码

ALTER ROLE <用户名> WITH PASSWORD '<密码>';

列出模式

\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;

创建模式

CREATE SCHEMA IF NOT EXISTS <模式名>;

删除模式

DROP SCHEMA IF EXISTS <模式名> CASCADE;

显示当前日期 YYYY-MM-DD

SELECT current_date;

计算两个日期之间的年龄

SELECT age(timestamp, timestamp);

显示带时区的当前时间

SELECT current_time;

使用整数创建日期

SELECT make_date(2021,03,25);

• \lo_export LOBOID 文件
• \lo_import 文件 [注释]
• \lo_list
• \lo_unlink LOBOID

使用 pg_dumpall 备份所有数据库

$ pg_dumpall -U postgres > all.sql

使用 pg_dump 备份数据库

$ pg_dump -d mydb -f mydb_backup.sql

使用 pg_dump -? 获取完整的选项列表

使用 psql 恢复数据库

$ psql -U user mydb < mydb_backup.sql

使用 pg_restore 恢复数据库

$ pg_restore -d mydb mydb_backup.sql -c

使用 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 restart postgresql

将表导出到 CSV 文件

\copy 表 TO '<路径>' CSV
\copy 表(列1,列2) TO '<路径>' CSV
\copy (SELECT...) TO '<路径>' CSV

将 CSV 文件导入到表

\copy 表 FROM '<路径>' CSV
\copy 表(列1,列2) FROM '<路径>' CSV

另请参阅: Copy