postgres - truncate, refresh materialized view 暴力清空一个表 ERROR: update or delete on table "" violates foreign key constraint ...

访问量: 26

refer to: https://stackoverflow.com/questions/14182079/delete-rows-with-foreign-key-in-postgresql

1. 在postgres中,如果有外键约束的话,需要先删除外键表,再删除本表

2. 或者使用 truncate <table_name> cascade

对于量大的表(例如 > 1 G), truncate 可以在0.1内搞定。

13G的表,在0.2s 内搞定。 太爽了。

3. 对于一些view, 则可以直接把view删掉:

refresh materialized view <view_name> with no data;

4. 如何获得所有表的大小?

4.1 运行这个SQL; https://wiki.postgresql.org/wiki/Disk_Usage

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20

4.2 获得结果如下:

4.3 正常删除就行了。(判断下是table还是view, 可以先统一按照table来处理,然后再换成refresh view ...)

订阅/RSS Feed

Subscribe