1.删除字段SQL语句如下：ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME
例如删除：user_basic_table 表中的userlock 列：ALTER TABLE user_basic_table DROP COLUMN userlock
2.postgre数据操作语句
UPDATE iam.iamfunction set functionvisable=0 WHERE id=1036
SELECT * FROM approvalrelation WHERE "id">0 AND "id"<6 ORDER BY id DESC
DELETE FROM iam.approvalrelation WHERE "id"=73
3.将相同字段的表数据，插入到另一张表
INSERT INTO t_treasury_log_2019_01(SELECT * from t_treasury_log_2019_07_01);
4.服务端备份数据库中的一张表
pg_dump -U postgres -h 127.0.0.1 -p 5432 -c -Fp -t audit_201907.t_treasury_log_2019_07 -E UTF-8 -f /opt/t_treasury_log_2019_07_36.sql auditdb
5.数据库备份和恢复
备份整库：pg_dump -U username --inserts DBname> filename.sql
pg_dump -U user --inserts test_db> /opt/test_db_20170518.sql
恢复整库：先建库，再执行 psql –U username DBname < filename.sql
备份并压缩整库：pg_dump -U username DBname | gzip > filename.gz
对应的恢复整库：gunzip -c filename.gz | psql -U username DBname
备份某个schema及其数据：
pg_dump -U username –n schema_name --inserts DBname > filename.sql
5.查询数据库中所有表名：select * from pg_tables WHERE schemaname = 'public' and tablename LIKE 'test_table%'
6.给查询出的时间减去1年
INSERT INTO t_oper_2017_09_01(SELECT sid,seq,hostid,hostip,direct,eventid,risk_level,(logtime - INTERVAL '1 year') as logtime,operate,obj,result,duration,vdata from t_oper_2018_09_01);