1、按月产生连续数据
highgo=# SELECT to_char( T, 'yyyy-mm' ) AS TIME FROM generate_series(to_timestamp('2021-01', 'yyyy-mm'), to_timestamp('2021-10', 'yyyy-mm'), '1 months') AS T;
time
---------
2020-01
2020-02
2020-03
2020-04
2、按天产生连续数据
highgo=# SELECT to_char( T, 'yyyy-mm-dd' ) AS TIME FROM generate_series(to_timestamp('2021-01-01', 'yyyy-mm-dd'), to_timestamp('2021-01-03', 'yyyy-mm-dd'), '1 days') AS T;
time
------------
2020-01-01
2020-01-02
2020-01-03
3、按小时产生连续数据
highgo=# SELECT to_char( t, 'yyyy-mm-dd hh24:mi:ss' ) AS TIME FROM generate_series ( to_timestamp( '2021-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), to_timestamp( '2021-01-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), '1 hours' ) AS t;
time
---------------------
2020-01-01 00:00:00
2020-01-01 01:00:00
. . .
2020-01-01 06:00:00
2020-01-01 07:00:00
1、列出当前数据库所有触发器
select * from pg_trigger;
2、列举出特定表的触发器
select pt.* from pg_class pc join pg_trigger pt on pt.tgrelid=pc.oid where relname='table_name';
3、查询指定模式下的触发器，及其作用的表，使用的触发器函数
select pc.oid tableoid, pc.relname "表名",
pt.oid triggeroid, pt.tgname "触发器名",
pp.oid functionoid, pp.proname "触发器函数名"
from pg_trigger pt
join pg_class pc on pc.oid=pt.tgrelid
join pg_namespace pn on pc.relnamespace = pn.oid
left join pg_proc pp on pt.tgfoid=pp.oid
where tgisinternal=false -- 不是系统创建的
and pn.nspname ='schema_name';
命令行方式：
MySQL数据库脚本导出：
mysqldump -u username -p dbname > xx.sql
MySQL数据库脚本导入：
第一种方法：
mysql -u username -p dbname < xx.sql
第二种方法：
mysql -u username -p dbname 登陆到MySQL数据库
use newdb(使用要导入的库)
source D:/DB/xx.sql
root@ivan:~# ls -l /bin/sh
lrwxrwxrwx 1 root root 4 11月 30 10:13 /bin/sh -> dash
root@ivan:~# sudo dpkg-reconfigure dash
选<否>
更改后确认
root@ivan:~# ls -l /bin/sh
lrwxrwxrwx 1 root root 4 11月 30 10:34 /bin/sh -> bash
1、查询 Table的字段、字段注释以及所属的模式(兼容oracle user_col_comments)
--create view user_col_comments as --可改造成视图来兼容 user_col_comments
select pn.nspname, pc.relname, pa.attname, pd.description
from pg_class pc
join pg_namespace pn on pc.relnamespace = pn.oid
JOIN pg_attribute pa ON pc.oid = pa.attrelid
left join pg_description pd on pd.objoid = pc.oid and pa.attnum = pd.objsubid
where pc.relkind='r' and pa.attnum > 0
and pc.relnamespace = ( select oid from pg_namespace where nspname = 'schema_name' ) --schema_name 所属模式名
-- and pc.relname = 'table_name'; --table_name 表名，select可单独查询一张表的
2、查询 Table、注释以及所属的模式（兼容oracle user_tab_comments）
select n.nspname schema_name,c.relname table_name,
pg_catalog.obj_description(c.oid, 'pg_class') as comments
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r';
postgresql jdbc：https://jdbc.postgresql.org/documentation/head/connect.html
postgresql docs：https://www.postgresql.org/docs/12/catalog-pg-class.html