postgreSQL pgsql book [已迁移到语雀]
本文已迁移至语雀@20250314 https://www.yuque.com/puredream/windsnow/xpkepv7g02yvg1dc
pgsql官方下载地址
https://www.postgresql.org/ftp/binary/
pgsql与mysql对比
PostgreSQL与MySQL比较==>https://www.cnblogs.com/geekmao/p/8541817.html
在PG数据库中不单单可以控制操作表的权限，某中某几列, 其他数据库对象，比如序列、函数、视图等都可以控制。
pgsql安装
Linux安装postgresql【安装】==>https://www.cnblogs.com/whatlonelytear/p/10731255.html
linux 安装PostgreSql 12[转]==》https://www.cnblogs.com/whatlonelytear/p/15835862.html
docker安装pg(postgresql)==>https://www.cnblogs.com/cgy-home/p/17984101
docker 运行postgresql 极限简洁教程==>https://www.cnblogs.com/nulixuexipython/p/18040243
pgsql管理工具
dbeaver
navicat
pgadmin4工具安装及使用==>https://blog.csdn.net/tangzongwu/article/details/122165362
pgsql需要以非root用户模式启动
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
[root@iZbp1itlw36onzg6dw8fotZ pgsql_data]# ps -ef |grep postgresql
postgre 1887 11543 0 15:13 ? 00:00:00 /data/postgresql/pgsql/bin/postgres -D /data/postgresql/pgsql_data
root 1889 1622 0 15:13 pts/1 00:00:00 grep --color=auto postgresql
postgre 11543 1 0 Apr19 ? 00:02:42 /data/postgresql/pgsql/bin/postgres -D /data/postgresql/pgsql_data
[root@iZbp1itlw36onzg6dw8fotZ pgsql_data]# su postgre
linux 启动服务命令
启动时指定数据库和日志文件
/data/postgresql/pgsql/bin/pg_ctl -D /data/postgresql/pgsql_data/ -l /data/postgresql/pgsql_log/logfile start
linux关闭服务命令
关闭时指定数据库和日志文件
/data/postgresql/pgsql/bin/pg_ctl -D /data/postgresql/pgsql_data/ -l /data/postgresql/pgsql_log/logfile stop
进入linux管理postgresql命令控制台
/data/postgresql/pgsql/bin/psql
psql.bin (10.7)
Type "help" for help.
postgres=#
pgsql和mysql一样可以通过交互式提示符连接操作，连接方式如下：
/data/postgresql/pgsql/bin/psql -h 127.0.0.1 -p 5432 -d postgres -U postgre其中-h参数指定服务器地址，默认为127.0.0.1，默认不指定即可，-d指定连接之后选中的数据库，默认也是postgres，-U指定用户，默认是当前用户，-p 指定端口号，默认是"5432"，其它更多的参数选项可以执行： ./bin/psql --help 查看
创建用户
创建postgre数据库用户, 密码为postgre_pwd
postgres=# CREATE USER postgre WITH PASSWORD 'postgre_pwd';
CREATE ROLE
创建数据库并把权限归属给某用户
如果在linux命令端执行创建数据库命令, 重要的事情说三遍!!! 注意结尾一定要用;一定要用;一定要用; 当初因为没有加; 最终命令行并没有提示已创建成功的"CREATE DATABASE"提示, 还以为没有错就是成功,死活都报database 不存在.
postgres=# CREATE DATABASE "user1db" WITH OWNER postgre ENCODING UTF8;
CREATE DATABASE
也可以直接在navicat中直接执行
CREATE DATABASE "user1db" WITH OWNER postgre ENCODING UTF8;
切换数据库(使用uer1登录user1db)
postgres=# \c user1db user1
psql (8.4.18, server 10.7)
WARNING: psql version 8.4, server version 10.7.
Some psql features might not work.
You are now connected to database "user1db" as user "user1".
user1db=>
查看postgresql版本
select version();
PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
给用户赋权限
postgresql赋权语句解释图如下:
https://www.processon.com/diagraming/5dc231a6e4b0ece7594c6065
我们在赋权之前,也可以使用 \dp [表名] 命令 (Database Privilege缩写) 查看acl权限
user1db=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------------+-------+---------------------+--------------------------
public | table1 | table | user1=arwdDxt/user1 |
public | user1table | table | user1=arwdDxt/user1 |
: user2=arwdDxt/user1
(2 rows)如果数据库的的归属本来归该用户, 则无需赋权.
赋权命令如下
-- 将某张表赋权限给某个用户
-- 先选中用户1的数据库
\c user1db;
-- 赋予组权限给用户1
GRANT "group1" TO "user1";
-- 赋予单表或视图权限
GRANT ALL ON "userTable" TO "user2";
-- 赋予全表和视图权限
GRANT ALL ON ALL TABLES IN SCHEMA public TO "user2";
-- 赋予全库权限(可能包含触发器函数等,该语句能执行,但亲测完全无效,仅作记录,可无视)
GRANT ALL ON DATABASE "user1db" TO "user2";
撤消同理,把以上的GRANT换成REVOKE , TO 换成FROM ,如
REVOKE "group1" FROM "user1";
REVOKE ALL ON "userTable" FROM "user1";
给各schema赋权 , 
每个个库选择后执行一遍 , 常用重要
--授予对空间的访问权限
GRANT USAGE ON SCHEMA public TO "readonlyUser";
--授予全表的查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "readonlyUser";
--授予后面新增表的访问权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to "readonlyUser";
GRANT readonly TO "readonlyUser";
另外 赋权只对已存在的表有效, 对之后添加的表无效 , 所以其它用户如果新增了某张表就算已经赋过某一类权限也是无法访问的, 需要再次赋权, 当初在这个问题上头痛了一天, 一直以为对新表.......
浅谈PostgreSQL用户权限==>https://www.cnblogs.com/lottu/p/12916046.html
PostgreSQL 逻辑结构 和 权限体系 介绍==>https://yq.aliyun.com/articles/41210
PostgreSQL权限控制==>https://blog.csdn.net/weixin_36171533/article/details/90319423
设置pgsql允许远程连接
1、修改pgsql配置文件postgresql.conf(默认位于安装目录的data子文件夹下 /data/postgresql/pgsql_data/postgresql.conf) 把 listen_addresses = 'localhost' 改为 改为 listen_addresses = '*' #并取消注释
listen_addresses = '*' 2、port = 5432 取消注释,默认就是5432 ,这一步可以跳过
port = 54323、修改pg_hda.conf(和postgresql.conf同一个目录)，在末尾加一行 ，注意一定要使用md5（千万不要使用trust ， trust代码无须密码，永远信任，所以可以免密登录）
host all all 0.0.0.0/0 md54、开启5432端口：
iptables -I INPUT -p tcp --dport 5432 -j ACCEPT
/etc/init.d/iptables save
/etc/init.d/iptables restart4、重载配置文件
如果服务已经启动 , 直接使用 pg_ctl reload 重载配置即可让pg_hda.conf 文件的修改直接生效.
/data/soft/pgsql/bin/pg_ctl reload或者使用启停命令
/data/soft/pgsql/bin/pg_ctl stop
/data/soft/pgsql/bin/pg_ctl start
查询
基本语句
select 'bobo','sisi';
PostgreSQL 中的单引号与双引号用法说明
如，执行一句query：
|
1
|
select "name" from "students" where "id"='1' |
加上引号的好处在于，当在程序中进行sql拼装的时候，可以简化对值的校验，同时又可以避免sql注入。即在数据库层面完成了事故的避免。
如，同样执行的query：
|
1
|
select ";drop table students;" from "students" where "id"='1' |
由于被引号框起来，pg只会认为“；”也是列名的一部分，而不会将语句切断，从而顺利避免了事故。
补充：PostgreSQL 和 MySQL 关于单引号、双引号、反单引号的区别
解决方案写在前面：
MySQL 可以使用单引号（'）或者双引号（"）表示值，
但是 PG 只能用单引号（'）表示值，PG 的双引号（"）是表示系统标识符的，比如表名或者字段名。
MySQL可以使用反单引号（`）表示系统标识符，比如表名、字段名，
但PG 是不支持的。
事情的起因是同事发现好像反单引号（`）不能在 PG 中使用。
在 MySQL 和 Spark SQL 中，我觉得用反单引号是一个优秀的习惯。
本小节参考: PostgreSQL 中的单引号与双引号用法说明==>https://www.jb51.net/article/205143.htm
postgresql某用户打不开表
打不开表一般报以下错 relation "xxxTable" does not exist ,
可能原因有两种:
- 表本身确实不存在
- 没有选择表所在数据库
PostgreSQL 选择数据库==>https://www.runoob.com/postgresql/postgresql-select-database.html
#查看数据库
\l
#选择数据库
\c databaseName
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO king; //赋予所有表的所有权限给 king
GRANT ALL PRIVILEGES ON tableA TO king; // 赋予 king 用户， tableA 表的所有权限
真实场景遇到userA用户在databaseName1下建的表, userB用户无法访问的问题, 使用以下三种赋权语句,没有任何效果!
grant all on database databaseName1 to userB;
grant select on all tables in schema "public" to "userB";
grant all privileges on student to UserB;最终通过navicat的界面操作再查看历史日志,才确认只有以下语句才是最标准的赋权查看语句,但是批量赋权的话需要额外设计.
GRANT Select ON TABLE "public"."student" TO "UserB";
刚安装完的postgresql的配置文件和测试环境差异点
也就是新的本地环境需要改动的地方
postgresql.conf文件改动如下
listen_address = '*'
post = 5432
max_connections = 1000
password_encryption = md5
开放所有ip可以访问postgresql数据库
pg_hba.conf文件改动如下，在文件尾添加 指定配置后 , 再执行/data/soft/pgsql/bin/pg_ctl reload 重新加载(在服务已启动的情况下)
host all all 0.0.0.0/0 md5
限制仅部分ip可以访问postgresql数据库
host all all 127.0.0.1/32 trust
# 以下两个ip可以使用任意帐号登录
host all all 192.168.1.3/32 md5
host all all 192.168.1.4/32 md5
# 以下两个ip只能使用readonly帐号登录 , 至于readonly帐号是不是真的只有只读权限,那得看具体配置
host all readonly 192.168.2.5/32 md5
host all readonly 192.168.2.6/32 md5
重启
/etc/init.d/postgresql restart
或者 使用 /data/soft/pgsql/bin/pg_ctl reload 重新加载(在服务已启动的情况下)
查询当前库下的所有表
-- 查询当前库下的所有表
SELECT tablename FROM pg_tables
WHERE tablename NOT LIKE 'pg%'
AND tablename NOT LIKE 'sql_%'
ORDER BY tablename;
查询表 student 表的字段信息
information_schema.columns字段说明，获取数据库表所有列信息==>https://blog.csdn.net/cplvfx/article/details/108292814
-- 查询当前库下的student表的字段信息SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a
.atttypmod AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where c.relname='student' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid
清空或删除某个库下的所有表
-- 清空
select 'delete from '|| tablename || ';' from pg_tables where schemaname='public';
select 'truncate from '|| tablename || ';' from pg_tables where schemaname='public';
-- 删除
select 'drop table '|| tablename || ';' from pg_tables where schemaname='public';
-- 强删
select 'drop table if exists '|| tablename || ' cascade;' from pg_tables where schemaname='public';
-- PostgreSQL获取数据库中所有table名 表
SELECT tablename FROM pg_tables WHERE schemaname ='public';
-- PostgreSQL获取数据库中所有view名 视图
SELECT viewname FROM pg_views WHERE schemaname ='public';
-- PostgreSQL获取数据库中某张表的表结构
SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = 'view_cshop_activity_gold_history'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;
修改某库下所有表的owner
-- 表
SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO my_new_owner;' FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;
-- 序列
SELECT 'ALTER SEQUENCE '|| sequence_schema || '.' || sequence_name ||' OWNER TO my_new_owner;' FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema') ORDER BY sequence_schema, sequence_name;
-- 查看
SELECT 'ALTER VIEW '|| table_schema || '.' || table_name ||' OWNER TO my_new_owner;' FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name;
-- 物化观点 基于this answer
SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;' FROM pg_class WHERE relkind = 'm' ORDER BY oid;
-- 这将生成所有必需的ALTER TABLE/ALTER SEQUENCE/ALTER VIEW语句，复制它们并将它们粘贴回plsql以运行它们。 通过执行以下操作检查psql中的工作：
\dt *.*
\ds *.*
\dv *.*
常用函数
select round(678.12345,2) ; -- 精确到小数点后2位substring( plate_no ,0,2 ); -- 取字符串前2个字节，"浙A12345" 会返回 "浙" PostgreSQL Substring教程==>https://blog.csdn.net/neweastsun/article/details/112546754
索引
-- 查看索引
SELECT * FROM pg_indexes WHERE tablename = 'tbname';
-- 创建索引
CREATE INDEX index_name ON TABLE_NAME ( COLUMN_NAME );
PostgreSQL 索引==>https://www.runoob.com/postgresql/postgresql-index.html
查看是否走索引
not in 不走, 当in的个数很小的时候走, 多了也不走
explain analyze SELECT * FROM "tablea" where id not in ('23');
索引知识
重建索引 from kimi
在PostgreSQL中，重建索引通常涉及删除旧索引并创建一个新索引。这个过程可以通过以下步骤完成：
-
备份：在进行任何索引重建之前，确保对数据库进行备份，以防万一发生错误。
-
分析表：使用
ANALYZE命令来收集表的统计信息，这有助于优化器更好地理解表中的数据分布。ANALYZE your_table; -
删除旧索引：使用
DROP INDEX命令来删除现有的索引。DROP INDEX IF EXISTS your_index_name; -
创建新索引：使用
CREATE INDEX命令来创建一个新的索引。确保新索引的定义与旧索引相同。CREATE INDEX your_index_name ON your_table (column1, column2, ...); -
使用
REINDEX命令：PostgreSQL提供了REINDEX命令，它可以用于重建单个表的索引或者整个数据库的索引。对于单个表的索引重建，可以使用：REINDEX TABLE your_table;或者，如果你只想重建特定的索引，可以使用：
REINDEX INDEX your_index_name;请勿在生产环境中直接使用如下命令，即不带 concurrently 选项，这样在 reindex 运行过程中会阻塞 DML 语句，对于生产业务是不可接受的 , 而小表的索引没必要修改, 大表索引修改又什阻塞, 所以这条命令是个废命令。 -
监控性能：重建索引后，监控数据库的性能，确保索引重建没有引入任何问题。
-
📌考虑使用
CONCURRENTLY：对于大型表，使用REINDEX可能会锁定表，导致其他操作被阻塞。在这种情况下，可以使用REINDEX CONCURRENTLY来减少对数据库操作的影响：REINDEX INDEX CONCURRENTLY your_index_name;
请注意，REINDEX CONCURRENTLY在重建索引时不会锁定索引，但可能会消耗更多的时间和资源来完成。
- 定期维护：将索引重建作为定期维护计划的一部分，以保持数据库性能。
锁表解锁操作
--查询是否锁表了
select oid from pg_class where relname='获取可能锁表了的表oid,注意本语句能查到结果不代表锁表';
select pid from pg_locks where relation='10016'; --上面查出的oid number
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid);
--当某一表被大量pid同时锁定时，直接生成批量解锁语句再，把结果集执行
select 'select pg_cancel_backend(' || t.pid || ');' "执行以下语句批量解锁" from
(
select pid from pg_locks where relation='10016' -- relation 为上面查出的 oid number
) t
如何查看PostgreSQL正在执行的SQL
SELECT
procpid,
START,
now( ) - START AS lap,
current_query
FROM
(
SELECT
backendid,
pg_stat_get_backend_pid ( S.backendid ) AS procpid,
pg_stat_get_backend_activity_start ( S.backendid ) AS START,
pg_stat_get_backend_activity ( S.backendid ) AS current_query
FROM
( SELECT pg_stat_get_backend_idset ( ) AS backendid ) AS S
) AS S
WHERE
current_query <> '<IDLE>'
ORDER BY
lap DESC;
procpid：进程id
start：进程开始时间
lap：经过时间
current_query：执行中的sql
怎样停止正在执行的sql
SELECT pg_cancel_backend(进程id);
参考： 如何查看PostgreSQL正在执行的SQL==>https://www.cnblogs.com/dancesir/p/8250238.html
其它好文章： POSTGRESQL识别阻塞会话==>http://www.dboracle.com/archivers/postgresql识别阻塞会话.html
另外还有一种分析查询慢的方式 PostgreSQL之pg_stat_activity==》https://www.cnblogs.com/zhuminghui/p/14421501.html
判断postgresql数据库是否达到最大连接上限
如果当前连接数 接近 最大连接数 ,则要当心了, 说明数据库快爆了
SHOW max_connections;--最大连接数
SELECT count(*) FROM pg_stat_activity;--当前连接数
使用正则表达式取值
select SUBSTRING ("折旧费用帐户组合",'.*?\.(.*?)\..*' ) from tableA
pgsql 并集 交集 差集
postgreSQL数据库，
- UNION 求并集，
- INTERSECT 求交集，
- EXCEPT 求差集 ( 相当于 oracle 的 minus )
select * from student1
EXCEPT
select * from student2;postgresql----UNION&&INTERSECT&&EXCEPT==>https://www.cnblogs.com/alianbog/p/5621562.html
pgsql递归查询
参考自: PostgreSQL的递归查询(with recursive)==>https://blog.csdn.net/wenzhihui_2010/article/details/43935019
create table temp_area(id varchar(3) , pid varchar(3) , name varchar(10));
insert into temp_area values('002' , 0 , '浙江省');
insert into temp_area values('001' , 0 , '广东省');
insert into temp_area values('003' , '002' , '衢州市');
insert into temp_area values('004' , '002' , '杭州市') ;
insert into temp_area values('005' , '002' , '湖州市');
insert into temp_area values('006' , '002' , '嘉兴市') ;
insert into temp_area values('007' , '002' , '宁波市');
insert into temp_area values('008' , '002' , '绍兴市') ;
insert into temp_area values('009' , '002' , '台州市');
insert into temp_area values('010' , '002' , '温州市') ;
insert into temp_area values('011' , '002' , '丽水市');
insert into temp_area values('012' , '002' , '金华市') ;
insert into temp_area values('013' , '002' , '舟山市');
insert into temp_area values('014' , '004' , '上城区') ;
insert into temp_area values('015' , '004' , '下城区');
insert into temp_area values('016' , '004' , '拱墅区') ;
insert into temp_area values('017' , '004' , '余杭区') ;
insert into temp_area values('018' , '011' , '金东区') ;
insert into temp_area values('019' , '001' , '广州市') ;
insert into temp_area values('020' , '001' , '深圳市') ;
WITH RECURSIVE cte AS (
SELECT A.ID, A.NAME, A.pid FROM temp_area A WHERE ID = '002'
UNION ALL
SELECT K.ID, K.NAME, K.pid FROM temp_area K
INNER JOIN cte C ON C.ID = K.pid
)
SELECT ID ,NAME FROM cte;
|
|
|
|
|
002 浙江省 |
002 浙江省 |
高级教程: SQL优化（五） PostgreSQL （递归）CTE 通用表表达式==>https://blog.csdn.net/habren/article/details/51137094
order by createDate limit 10 offset 0 乱序问题(高频)
假如有如下student表
| id | createDate |
| 0 | 2019-10-14 10:55:26 |
| 1 | 2019-10-14 10:55:26 |
| 2 | 2019-10-14 10:55:26 |
| 3 | 2019-10-14 10:55:25 |
在postgresql中执行如下2条sql语句, 得到的第一行数据结果并不一致
SELECT * FROM student T ORDER BY T.createDate DESC ; -- 取出id顺序 0 ,1 ,2 ,3
SELECT * FROM student T ORDER BY T.createDate DESC LIMIT 1 OFFSET 0; -- 取出id顺序 3 , 📌注意 limit 和 offset 位置不能交换得出结果是在postgresql中,只要排序字段中有相同的值, 那么排序后的结果就是不固定的,解决方案只能额外追加order by字段来保证排序的唯一性, 不然就只能接受这种乱序结果. 所以最好改成
SELECT * FROM student T ORDER BY T.createDate DESC , id ASC; --取出id顺序 0 ,1 ,2 ,3
SELECT * FROM student T ORDER BY T.createDate DESC , id ASC LIMIT 1 OFFSET 0; --取出id顺序 0
pgsql用正则匹配
使用 ~ 后面跟着正则表达式就能够完成
SELECT * from table WHERE param ~ '^[1-9]\d{3}-$'如果需要筛选不符合此时间格式的诗句，使用 ！~ 就能够达成目的；
函数
PostgreSQL 常用函数==>https://www.runoob.com/postgresql/postgresql-functions.html
postgreSql日期比较
"END_DATE" >= '2019-11-14 23:59:59'::date数据库之postgreSql时间计算，例如获取前一天、后一天等。==>https://blog.csdn.net/weixin_40594160/article/details/100139852
当前日期加减1天
SELECT now()::timestamp + '1 year'; --当前时间加1年
SELECT now()::timestamp + '1 month'; --当前时间加一个月
SELECT now()::timestamp + '1 day'; --当前时间加一天
SELECT now()::timestamp + '-1 day'; --当前时间减一天
SELECT now()::timestamp + '1 hour'; --当前时间加一个小时
SELECT now()::timestamp + '1 min'; --当前时间加一分钟
SELECT now()::timestamp + '1 sec'; --加一秒钟
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec'; --加1年1月1天1时1分1秒
SELECT now()::timestamp + (col || ' day')::interval FROM table --把col字段转换成天 然后相加
数据库字段日期加1天
select stu.birth_date , (stu.birth_date::timestamp + interval '1 day') from student; -- 学生日期+1天
生成连续的时间函数
select * from generate_series ( to_timestamp ( '2018-10-01', 'YYYY-MM-DD HH24:MI:SS' ), to_timestamp ( '2018-10-7', 'YYYY-MM-DD HH24:MI:SS' ), '1 days' )
postgreSQL中timestamp转date的方式
SELECT to_date(to_char(NOW(),'YYYY-MM-DD'),'YYYY-MM-DD');
SELECT NOW() :: DATE
抽取年月日
select
EXTRACT(year from createdate) as Year,
EXTRACT(month from createdate) as Month,
EXTRACT(day from createdate) as Day
from student ;
select date(createDate) as mydate from student;
select to_char(createDate ,'yyyy-MM') yearAndMonth from student;
字符串转数字
select cast('1234' as integer ) ;
--用substring截取字符串，从第8个字符开始截取2个字符：结果是12
select cast(substring('1234abc12',8,2) as integer)
数字转字符串
select cast(123 as VARCHAR);
postgreSql存储过程for循环
CREATE extension "uuid-ossp";
SELECT
uuid_generate_v4 ( );
DO
$$ DECLARE
v_idx INTEGER := 1;
BEGIN
while
v_idx < 300000
loop
v_idx = v_idx + 1;
INSERT INTO "public"."student" ( "id", "name" )
VALUES
( uuid_generate_v4 ( ), 'bobo' );
END loop;
END $$;
postgresql procedure 存储过程==>https://www.cnblogs.com/whatlonelytear/p/14368716.html
postgresql查询时生成序号
SELECT row_number() over () as rownumfrom from student s
postgresql查询时生成分组序号
业务场景:
查询学生的每门课里考试成终最高的那一次作为最终考试结果 (下方样例暂未按此需求实现, 但总体意思相同)
sql语句:
select row_number() over( [partition by col1] order by col2[desc]) , * from student - row_number() 为返回的记录定义各行编号
- partition by 分组
- order by 排序
postgresql sql查询结果添加序号列与每组第一个序号应用 ==>https://www.cnblogs.com/love1/p/12082701.html
第一步:先按分组排序
select ROW_NUMBER() OVER (partition BY createuserid ORDER BY createdate asc) rowid, * from student t4
结果
|
rowid |
id |
name |
clazz |
type |
createuserid |
createdate |
score |
memo |
|
1 |
6 |
赵 |
1班 |
数学 |
U001 |
2021-10-22 00:00:00.000000 |
60 |
- |
|
2 |
1 |
赵 |
1班 |
语文 |
U001 |
2021-10-22 00:00:00.000000 |
60 |
- |
|
3 |
12 |
赵 |
1班 |
物理 |
U001 |
2021-11-16 00:00:00.000000 |
60 |
同id为11的创建日期,又考了一次 |
|
4 |
11 |
赵 |
1班 |
物理 |
U001 |
2021-11-16 00:00:00.000000 |
60 |
- |
|
1 |
2 |
钱 |
1班 |
语文 |
U002 |
2021-10-22 00:00:00.000000 |
70 |
- |
|
2 |
7 |
钱 |
1班 |
数学 |
U002 |
2021-10-22 00:00:00.000000 |
70 |
- |
|
1 |
3 |
孙 |
1班 |
语文 |
U003 |
2021-10-22 00:00:00.000000 |
80 |
- |
|
2 |
8 |
孙 |
1班 |
数学 |
U003 |
2021-10-22 00:00:00.000000 |
80 |
- |
|
1 |
4 |
李 |
2班 |
语文 |
U004 |
2021-10-22 00:00:00.000000 |
90 |
- |
|
2 |
9 |
李 |
2班 |
数学 |
U004 |
2021-10-22 00:00:00.000000 |
90 |
- |
|
1 |
5 |
周 |
2班 |
语文 |
U005 |
2021-10-22 00:00:00.000000 |
100 |
- |
|
2 |
10 |
周 |
2班 |
数学 |
U005 |
2021-10-22 00:00:00.000000 |
100 |
- |
第二步:取每个用户编号分组的第一条
select * from (
select ROW_NUMBER() OVER (partition BY createuserid ORDER BY createdate asc) rowid, * from student t1
)t2 where rowid = 1
结果
|
rowid |
id |
name |
clazz |
type |
createuserid |
createdate |
score |
memo |
|
1 |
6 |
赵 |
1班 |
数学 |
U001 |
2021-10-22 00:00:00.000000 |
60 |
- |
|
1 |
2 |
钱 |
1班 |
语文 |
U002 |
2021-10-22 00:00:00.000000 |
70 |
- |
|
1 |
3 |
孙 |
1班 |
语文 |
U003 |
2021-10-22 00:00:00.000000 |
80 |
- |
|
1 |
4 |
李 |
2班 |
语文 |
U004 |
2021-10-22 00:00:00.000000 |
90 |
- |
|
1 |
5 |
周 |
2班 |
语文 |
U005 |
2021-10-22 00:00:00.000000 |
100 |
- |
SELECT
cols.column_name AS "字段名",
pgd.description AS "字段备注"
FROM
information_schema.columns cols
LEFT JOIN
pg_catalog.pg_description pgd
ON
pgd.objoid = (
SELECT oid
FROM pg_catalog.pg_class
WHERE relname = cols.table_name
)
AND pgd.objsubid = cols.ordinal_position
WHERE
cols.table_name = 'my_table' -- 表名
AND cols.table_schema = 'public'; -- 如果表在public模式下
postgresql开启事务
在PostgreSQL中，开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来。PostgreSQL实际上将每一个SQL语句都作为一个事务来执行。如果我们没有发出BEGIN命令，则每个独立的语句都会被加上一个隐式的BEGIN以及（如果成功）COMMIT来包围它。一组被BEGIN和COMMIT包围的语句也被称为一个事务块。
BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
-- etc etc
COMMIT;
-- ROLLBACK; -- 返回，在做测试的时候，这个语句会非常便利
postgresql查询出空值时设默认值0
COALESCE函数是返回参数中的第一个非null的值，它要求参数中至少有一个是非null的，如果参数都是null会报错
select COALESCE(null,null); //报错
select COALESCE(null,null,now(),''); //结果会得到当前的时间
select COALESCE(null,null,'',now()); //结果会得到''
//查询学生性别，如果学生名字为male显示"男生",否则显示"女生"
select case when sex = 'male' then '男生' else '女生' end from student;
select case when sex = 'male' then '男生' when sex='female' then '女生' else '空' end from student;
//可以和其他函数配合来实现一些复杂点的功能：查询学生姓名，如果学生名字为null或''则显示“姓名为空”
select case when coalesce(name,'') = '' then '姓名为空' else name end from student;
//case when 中还可以使用in进行批量标记, 比如标记一批姓名叫'王小二'或'王小王'的学生
select case when name in ('王小二','王小五') then '匹配' else '不匹配' end from student;PostgreSQL（MySQL）插入操作传入值为空则设置默认值==>https://blog.csdn.net/qq_19734597/article/details/103740673
postgresql查询视图的依赖引用关系
所有表
SELECT c.ev_class::regclass::varchar AS objname, pc.oid::regclass::varchar AS refobjname
FROM pg_depend a,pg_depend b,pg_class pc,pg_rewrite c
WHERE a.refclassid=1259 -- 1259是pg_depend的oid
AND a.classid=2618 -- 2618是pg_rewrite的oid
AND b.deptype='i' -- 内部依赖
AND a.objid=b.objid
AND a.classid=b.classid
AND a.refclassid=b.refclassid
AND a.refobjid<>b.refobjid
AND pc.oid=a.refobjid
AND c.oid=b.objid
GROUP BY c.ev_class,pc.oid;
单表📌
SELECT c.ev_class::regclass::varchar AS objname, pc.oid::regclass::varchar AS refobjname
FROM pg_depend a,pg_depend b,pg_class pc,pg_rewrite c
WHERE a.refclassid=1259 -- 1259是pg_depend的oid
AND a.classid=2618 -- 2618是pg_rewrite的oid
AND b.deptype='i' -- 内部依赖
AND a.objid=b.objid
AND a.classid=b.classid
AND a.refclassid=b.refclassid
AND a.refobjid<>b.refobjid
AND pc.oid=a.refobjid
AND c.oid=b.objid
and pc.oid::regclass::varchar = '输入指定视图名'
GROUP BY c.ev_class,pc.oid ;
postgresql导出到oracle
需要使用工具PostgresToOracle ,有30天使用期, 最好在虚拟机中执行, 且导出时单表最多只能1000条, 另外导出的表和表字段会有大小写问题, 要特别留意 , 之后在SpringBoot2的项目中改造时只需要调整以下5行内容即可 , 启动新oracle的项目
更新
update tableA AA set name = BB.name , sex = BB.sex from tableB BB where AA.id = BB.id ; #注意 name = BB.name 前不能加AA, 即不能用AA.name = BB.name
update批量更新某一列成其它列对应的值【原】==>https://www.cnblogs.com/whatlonelytear/p/11649747.html
按条件更新
-- 当学生分数大于90分时统一减10分,不然保留原始分数
UPDATE "student"
SET score =
CASE
WHEN score >= 90 THEN score - 10 ELSE score END
WHERE
createuserid = 'ID0000001';
postgresql查随机10条数据
select * from Student order by random() limit 10ostgreSQL-随机查询N条记录==>https://blog.csdn.net/weixin_30586257/article/details/99510977
导入导出
postgresql数据备份之导入导出【转】==>https://www.cnblogs.com/whatlonelytear/p/11791222.html
postgresql数据的导入导出(备份)==>https://www.cnblogs.com/zhengshuaiaijava/p/9493464.html
注意
postgresql以非yum安装，用systemctl启动失败，报权限不能以root启动
查看安装了哪些扩展插件
select * from pg_available_extensions;
跨库视图
最后可以使用以下语句查询扩展是否安装成功
select * from pg_available_extensions; --查询扩展如果有dblink则代表可以创建
name |default_version|installed_version|comment |
---------------+---------------+-----------------+------------------------------------------------------------------+
plpgsql |1.0 |1.0 |PL/pgSQL procedural language |
dblink |1.2 |1.2 |connect to other PostgreSQL databases from within a database |
btree_gist |1.5 | |support for indexing common datatypes in GiST |
postgres_fdw |1.0 |1.0 |foreign-data wrapper for remote PostgreSQL servers |
...
第一步
create extension dblink第二步
create view myView as
SELECT *
FROM dblink('hostaddr=1.2.3.4 port=5432 dbname=myDb user=myUsername password=myPassword'::text,
'select
id ,
name
from "student" '::text)
student(
id CHARACTER VARYING(50),
name CHARACTER VARYING(50)
);参考： PostgreSQL中使用dblink实现跨库查询的方法【转】==>https://www.cnblogs.com/whatlonelytear/p/12799837.html
fdw(Foreign Data Wrappers)外部服务器
fdw(Foreign Data Wrappers)外部服务器==>https://www.yuque.com/puredream/windsnow/yy5n96
PostgreSQL 手册==》https://www.php.cn/manual/view/20782.html
随机查数据
SELECT myid FROM mytable ORDER BY RANDOM() LIMIT 1;
PostgreSQL快速随机取出记录==>http://blog.chinaunix.net/uid-20332519-id-5616589.html
遇见异常
password authentication failed for user "postgres"
FATAL: no pg_hba.conf entry for host
错误明细: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.1.5", user "bobo", database "dbbobo", SSL off
解决方案: 在pg_hba.conf中把192.168.1.5加入到访问清单中 , 具体配置参见本文 pg_hba.conf 相关配置说明处
postgres数据库varchar类型的最大长度
在分析一个场景时，postgres中的一个字段存储很长的字符串时，是否可能存在问题。被问到varchar类型的最大长度，不是很清楚。
查了一下，记录一下。
| 名字 | 描述 |
|---|---|
| character varying(n), varchar(n) | 变长，有长度限制 |
| character(n), char(n) | 定长，不足补空白 |
| text | 变长，无长度限制 |
简单来说，varchar的长度可变，而char的长度不可变，对于postgresql数据库来说varchar和char的区别仅仅在于前者是变长，而后者是定长，最大长度都是10485760（1GB）
varchar不指定长度，可以存储最大长度（1GB）的字符串，而char不指定长度，默认则为1，这点需要注意。
text类型：在postgresql数据库里边，text和varchar几乎无性能差别，区别仅在于存储结构的不同。
对于char的使用，应该在确定字符串长度的情况下使用，否则应该选择varchar或者text。
其他人说的最大长度是10485760，我不是DBA，也没做过这个实验。但是有疑问，编码格式不为UTF-8时，是否还是10485760？
text类型是挺好用的，假如需要存储一个复杂且结构可能会变化的数据，搞成json字符串存储到text里也是很好的。感觉成了MongoDB
参考 postgres数据库varchar类型的最大长度==>https://www.cnblogs.com/lnlvinso/p/12369472.html
遇见异常
FATAL: password authentication failed for user "postgres"
要么是密码本来就错了, 或者密码过期了, 2020年9月10日遇到密码过期问题
# 进入
/data/soft/postgres/pgsql/bin/
# 访问postgresql控制台
./psql
# 查看用户
SELECT * FROM pg_roles WHERE rolname='postgres';
# 修改用户密码和过期时间
alter role postgres with password 'postgres123' valid until '2099-01-01';
# 再次查看用户
SELECT * FROM pg_roles WHERE rolname='postgres';org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"
查看用户对表有哪些权限
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilegeselect * from INFORMATION_SCHEMA.role_table_grants where grantee='readonlyUser';
GRANT SELECT ON ALL tables IN SCHEMA public TO "readonlyUser"; # 给用户赋查询表和视图权限
postgresql使用对查询结果使用正则SUBSTRING
select SUBSTRING(info, '\"unionId\":\"(.*?)\"') as unionId, SUBSTRING(info, '\"mch_billno\":\"(.*?)\"') as mch_billno from teacher
给postgresql添加注释
--database注释
COMMENT ON DATABASE myDbName IS '样例数据库';
--表注释
COMMENT ON TABLE myTableName IS '样例';
--sequence注释
COMMENT ON SEQUENCE mySeqName IS '样例';
--列明注释
COMMENT ON COLUMN myTableName.myColumnName IS '姓名';
--注释删除
COMMENT ON COLUMN myTableName.myColumnName IS NULL;
postgresql临时表的使用
只跟会话有关，会话结束则临时表自动删，其它会话 无法访问
CREATE TEMPORARY TABLE temp_tbl_student (
id int,
name varchar(50),
age int,
birthdate timestamp
)
create TEMP table temp_tbl_student;Postgresql临时表==>https://www.cnblogs.com/baisha/p/8074716.html
json格式数据存取(难用）
postgresql----JSON和JSONB类型==>https://blog.csdn.net/u012129558/article/details/81453640
postgresql数据库状态
select * from pg_stat_activity
索引
查索引语句：
SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
tablename = 'user_tbl'
ORDER BY
tablename,
indexname;
查表名语句：
SELECT
tablename
FROM
pg_tables
查数据库语句：
SELECT
datname
FROM
pg_database
命令行语句：
\l —— 得到全部数据库
\dt —— 得到全部表
\d 数据库 —— 得到所有表的名字
\d 表名 —— 得到表结构
查看表已占用多少磁盘空间
#表空间 #占用大小 #占用空间 #表大小 #磁盘空间
select pg_size_pretty(pg_relation_size('profile_userinfo_copy1'));
查看各个数据库大小，都需要先进入到对应数据库再执行
查看各个数据库表大小(不包含索引)，以及表数据量
mysql:
select table_name,concat(round((DATA_LENGTH/1024/1024),2),'M')as size,table_rows from information_schema.tables order by table_rows desc limit 20postgresql:
select table_schema,TABLE_NAME,reltuples,pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"'))
from pg_class,information_schema.tables where relname=TABLE_NAME
ORDER BY reltuples desc limit 20
Sybase:
据空间大小的：
1.sybase(以16k逻辑页为例)
use YWST
go
select top 10 object_name(id) tabName,rowcnt rowCnt,convert(varchar,(pagecnt*16/1024)) + 'M' dataSize from systabstats order by rowcnt descoracle:
查记录条数可以用如下语句：
select * from user_tables t where t.NUM_ROWS is not null order by t.NUM_ROWS desc 。
-- 表实际使用的空间：
select num_rows * avg_row_len
from user_tables
postgresql-查看各个数据库大小==》https://blog.csdn.net/weixin_30896657/article/details/98340169
POSTGRESQL 查看数据库 数据表大小==>https://www.cnblogs.com/liqiu/p/3922288.html
查看表大小
select table_schema,TABLE_NAME,reltuples,pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')/1024/1024 table_size_mb
from pg_class,information_schema.tables where relname=TABLE_NAME AND table_schema = 'public'
ORDER BY reltuples desc limit 50
查看Postgresql的连接状况
查看Postgresql的连接状况==》https://blog.csdn.net/weixin_34072458/article/details/92575864
select * from pg_stat_activity;
postgresql注入
SELECT * FROM tableName ORDER BY 1/(case when user<'cshoq' then 1 else 0 end)
在ORDER BY 中传参 1/(case when user<'cshoq' then 1 else 0 end) 看是否能正常执行
postgresql常用命令
1.createdb 数据库名称
产生数据库
2.dropdb 数据库名称
删除数据库
3.CREATE USER 用户名称
创建用户
4.drop User 用户名称
删除用户
5.SELECT usename FROM pg_user;
查看系统用户信息
\du
7.SELECT version();
查看版本信息
8.psql 数据库名
打开psql交互工具
9.mydb=> \i basics.sql
\i 命令从指定的文件中读取命令。
10.COPY weather FROM '/home/user/weather.txt';
批量将文本文件中内容导入到wether表
11.SHOW search_path;
显示搜索路径
12.创建用户
CREATE USER 用户名 WITH PASSWORD '密码'
13.创建模式
CREATE SCHEMA myschema;
14.删除模式
DROP SCHEMA myschema;
15.查看搜索模式
SHOW search_path;
16.设置搜索模式
SET search_path TO myschema,public;
17.创建表空间
create tablespace 表空间名称 location '文件路径';
18.显示默认表空间
show default_tablespace;
19.设置默认表空间
set default_tablespace=表空间名称;
20.指定用户登录
psql MTPS -u
21.显示当前系统时间、
now()
22.配置plpgsql语言
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
23.删除规则
DROP RULE name ON relation [ CASCADE | RESTRICT ]
输入
name
要删除的现存的规则．
relation
该规则应用的关系名字(可以有大纲修饰)．
CASCADE
自动删除依赖于此规则的对象。
RESTRICT
如果有任何依赖对象，则拒绝删除此规则。这个是缺省。
24.日期格式函数
select 'P'||to_char(current_date,'YYYYMMDD')||'01'
25.产生组
Create Group 组名称
26.修改用户归属组
Alter Group 组名称 add user 用户名称
26.为组赋值权限
grant 操作 On 表名称 to group 组名称：
27.创建角色
Create Role 角色名称
28.删除角色
Drop Role 角色名称
29.获得当前postgresql版本
SELECT version();
30.在linux中执行计划任务
通过crontab执行
su root -c "psql -p 5433 -U developer MTPS -c'select test()'"
developer用户的密码存储于环境变量PGPASSWORD中。
31.查询表是否存在
select * from pg_statio_user_tables where relname='你的表名';
32.为用户复制SCHEMA权限
grant all on SCHEMA 作用域名称 to 用户名称
33.整个数据库导出
pg_dumpall -D -p 端口号 -h 服务器IP -U postgres(用户名) > /home/xiaop/all.bak
34.数据库备份恢复
psql -h 192.168.0.48 -p 5433 -U postgres
35.当前日期函数
current_date
36.返回第十条开始的5条记录
select * from tabname limit 5 offset 10;
37.为用户赋模式权限
Grant on schema developer to UDataHouse
38.将字符转换为日期时间
select to_timestamp('2010-10-21 12:31:22', 'YYYY-MM-DD hh24:mi:ss')
39.数据库备份
pg_dumpall -h 192.168.0.4 -p 5433 -U postgres >/DataBack/Postgresql2010012201.dmp
如8.1以后多次输入密码
40.\dn
查看schema
41.删除schema
drop schema _clustertest cascade;
42.导出表
./pg_dump -p 端口号 -U 用户 -t 表名称 -f 备份文件位置 数据库 ;
43.字符串操作函数
select distinct(split_part(ip,'.',1)||'.'||split_part(ip,'.',2)) from t_t_userip order by (split_part(ip,'.',1)||'.'||split_part(ip,'.',2));
44.删除表主键
alter table 表名 drop CONSTRAINT 主键名称;
45.创建表空间
create tablespace 空间名称 location '路径'
46.查看表结构
select * from information_schema.columns
./postgres -D /usr/local/src/data
or
./pg_ctl -D /usr/local/src/data -l logfile start
47.查看数据库大小
SELECT pg_size_pretty(pg_database_size('MTPS')) As fulldbsize;
48.查看数据库表大小
SELECT pg_size_pretty(pg_total_relation_size('developer.t_L_collectfile')) As fulltblsize,
pg_size_pretty(pg_relation_size('developer.t_L_collectfile')) As justthetblsize
49.设置执行超过指定秒数的sql语句输出到日志
log_min_duration_statement = 3
50.超过一定秒数sql自动执行执行计划
shared_preload_libraries = 'auto_explain'
custom_variable_classes = 'auto_explain'
auto_explain.log_min_duration = 4s
51.数据库备份
select pg_start_backup('backup baseline');
select pg_stop_backup();
recovery.conf
restore_command='cp /opt/buxlog/%f %p'
52.重建索引
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]
INDEX
重新建立声明了的索引。
TABLE
重新建立声明的表的所有索引。如果表有个从属的"TOAST"表，那么这个表也会重新索引。
DATABASE
重建当前数据库里的所有索引。 除非在独立运行模式下，会忽略在共享系统表上的索引（见下文）。
SYSTEM
在当前数据库上重建所有系统表上的索引。不会处理在用户表上的索引。 另外，除了是在单主机模式下，共享的系统表也会被忽略（见下文）。
name
需要重建索引的索引，表或者数据库的名称。 表和索引名可以有模式修饰。 目前，REINDEX DATABASE 和 REINDEX SYSTEM 只能重建当前数据库的索引， 因此其参数必须匹配当前数据库的名字。
FORCE
这是一个废弃的选项，如果声明，会被忽略。
54.数据字典查看表结构
SELECT column_name, data_type from information_schema.columns where table_name = 'blog_sina_content_train';
52.查看被锁定表
SELECT pg_class.relname AS table, pg_database.datname AS database, pid, mode, granted
FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid;
53.查看客户端连接情况
SELECT client_addr ,client_port,waiting,query_start,current_query FROM pg_stat_activity;
54.常看数据库.conf配置
show all
55.修改数据库postgresql.conf参数
修改postgresql.conf内容
pg_ctl reload
56.回滚日志强制恢复
pg_resetxlog -f 数据库文件路径
idvalue | remark
----------+--------
33953557 | inser
57.当前日期属于一年中第几周
select EXTRACT(week from TIMESTAMP '2010-10-22');
58.显示最近执行命令
\s
I. SQL 命令
ABORT — 退出当前事务
ALTER AGGREGATE — 修改一个聚集函数的定义
ALTER CONVERSION — 修改一个编码转换的定义
ALTER DATABASE — 修改一个数据库
ALTER DOMAIN — 改变一个域的定义
ALTER FUNCTION — 修改一个函数的定义
ALTER GROUP — 修改一个用户组
ALTER INDEX — 改变一个索引的定义
ALTER LANGUAGE — 修改一个过程语言的定义
ALTER OPERATOR — 改变一个操作符的定义
ALTER OPERATOR CLASS — 修改一个操作符表的定义
ALTER ROLE — 修改一个数据库角色
ALTER SCHEMA — 修改一个模式的定义
ALTER SEQUENCE — 更改一个序列生成器的定义
ALTER TABLE — 修改表的定义
ALTER TABLESPACE — 改变一个表空间的定义
ALTER TRIGGER — 改变一个触发器的定义
ALTER TYPE — 改变一个类型的定义
ALTER USER — 改变数据库用户帐号
ANALYZE — 收集与数据库有关的统计
BEGIN — 开始一个事务块
CHECKPOINT — 强制一个事务日志检查点
CLOSE — 关闭一个游标
CLUSTER — 根据一个索引对某个表集簇
COMMENT — 定义或者改变一个对象的评注
COMMIT — 提交当前事务
COMMIT PREPARED — 提交一个早先为两阶段提交准备好的事务
COPY — 在表和文件之间拷贝数据
CREATE AGGREGATE — 定义一个新的聚集函数
CREATE CAST — 定义一个用户定义的转换
CREATE CONSTRAINT TRIGGER — 定义一个新的约束触发器
CREATE CONVERSION — 定义一个新的的编码转换
CREATE DATABASE — 创建新数据库
CREATE DOMAIN — 定义一个新域
CREATE FUNCTION — 定义一个新函数
CREATE GROUP — 定义一个新的用户组
CREATE INDEX — 定义一个新索引
CREATE LANGUAGE — 定义一种新的过程语言
CREATE OPERATOR — 定义一个新的操作符
CREATE OPERATOR CLASS — 定义一个新的操作符表
CREATE ROLE — define a new database role
CREATE RULE — 定义一个新的重写规则
CREATE SCHEMA — 定义一个新的模式
CREATE SEQUENCE — 创建一个新的序列发生器
CREATE TABLE — 定义一个新表
CREATE TABLE AS — 从一条查询的结果中定义一个新表
CREATE TABLESPACE — 定义一个新的表空间
CREATE TRIGGER — 定义一个新的触发器
CREATE TYPE — 定义一个新的数据类型
CREATE USER — 创建一个新的数据库用户帐户
CREATE VIEW — 定义一个视图
DEALLOCATE — 删除一个准备好的查询
DECLARE — 定义一个游标
DELETE — 删除一个表中的行
DROP AGGREGATE — 删除一个用户定义的聚集函数
DROP CAST — 删除一个用户定义的类型转换
DROP CONVERSION — 删除一个用户定义的编码转换
DROP DATABASE — 删除一个数据库
DROP DOMAIN — 删除一个用户定义的域
DROP FUNCTION — 删除一个函数
DROP GROUP — 删除一个用户组
DROP INDEX — 删除一个索引
DROP LANGUAGE — 删除一个过程语言
DROP OPERATOR — 删除一个操作符
DROP OPERATOR CLASS — 删除一个操作符表
DROP ROLE — 删除一个数据库角色
DROP RULE — 删除一个重写规则
DROP SCHEMA — 删除一个模式
DROP SEQUENCE — 删除一个序列
DROP TABLE — 删除一个表
DROP TABLESPACE — 删除一个表空间
DROP TRIGGER — 删除一个触发器定义
DROP TYPE — 删除一个用户定义数据类型
DROP USER — 删除一个数据库用户帐号
DROP VIEW — 删除一个视图
END — 提交当前的事务
EXECUTE — 执行一个准备好的查询
EXPLAIN — 显示语句执行规划
FETCH — 用游标从查询中抓取行
GRANT — 定义访问权限
INSERT — 在表中创建新行
LISTEN — 监听一个通知
LOAD — 装载或重载一个共享库文件
LOCK — 明确地锁定一个表
MOVE — 重定位一个游标
NOTIFY — 生成一个通知
PREPARE — 创建一个准备好的查询
PREPARE TRANSACTION — 为当前事务做两阶段提交的准备
REINDEX — 重建索引
RELEASE SAVEPOINT — 删除一个前面定义的保存点
RESET — 把一个运行时参数值恢复为缺省值
REVOKE — 删除访问权限
ROLLBACK — 退出当前事务
ROLLBACK PREPARED — 取消一个早先为两阶段提交准备好的事务
ROLLBACK TO — 回滚到一个保存点
SAVEPOINT — 在当前事务里定义一个新的保存点
SELECT — 从表或视图中取出若干行
SELECT INTO — 从一个查询的结果中定义一个新表
SET — 改变运行时参数
SET CONSTRAINTS — 设置当前事务的约束检查模式
SET ROLE — set the current user identifier of the current session
SET SESSION AUTHORIZATION — 为当前会话设置会话用户标识符和当前用户标识符
SET TRANSACTION — 设置当前事务的特性
SHOW — 显示运行时参数的数值
START TRANSACTION — 开始一个事务块
TRUNCATE — 清空一个或者一堆表
UNLISTEN — 停止监听通知信息
UPDATE — 更新一个表中的行
VACUUM — 垃圾收集以及可选地分析一个数据库
II. 客户端应用
clusterdb — 对一个PostgreSQL数据库进行建簇
createdb — 创建一个新的 PostgreSQL 数据库
createlang — 定义一种新的 PostgreSQL 过程语言
createuser — 定义一个新的 PostgreSQL 用户帐户
dropdb — 删除一个现有 PostgreSQL 数据库
droplang — 删除一种 PostgreSQL 过程语言
dropuser — 删除一个 PostgreSQL 用户帐户
ecpg — 嵌入的 SQL C 预处理器
pg_config — 检索已安装版本的 PostgreSQL 的信息
pg_dump — 将一个PostgreSQL数据库抽出到一个脚本文件或者其它归档文件中
pg_dumpall — 抽出一个 PostgreSQL 数据库集群到脚本文件中
pg_restore — 从一个由 pg_dump 创建的备份文件中恢复 PostgreSQL 数据库。
psql — PostgreSQL 交互终端
vacuumdb — 收集垃圾并且分析一个PostgreSQL 数据库
III. PostgreSQL 服务器应用
initdb — 创建一个新的 PostgreSQL数据库集群
ipcclean — 从失效的PostgreSQL服务器中删除共享内存和信号灯
pg_controldata — 显示一个 PostgreSQL 集群的控制信息
pg_ctl — 启动，停止和重起 PostgreSQL
pg_resetxlog — 重置一个 PostgreSQL 数据库集群的预写日志以及其它控制内容
postgres — 以单用户模式运行一个 PostgreSQL服务器
postmaster — PostgreSQL多用户数据库服务器
59.导出数据库角色
/data/pgsql/bin/pg_dumpall -p 5432 -U postgres -r >/tmp/postgres_8.3_role.bak
60.修改sequence所有者
grant all on sequence名称 to 所有者;
61.修改sequence初始值
Alter SEQUENCE sequencename START value;
62.查看sequence当前值
SELECT currval('sequencename');
63.查看sequence下一值
SELECT nextval('sequencename');
64.设置sequence当前值
alter SEQUENCE sequencename restart with startvalue;
SELECT nextval('sequencename');
65.查询表结构
SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a
.atttypmod AS lengthvar,a.attnotnull AS notnull
FROM pg_class c,pg_attribute a,pg_type t
WHERE c.relname=表名称and a.attnum > 0 and a.attrelid = c.oid and a
.atttypid = t.oid
66.将查询结果直接输出到文件
在psql中
\o 文件路径
select datname,rolname from pg_database a left outer join pg_roles b on a.datdba=b.oid ;
\o
67.查询数据库所有则
select datname,rolname from pg_database a left outer join pg_roles b on a.datdba=b.oid ;
68.结束正在执行的事务
SELECT * from pg_stat_activity;
select pg_cancel_backend('procpid');
60.结束session
SELECT * from pg_stat_activity;
select pg_terminate_backend('procpid');
61.postgresql取消转义字符功能
将postgresql.conf文件中的standard_conforming_strings设置为on
62.查询正在执行SQL
SELECT
procpid,
start,
now() - start AS lap,
current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S
WHERE
current_query <> ''
ORDER BY
lap DESC;
postgres创建用户，修改用户密码，创建数据库
1.创建用户
1
sudo -s -u postgres
2
psql
3
postgres# CREATE USER xxxx1 WITH PASSWORD 'xxxx';
4
postgres# CREATE DATABASE xxxx2;
5
postgres# GRANT ALL PRIVILEGES ON DATABASE xxxx2 to xxxx1;
2.修改密码
alter user postgres with password 'foobar';
3.创建数据库
1
createdb--encoding=UTF8 --owner=foo --template=template_postgis -Ufoo
参数： --encoding=UTF8 设置数据库的字符集
--owner=foo 设置数据库的所有者
--tmplate=template_postgis 设置建库的模板，该模板支持空间数据操作
--Ufoo 用foo用户身份建立数据库
postgresql限制
表名、列名、函数名最大长度为63个字符,可以在编译前修改,修改后必须重新initdb
2019-08-13修改:63个字符是指英文和数字组合,如果奇葩的使用中文或其它字符集的话,大约会是21个字符左右
src/include/pg_config_manual.h
#define NAMEDATALEN 64
最大数据库大小无限制
单个表最大大小 32 TB
最大行大小 1.6 TB
最大字段大小 1 GB
每张表的最大行数无限制
每个表的最大列数 250 - 1600 取决于列类型
每张表的最大索引无限制
postgresql将字符串列表作为表格进行搜索
-- 单字段
select *
from (
values
('1'),
('2'),
('3')
) as student(id)
where id = '2';
-- 多字段
select *
from (
values
('1', 'bobo', 90),
('2', 'sisi', 80),
('3', 'cici', 87)
) as student(id, name, score)
where id = '2';
postgresql管理工具
优质数据库管理工具盘点，看看这三个软件的区别==>https://blog.csdn.net/weixin_46201409/article/details/109091975
日志参考
6 错误操作和日志 ERROR REPORTING AND LOGGING==》https://www.cnblogs.com/lykops/p/8263098.html
遇见异常
PostgreSQL之alter table add column会锁表吗==>https://www.modb.pro/db/84224
参考
CentOS7安装并配置PostgreSQL==>https://www.cnblogs.com/Paul-watermelon/p/10654303.html
PostgreSQL学习之【用户权限管理】说明==>https://www.cnblogs.com/zhoujinyi/p/10939715.html
PGsql 基本用户权限操作==>https://www.cnblogs.com/jokerjason/p/9389526.html
step by step设置postgresql用户密码并配置远程连接==>https://www.cnblogs.com/error500/p/3305956.html
PGsql 基本用户权限操作==>https://www.cnblogs.com/jokerjason/p/9389526.html
postgresql----条件表达式==>https://www.cnblogs.com/alianbog/p/5672018.html （实用）case when
待学习
postgresql-shared_buffers==》https://www.cnblogs.com/zhangfx01/p/10438735.html
postgresql shared_buffers 讲解==>https://blog.csdn.net/liuhhaiffeng/article/details/106141097
postgresql之shared_buffer==》https://blog.csdn.net/qq_35462323/article/details/111299657
PostgreSQL内存参数之shared_buffers==>https://zhuanlan.zhihu.com/p/337091745
java EntityManager使用方法[转]==》https://www.cnblogs.com/rain-in-summer/articles/5590369.html
浙公网安备 33010602011771号