2021-06-17 10:35:14    22    0    0

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

 

2021-06-17 10:35:06    17    0    0

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

 

2021-06-16 16:57:19    19    0    0

令行方式:

        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

2021-06-16 16:19:26    3    0    0


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


2021-06-16 14:54:27    10    0    0

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

2021-06-16 13:06:25    12    0    0
2021-06-13 10:09:22    13    0    1