数据库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;