PostgreSQL 常用命令

登录命令

1
2

#连接指定服务器上的数据库
psql -h IP -p 端口 -U 用户名 -d 数据库名 -W

psql是PostgreSQL的一个命令行交互式客户端工具，它具有非常丰富的功能，类似于Oracle的命令行工具sqlplus。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-h #数据库所在的IP地址
-p #（默认5432）数据库的监听端口
-U #用户名
-d #数据库名称
常用命令说明
\? #所有命令帮助
\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执行结果导入文件中

用户管理

创建用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

create user 用户名 password '密码';

#设置只读权限
alter user 用户名 set default_transaction_read_only = on;

#设置可操作的数据库
grant all on database 数据库名 to 用户名;

#授权可操作的模式和权限
-- 授权
grant select on all tables in schema public to 用户名;

-- 授权
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
 5
 6
 7
 8
 9
10
11
12
13
14

#撤回在public模式下的权限
revoke select on all tables in schema public from 用户名;

#撤回在information_schema模式下的权限
revoke select on all tables in schema information_schema from 用户名;

#撤回在pg_catalog模式下的权限
revoke select on all tables in schema pg_catalog from 用户名;

#撤回对数据库的操作权限
revoke all on database 数据库名 from 用户名;

#删除用户
drop user 用户名;

权限管理

授权

1
2
3
4
5
6
7
8

#设置只读权限
alter user 用户名 set default_transaction_read_only = on;

#设置可操作的数据库
grant all on database 数据库名 to 用户名;

#设置可操作的模式和权限
grant select,insert,update,delete on all tables in schema public to 用户名;

撤回权限

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

#撤回在public模式下的权限
revoke select on all tables in schema public from 用户名;

#撤回在information_schema模式下的权限
revoke select on all tables in schema information_schema from 用户名;

#撤回在pg_catalog模式下的权限
revoke select on all tables in schema pg_catalog from 用户名;

#撤回对数据库的操作权限
revoke all on database 数据库名 from 用户名;

模式 Schema

PostgreSQL 模式SCHEMA 可以看着是一个表的集合。一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰。
• 将数据库对象组织成逻辑组以便更容易管理。
• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

语法 我们可以使用 CREATE SCHEMA 语句来创建模式，语法格式如下：

1
2
3

CREATE SCHEMA myschema.mytable (
...
);

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

create schema AUTHORIZATION CURRENT_USER; 自定义创建模式（schema）

create schema 模式名称; 注意：如果不创建scheme，并且语句中不写scheme，则默认scheme使用内置的public。

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

1
2
3
4
5
6

select * from information_schema.schemata;
数据库管理
查询所有数据库
select datname from pg_database;
创建数据库
create database 数据库名 owner 所属用户 encoding UTF8;

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

-- 重新登陆到新数据库下，执行如下语句 create schema AUTHORIZATION CURRENT_USER; 删除数据库 drop database 数据库名; 注意：删库前需要关闭所有会话，不然会提示：

ERROR: database "mydb" is being accessed by other users DETAIL: There are 8 other sessions using the database. 关闭数据库所有会话 SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='mydb' AND pid<>pg_backend_pid(); 更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

表管理 建表模板语句 create table "t_user" ( "id" bigserial not null, "username" varchar (64) not null, "password" varchar (64) not null, "create_time" timestamp not null default current_timestamp, "update_time" timestamp not null default current_timestamp, constraint t_user_pk primary key (id) );

comment on column "t_user"."id" is '主键'; comment on column "t_user"."username" is '用户名'; comment on column "t_user"."password" is '密码'; comment on column "t_user"."create_time" is '创建时间'; comment on column "t_user"."update_time" is '更新时间'; 查询schema中所有表 select table_name from information_schema.tables where table_schema = 'myuser'; 创建表 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"); 根据已有表结构创建表 create table if not exists 新表 (like 旧表 including indexes including comments including defaults); 删除表 drop table if exists "t_template" cascade; 查询注释 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; 索引管理 创建索引 drop index if exists t_user_username; create index t_user_username on t_user (username); 创建唯一索引 drop index if exists t_user_username; create index t_user_username on t_user (username); 查看索引 \d t_user 查询SQL 注意：PostgreSQL中的字段大小写敏感，而且只认小写字母，查询时需注意。其他与基本sql大致相同。

to_timestamp() 字符串转时间 select * from t_user where create_time >= to_timestamp('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:MI:SS'); to_char 时间转字符串 select to_char(create_time, 'yyyy-mm-dd hh24:MI:SS') from t_user; 时间加减 -- 当前时间加一天 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'; like 模糊查询 SELECT * FROM 表名 WHERE 字段 LIKE ('%关键字%'); substring字符串截取 --从第一个位置开始截取，截取4个字符,返回结果:Post SELECT SUBSTRING ('PostgreSQL', 1, 4); -- 从第8个位置开始截取，截取到最后一个字符，返回结果:SQL SELECT SUBSTRING ('PostgreSQL', 8); --正则表达式截取，截取'gre'字符串 SELECT SUBSTRING ('PostgreSQL', 'gre'); 更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

执行sql脚本 方式一：先登录再执行

\i testdb.sql 方式二：通过psql执行

psql -d testdb -U postgres -f /pathA/xxx.sql 导出数据到SQL文件 pg_dump -h localhost -p 5432 -U postgres --column-inserts -t table_name -f save_sql.sql database_name --column-inserts #以带有列名的 INSERT 命令形式转储数据。 -t #只转储指定名称的表。 -f #指定输出文件或目录名。 JDBC 连接串常用参数 PostgreSQL JDBC 官方驱动下载地址：https://jdbc.postgresql.org/download/ PostgreSQL JDBC 官方参数说明文档：https://jdbc.postgresql.org/documentation/use/ 驱动类：driver-class-name=org.postgresql.Driver 单机 PostgreSQL 连接串 url: jdbc:postgresql://10.20.1.231:5432/postgres? binaryTransfer=false&forceBinary=false&reWriteBatchedInserts=true binaryTransfer=false：控制是否使用二进制协议传输数据，false 表示不适用，默认为 true forceBinary=false：控制是否将非 ASCII 字符串强制转换为二进制格式，false 表示不强制转换，默认为 true reWriteBatchedInserts=true：控制是否将批量插入语句转换成更高效的形式，true 表示转换，默认为 false 例如：

insert into foo (col1, col2, col3) values(1,2,3); insert into foo (col1, col2, col3) values(4,5,6); 会转换成：

insert into foo (col1, col2, col3) values(1,2,3), (4,5,6); 如果使用正确，reWriteBatchedInserts 会提升批量 insert 性能 2-3 倍。图片

集群PostgreSQL 连接串 集群PostgreSQL，连接串如下：

url: jdbc:postgresql://10.20.1.231:5432/postgres? binaryTransfer=false&forceBinary=false&reWriteBatchedInserts=true&targetServerType=master&loadBalanceHosts=true 单机 PostgreSQL 连接串的所有参数。 targetServerType=master：只允许连接到具有所需状态的服务器，可选值有： any：默认，表示连接到任何一个可用的数据库服务器，不区分主从数据库； master：表示连接到主数据库，可读写； slave：表示连接到从数据库，可读，不可写； 其他不常用值：primary, master, slave, secondary, preferSlave, preferSecondary and preferPrimary。 loadBalanceHosts=true：控制是否启用主从模式下的负载均衡，true 表示启用，开启后依序选择一个 ip1:port 进行连接，默认为 false。

捐赠本站(Donate)

如您感觉文章有用，可扫码捐赠本站！(If the article useful, you can scan the QR code to donate))