PostgreSQL（二）常用命令整理_psql命令

一、常用命令

登录命令：

psql -h IP -p 端口 -U 用户名 -d 数据库名 -W
1

• \? 所有命令帮助
• \l 列出所有数据库
• \d 列出数据库中所有表
• \dt 列出数据库中所有表
• \d [table_name] 显示指定表的结构
• \di 列出数据库中所有 index 索引
• \dv 列出数据库中所有 view 视图
• \h sql命令帮助
• \q 退出连接
• \c [database_name] 切换到指定的数据库
• \c 显示当前数据库名称和用户
• \conninfo 显示客户端的连接信息
• \du 显示所有用户
• \dn 显示数据库中的schema
• \encoding 显示字符集
• select version(); 显示版本信息
• \i testdb.sql 执行sql文件
• \x 扩展展示结果信息，相当于MySQL的\G
• \o /tmp/test.txt 将下一条sql执行结果导入文件中

查看版本：

select version();
1

二、用户

2.1 创建账号

创建用户：

create user 用户名 password '密码';
1

设置只读权限：

alter user 用户名 set default_transaction_read_only = on;
1

设置可操作的数据库：

grant all on database 数据库名 to 用户名;
1

授权可操作的模式和权限：

-- 授权
grant select on all tables in schema public to 用户名;
1
2

-- 授权
GRANT ALL ON TABLE public.user TO mydata;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.user TO mydata_dml;
GRANT SELECT ON TABLE public.user TO mydata_qry;
1
2
3
4

2.2 删除账号

撤回在public模式下的权限：

revoke select on all tables in schema public from 用户名;
1

撤回在information_schema模式下的权限：

revoke select on all tables in schema information_schema from 用户名;
1

撤回在pg_catalog模式下的权限：

revoke select on all tables in schema pg_catalog from 用户名;
1

撤回对数据库的操作权限：

revoke all on database 数据库名 from 用户名;
1

删除用户：

drop user 用户名;
1

三、权限

3.1 授权

设置只读权限：

alter user 用户名 set default_transaction_read_only = on;
1

设置可操作的数据库：

grant all on database 数据库名 to 用户名;
1

设置可操作的模式和权限：

grant select,insert,update,delete on all tables in schema public to 用户名;
1

2.2 撤回权限

撤回在public模式下的权限：

revoke select on all tables in schema public from 用户名;
1

撤回在information_schema模式下的权限：

revoke select on all tables in schema information_schema from 用户名;
1

撤回在pg_catalog模式下的权限：

revoke select on all tables in schema pg_catalog from 用户名;
1

撤回对数据库的操作权限：

revoke all on database 数据库名 from 用户名;
1

四、模式 Schema

创建和当前用户同名模式（schema）：

注意：用户名与 schema 同名，且用户具有访问改 schema 的权限，用户连入数据库时，默认即为当前 schema。

create schema AUTHORIZATION CURRENT_USER;
1

自定义创建模式（schema）：

create schema 模式名称;
1

注意：如果不创建scheme，并且语句中不写scheme，则默认scheme使用内置的public

查看数据库下的所有（schema）：

select * from information_schema.schemata;
1

五、数据库

查询所有数据库：

select datname from pg_database;
1

创建数据库：

create database 数据库名 owner 所属用户 encoding UTF8;
1

注意：创建完数据库，需要切换到数据库下，创建和当前用户同名scheme，删除数据库后schema也会一并删除：

-- 重新登陆到新数据库下，执行如下语句
create schema AUTHORIZATION CURRENT_USER;
1
2

删除数据库：

drop database 数据库名;
1

注意：删库前需要关闭所有会话，不然会提示：

ERROR:  database "mydb" is being accessed by other users
DETAIL:  There are 8 other sessions using the database.
1
2

关闭数据库所有会话：

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='mydb' AND pid<>pg_backend_pid();
1
2
3

六、表

查询schema中所有表：

select table_name from information_schema.tables where table_schema = 'myuser';
1

创建表：

CREATE TABLE public.t_user (
  "id" BIGSERIAL NOT NULL,
  "username" VARCHAR(64) NOT NULL,
  "password" VARCHAR(64) NOT NULL,
  "create_time" TIMESTAMP(0) default CURRENT_TIMESTAMP not null,
  "update_time" TIMESTAMP(0) default CURRENT_TIMESTAMP not null
);
-- 注释
COMMENT ON TABLE public.t_user IS '用户表';
COMMENT ON COLUMN public.t_user.id IS '主键';
COMMENT ON COLUMN public.t_user.username IS '用户名';
COMMENT ON COLUMN public.t_user.password IS '密码';
COMMENT ON COLUMN public.t_user.create_time IS '创建时间';
COMMENT ON COLUMN public.t_user.update_time IS '更新时间';
-- 创建自增序列
alter sequence "t_user_ID_seq" restart with 1 increment by 1;
-- 创建主键序列
drop index if exists "t_user_pkey";
alter table "t_user" add constraint "t_user_pkey" primary key ("ID");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

