数据库postgres使用

find / -name postgresql.conf  # 在 / 下搜索postgresql.conf的位置，可能在 /var/lib/postgresql/data
cat 文件名 | grep “关键词” # 查找关键词所在行
grep -i “关键词” 文件名 # 例：grep -i “train” log.log　　# 返回log.log中包含train的所有行

psql -U postgres # 进入postgres

# 安装vim
apt-get update
apt-get install vim

CREATE UNIQUE INDEX 索引名 on 表名 (字段名); -- 创建唯一索引

-- 给表3d_document_block的name字段创建btree索引，索引名是IDX_3d_document_block_name
create index IDX_3d_document_block_name on "3d_document_block" using btree(name);  

-- 给字段设置默认值
ALTER TABLE "3d_document_cate" ALTER COLUMN create_on SET DEFAULT current_timestamp;

ALTER TABLE "3d_document_cate" ALTER COLUMN create_by SET DEFAULT 'sys';

https://studygolang.com/articles/5455
创建索引参考： https://www.cnblogs.com/alianbog/p/5621749.html

-- Table: threed_document_cate

-- DROP TABLE threed_document_cate;

CREATE TABLE threed_document_cate
(
    id varchar(64) PRIMARY KEY NOT NULL,
    create_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by varchar(64) NOT NULL default 'sys',
    change_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    change_by varchar(64) NOT NULL default 'sys',
    is_deleted char(1) NOT NULL default 'n',
    name varchar(255) NOT NULL default '',
    cate_type varchar(255) NOT NULL DEFAULT '',
    parent_id varchar(255) NOT NULL DEFAULT ''
);
COMMENT ON TABLE threed_document_cate
    IS '标签分类表';
comment on column threed_document_cate.id is '主键ID';
comment on column threed_document_cate.create_on is '创建时间';
comment on column threed_document_cate.create_by is '主键人';
comment on column threed_document_cate.change_on is '更新时间';
comment on column threed_document_cate.change_by is '更新人';
comment on column threed_document_cate.is_deleted is '是否删除（n|y）';
comment on column threed_document_cate.name is '名称';
comment on column threed_document_cate.cate_type is '类型(group、tag、project)';
comment on column threed_document_cate.parent_id is '上级';
create index IDX_3d_document_cate_name on "threed_document_cate" using btree(name); 
create index IDX_3d_document_cate_type on "threed_document_cate" using btree(cate_type); 
create index IDX_3d_document_cate_parent_id on "threed_document_cate" using btree(parent_id); 

-----------------------


-- Table: threed_document_block

-- DROP TABLE threed_document_block;

CREATE TABLE threed_document_block
(
    id varchar(64) PRIMARY KEY NOT NULL,
    create_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by varchar(64) NOT NULL default 'sys',
    change_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    change_by varchar(64) NOT NULL default 'sys',
    is_deleted char(1) NOT NULL default 'n',
    name varchar(255) NOT NULL default '',
    block_version varchar(16) NOT NULL DEFAULT '',
    title varchar(128) NOT NULL DEFAULT '',
    block_desc varchar(1024) NOT NULL DEFAULT '',
    block_tags varchar(1024) NOT NULL DEFAULT '',
    group varchar(64) NOT NULL DEFAULT '',
    can_export char(1) NOT NULL DEFAULT 'y',
    block_type varchar(64) NOT NULL,
    other_prop text NOT NULL DEFAULT '',
    content text NOT NULL
)

TABLESPACE pg_default;

COMMENT ON TABLE threed_document_block
    IS '文档块信息表';


-- DROP INDEX IDX_3d_document_block_name;
-- DROP INDEX IDX_3d_document_block_title;
-- DROP INDEX IDX_3d_document_block_tags;
create index IDX_3d_document_block_name on "threed_document_block" using btree(name); 
create index IDX_3d_document_block_title on "threed_document_block" using btree(title); 
create index IDX_3d_document_block_tags on "threed_document_block" using btree(block_tags); 
create index IDX_3d_document_block_group on "threed_document_block" using btree(group); 
create index IDX_3d_document_block_can_export on "threed_document_block" using btree(can_export); 
create index IDX_3d_document_block_type on "threed_document_block" using btree(block_type); 
create index IDX_3d_document_block_version on "threed_document_block" using btree(block_version); 

select 'sys'=ANY(string_to_array('sys,hh',',')) -- true 查询字符串sys是否在'sys,hh'字符串里

sqlStr := `select content from threed_document_block 
	where  {{if .Tags}}'{{.Tags}}'=ANY(string_to_array(block_tags,',')){{end}}
	{{if .Project}}and project='{{.Project}}'{{end}}
	{{if .Title}}and title = '{{.Title}}'{{end}}
	{{if .Name}}and name = '{{.Name}}'{{end}}
	and block_type = '{{.BlockType}}'
	and is_deleted = 'n'
	`
	sqlStr = base.RenderText(sqlStr, params)

WITH RECURSIVE recursion (id, name, parent_id,pname) AS
(
 
  SELECT T1.id,concat('{"name":"',T1.name,'"}') as name,  T1.parent_id,t4.name as pname
	  from threed_document_cate T1
	 left join threed_document_cate t4 on T1.parent_id = t4.id
	 where T1.id='节点ID'  and T1.cate_type = 'group'
  UNION ALL
  SELECT T2.id, concat('{"name":"',T2.name,'"}') as name, T2.parent_id,t5.name as pname
    from recursion T3,threed_document_cate T2
	 left join threed_document_cate t5 on T2.parent_id = t5.id
	 WHERE T2.parent_id=T3.id	 
)
SELECT concat('{"name":"',COALESCE(min(T.pname),''),'","children":[',string_agg(T.name,','),']','}')  as groupNames
  FROM recursion T group by T.parent_id

with recursive（递归查询） 参考：http://www.cppcns.com/shujuku/postgresql/259441.html

with recursive node(id,name,parent_id,pname,branch) as
(
	select id,name,parent_id,cast('' as varchar(255)) as pname,cast(name as varchar(1024)) as branch
	from threed_document_cate where cate_type = 'group' and id = 'groupid1'
	union all
	select c.id,c.name,c.parent_id,p.name as pname,cast((p.branch || '~' || c.name) as varchar(1024)) as branch
	from node as p,threed_document_cate as c where c.parent_id = p.id
)select id,name,parent_id,pname,branch,(length(branch)-length(replace(branch,'~',''))) as levels from node;