pg_relation_size tells me column doesn't exist

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

pg_relation_size

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

However when I use it with a valid table name, I get the err开发者_C百科or:

column [table] does not exist...

I know my table exists, because doing

SELECT count(*) FROM [table]

returns a valid number. Any ideas?

---

I got the same error though the cause was different. pg_relation_size is case insensitive, so if you have anything other than lower case it will not work out of the box:

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)

So in order for this to work in a SELECT statement you need to enclose the table name in quotes:

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;

---

Try explicitely adding the schema (e.g. 'public') where the table is located in the pg_relation_size call.

Like this (untested):

select pg_relation_size(public.mytablename) from pg_tables