postgresql - pg_relation_size 告诉我列不存在

Question

http://www.postgresql.org/docs/8.4/static/functions-admin.html说:

pg_relation_size
accepts the OID or name of a table, index or toast table, and returns the size in bytes

但是，当我将它与有效的表名称一起使用时，我收到错误:

column [table] does not exist...

我知道我的 table 存在，因为这样做

SELECT count(*) FROM [table]

返回一个有效的数字。有什么想法吗？

Answer 1

尽管原因不同，但我遇到了相同的错误。 pg_relation_size 不区分大小写，因此如果您有小写以外的任何内容，它将无法开箱即用:

postgres=> SELECT pg_size_pretty(pg_total_relation_size('MyTable'));
ERROR:  relation "mytable" does not exist
LINE 1: SELECT pg_size_pretty(pg_total_relation_size('mytable...
                                                     ^
postgres=> SELECT pg_size_pretty(pg_total_relation_size('"MyTable"'));
 pg_size_pretty
----------------
 225 MB
(1 row)

因此，为了使其在 SELECT 语句中起作用，您需要将表名称括在引号中:

postgres=> SELECT relname, nspname, pg_size_pretty(pg_relation_size('"' || relname || '"')) 
  FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
  WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_table_is_visible(c.oid) 
  ORDER BY c.relpages DESC;