redraiment_个人页

提供两种思路：将Excel导出成CSV文件，然后通过COPY导入通过Python等语言定义UDF，实现Excel文件的解析，自动创建表并导入数据参考：https://stackoverflow.com/questions/20039856/import-excel-data-into-postgresql-9-3

根据题目的描述，假设表dm_org，有四个字段：org_id int：节点唯一编号。org_up_id int：父节点编号，根据decode(t.org_up_id , t.org_id , 0 , t.org_up_id)，推测根节点的org_up_id等于org_id。org_level int：层级？org_name varchar(64)：名称假设表中包含以下六行记录：org_idorg_up_idorg_levelorg_name111'A'212'B'312'C'423'D'533'E'654'F'利用题主的SQL查询结果如下：org_idorg_nameorg_levelorg_up_idorg_up_nameorg_levelleaf1A11A102B21A104D31A113C21A105E31A106F41A112B22B204D32B213C23C205E33C206F43C214D34D315E35E306F45E316F46F41因为没有指定start with语句，从功能上来说，是递归查询dm_org表所有记录的子节点。与之对应的PostgreSQL的查询语句是：with recursive leafs as ( select distinct parents.org_id as leaf_id from dm_org as parents left join dm_org as children on parents.org_id = children.org_up_id where children.org_id is null ), trees as ( select org_id, org_name, org_level, org_id as org_up_id, org_name as org_up_name, org_level as org_up_level from dm_org union all select dm_org.org_id, dm_org.org_name, dm_org.org_level, trees.org_up_id, trees.org_up_name, trees.org_up_level from dm_org inner join trees on dm_org.org_up_id = trees.org_id and dm_org.org_id != trees.org_id ) select trees.*, leafs.leaf_id is not null as leaf from trees left join leafs on trees.org_id = leafs.leaf_id order by org_up_id, org_id;定义了两个公共表表达式，其中：leafs：获得所有叶子节点。因为PostgreSQL没有提供connect_by_isleaf这样的关键字。trees：递归查询所有记录的子节点。

看问题描述，你是要直接开通PG数据库的直连用户？ 问题1：不需要创建schema，用create user xxx创建用户即可，相应的权限用grant来赋予； 问题2：如果空间不是问题，可以考虑修改postgresql.conf的日志选项log_statement = 'all'，这样就会把所有执行的语句都输出到日志文件中，配置如下： log_destination = 'csvlog' log_directory = 'pg_logs' log_filename = 'postgresql-%Y-%m-%d.log' log_rotation_age = 1d log_statement = 'all' 按照上面的配置，日志会输出到$PGDATA/pg_logs/postgresql-YYYY-MM-DD.log文件中，一天一个文件。 例如，我用redraiment用户在postgres数据库中执行了三条SQL，日志中得到结果如下： 2018-07-09 22:13:57.746 CST,'redraiment','postgres',11009,'[local]',5b436da3.2b01,1,'',2018-07-09 22:13:55 CST,3/9,0,日志,00000,'语句: create table users (id bigserial primary key, name text);',,,,,,,,,'psql' 2018-07-09 22:13:58.969 CST,'redraiment','postgres',11009,'[local]',5b436da3.2b01,2,'',2018-07-09 22:13:55 CST,3/10,0,日志,00000,'语句: insert into users (name) values ('Joe'), ('Alice'), ('Wiki');',,,,,,,,,'psql' 2018-07-09 22:14:00.546 CST,'redraiment','postgres',11009,'[local]',5b436da3.2b01,3,'',2018-07-09 22:13:55 CST,3/11,0,日志,00000,'语句: select * from users;',,,,,,,,,'psql'

因为问题描述中没有提到数据模型，我假设alarm表与alarm_date表的结构如下：create table alarm( id serial primary key, description text, hits int ); comment on column alarm.hits is '告警的次数'; create table alarm_date ( id serial primary key, aid int references alarm, create_time timestamp );假设alarm与alarm_date是“one to many”的关系，则查询2018-11-11之后发生的告警，并按告警次数倒序的查询SQL如下：select id, description, hits from alarm inner join ( select distinct aid from alarm_date where create_time > '2018-11-11' ) as records on alarm.id = records.aid order by hits desc

猜测md5_wp_cur这个字段的类型是varchar(32)，所以代码中array[md5_wp_cur]::varchar[]转换成非32位varchar的数组时，会有类型不匹配的问题。可以改成：... array[md5_wp_cur]::varchar(32)[], ... (path || g.md5_wp_cur)::varchar(32)[],