Postgre - smile0821/learngit GitHub Wiki

高斯赋予某个用户某张表的查询权限

1. 进入高斯安装目录，并切换高斯用户

 su gdba

2. 登录项目管理库

 gsql -d titan_projectmgr -p 5431

3. 创建查询用户

 create role select_user login password 'abc=12345';

4. 赋予select_user这个用户bp_project_t表的查询权限

 grant select on bp_project_t to select_user;

5. 进入高斯data目录，添加select_user用户外部10.网段对titan_projectmgr库的访问权限

 vi pg_hba.conf
 host  all  titan    10.21.55.199/8            sha256

create user titan_select identified by 'DGec1e00b4#!';
grant select on BP_PROJECT_T_COPY TO titan_select;
grant select on BP_PROJECT_DETAIL_T_COPY TO titan_select;
grant select on BP_PROJECT_TOOLCODES_T_COPY TO titan_select;
grant select on WP_DRAFT_T_COPY TO titan_select;

数据表拷贝

create table B_EXTTABLE_T_COPY AS select * from B_EXTTABLE_T;

postgre数据库操作

select * from information_schema.schema;
show search_path;
创建用户：drop user if exists db_test_wy;
create user db_test_wy with password 'w123456';
grant db_test_wy to gdba;
psql --username=db_test_wy --dbname=titan_projectmgr --password
drop schema mytest cascade;
create schema mytest authorization db_test_wy;
create table mytest.test1(id  int);
insert into mytest.test1 values(1);
查询用户：\l titan_projectmgr
列出所有角色：\du 
列出所有schema: \n
列出所有表: \t
变更schema: alter schema mytest owner to db_test_wy;

设置字段默认值

ALTER TABLE wp_mo_t_0 ALTER COLUMN BP_PHASE_ID SET DEFAULT 'N/A'

查询某个库某张表的所有字段名(注意大小写)

   select COLUMN_NAME from information_schema.COLUMNS 
   where TABLE_SCHEMA =UPPER('public')  
   AND TABLE_NAME = UPPER('wp_draft_t') AND TABLE_CATALOG='TITAN_PROJECTMGR';

Mysql数据库中使用UNIQUE KEY语法，GaussDB需要通过工具替换成UNIQUE

   ALTER TABLE tbl_ParaDefGU ADD CONSTRAINT  UNIQUE KEY  (iCmdCode, iParaCode);
   转换为
   ALTER TABLE tbl_ParaDefGU ADD CONSTRAINT PK_tbl_ParaDefGU UNIQUE (iCmdCode, iParaCode);

时间转换

STR_TO_DATE('20190506000000','%Y-%m-%d%H:%i:%S') 转换为TO_DATE('20190506000000','yyyyMMddHH24miss') 

导入数据后设置最大值

select setval('WP_MO_T_0_ID_SEQ',(select max(id) from WP_MO_T_0));