根据已有表结构创建表：

create table if not exists 新表 (like 旧表 including indexes including comments including defaults);
1

删除表：

drop table if exists "t_template" cascade;
1

查询注释：

SELECT
a.attname as "字段名",
col_description(a.attrelid,a.attnum) as "注释",
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '(.*)')) as "字段类型"
FROM
pg_class as c,
pg_attribute as a,
pg_type as t
WHERE
c.relname = 't_batch_task'
and a.atttypid = t.oid
and a.attrelid = c.oid
and a.attnum>0;
1
2
3
4
5
6
7
8
9
10
11
12
13

七、索引

创建索引：

drop index if exists t_user_username;
create index t_user_username on t_user (username);
1
2

创建唯一索引：

drop index if exists t_user_username;
create unique index t_user_username on t_user (username);
1
2

查看索引：

\d t_user
1

删除索引：

drop index t_user_username;
1

八、查询SQL

注意：PostgreSQL中的字段大小写敏感，而且只认小写字母，查询时需注意。

其他与基本sql大致相同。

8.1 to_timestamp() 字符串转时间

select * from t_user
where create_time >= to_timestamp('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:MI:SS');
1
2

8.2 to_char 时间转字符串

select to_char(create_time, 'yyyy-mm-dd hh24:MI:SS') from t_user;
1

8.3 时间加减

-- 当前时间加一天
SELECT NOW()::TIMESTAMP + '1 day';
SELECT NOW() + INTERVAL '1 DAY';
SELECT now()::timestamp + ('1' || ' day')::interval
-- 当前时间减一天
SELECT NOW()::TIMESTAMP + '-1 day';
SELECT NOW() - INTERVAL '1 DAY';
SELECT now()::timestamp - ('1' || ' day')::interval
-- 加1年1月1天1时1分1秒
select NOW()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';
1
2
3
4
5
6
7
8
9
10

8.4 like模糊查询

SELECT * FROM 表名 WHERE 字段 LIKE ('%关键字%');
1

8.5 substring字符串截取

--从第一个位置开始截取，截取4个字符,返回结果:Post
SELECT SUBSTRING ('PostgreSQL', 1, 4);
-- 从第8个位置开始截取，截取到最后一个字符，返回结果:SQL
SELECT SUBSTRING ('PostgreSQL', 8);
--正则表达式截取，截取'gre'字符串
SELECT SUBSTRING ('PostgreSQL', 'gre');
1
2
3
4
5
6

九、关联更新、关联删除SQL

9.1 关联更新

更新单列数据：

update table1 a set address = (select address from table2 b where a.id=b.id);
1

更新多列数据：

update table1 a set (address,people_cnt) = (select address,people_cnt from table2 b where a.id=b.id);
1

9.2 关联删除

示例：

delete from table1 a using table2 where a.id=b.id and address='china'; 
1

十、执行sql脚本

方式一：先登录再执行

\i testdb.sql
1

方式二：通过psql执行

psql -d testdb -U postgres -f /pathA/xxx.sql
1

十一、导出

11.1 导出数据

写法一：

pg_dump -h localhost -p 5432 -U postgres --column-inserts -t table_name -f data_sql.sql database_name
1

• --column-inserts 以带有列名的 INSERT 命令形式转储数据。
• -t 只转储指定名称的表。（不指定具体表则导出整个库。）
• -f 指定输出文件或目录名。

写法二：

直接使用 Linux 的 > 将内容写入到文件。

pg_dump -h localhost -p 5432 -U postgres --column-inserts -t table_name database_name > data_sql.sql
1

11.2 导出表结构

写法一：

pg_dump -h localhost -p 5432 -U postgres -s -t table_name -f struct_sql.sql database_name
1

• --column-inserts 以带有列名的 INSERT 命令形式转储数据。
• -t 只转储指定名称的表。（不指定具体表则导出整个库。）
• -f 指定输出文件或目录名。

写法二：

直接使用 Linux 的 > 将内容写入到文件。

pg_dump -h localhost -p 5432 -U postgres -s -t table_name -f struct_sql.sql database_name
1

整理完毕，完结撒花~

参考地址：

1.PostgreSql常用命令，https://blog.csdn.net/weixin_48321825/article/details/121775011

2.Postgresql创建账号及删除账号详细命令，https://blog.csdn.net/qq_44322586/article/details/123084962