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 <schema>

列出数据库

\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>;

列出当前数据库中的表

\dt

SELECT table_schema,table_name FROM information_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;

如果遇到权限错误，切换到 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>;

添加列

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> );

选择所有数据

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>;

列出角色

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>';

列出架构

\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;

创建架构

CREATE SCHEMA IF NOT EXISTS <schema_name>;

删除架构

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

显示当前日期 YYYY-MM-DD

SELECT current_date;

计算两个日期之间的年龄差

SELECT age(timestamp, timestamp);

显示带时区的当前时间

SELECT current_time;

使用整数创建日期

SELECT make_date(2021,03,25);

• \lo_export LOBOID FILE
• \lo_import FILE [COMMENT]
• \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 table TO '<path>' CSV
\copy table(col1,col1) TO '<path>' CSV
\copy (SELECT...) TO '<path>' CSV

将 CSV 文件导入表

\copy table FROM '<path>' CSV
\copy table(col1,col1) FROM '<path>' CSV

另见：Copy