pgsql 经常用语句

web css 报错，没有登陆验证等问题

delete FROM "ir_attachment" where mimetype like '%/css%';
delete FROM "ir_attachment" where mimetype like '%/javascript%';

There are 4 other sessions using the database 解决方式

select pg_terminate_backend(pid) from pg_stat_activity where DATNAME = 'temp_test_yang';

back.sh

cur_time=date +%y_%m_%d_02_00_00
docker exec -t jc-postgres pg_dump -U odoo -c jc > /home/ceshi/backup/jc_$cur_time.sql;

查询固定日期上线率，并导出到容器中.csv文件，
docker cp 容器名:/容器目录地址 宿主机地址
COPY(select ...)TO '/home/tmp/lift0303.csv' WITH CSV HEADER;

查询数据库占内存最大的20张表

SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' ORDER BY pg_table_size(oid) DESC LIMIT 20;

sql清空表数据的三种方式：

1、truncate–删除所有数据，保留表结构，不能撤销还原
2、delete–是逐行删除速度极慢，不适合大量数据删除
3、drop–删除表，数据和表结构一起删除，快速

语法

truncate table 表名
delete from 表名
delete from 表名 where 列名="value "
drop form 表名