Postgres Error: pg_table_size('x') gives 'Error: relation "x" does not exist' but I can query "x"

What would cause certain Postgres Database Object Management Functions to give the error:
ERROR:  relation "x" does not exist
even though I can query data in relation "x"?

I am experiencing this with pg_table_size(), pg_relation_size(), pg_total_relation_size() on certain relations.
DerekWatlingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
What is the actual name of the table / relation?  Could capitalization issues be your problem?

In Postgres, things go most smoothly if everything is in lowercase.  Mixed-case names cause problems in Oracle as well:
http://www.sadalage.com/blog/2013/08/06/mixed_case_database_objects/

And Postgres is very similar to Oracle.
0
DerekWatlingAuthor Commented:
All my table/column names are lowercase with underscores. Can't stand having to put quotes around anything except strings.
0
Daniel WilsonCommented:
Can you post the DDL?  e.g. Create Table ...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

DerekWatlingAuthor Commented:
Here is 1 example:
create table lookup.deedsoffice (
	deeds_office_id int not null,
	description text not null,
	code varchar(3) not null,
	code_cad varchar(1) not null default '',
	code_deeds_summary text not null,
	primary key (deeds_office_id)
);

Open in new window

0
DerekWatlingAuthor Commented:
It seems to work only in the "public" schema!

I have the following query, and every time I try a schema other than "public" I get the error.

SELECT *, pg_total_relation_size(table_name) as size
FROM information_schema.tables
WHERE table_schema = 'public'

Open in new window

0
DerekWatlingAuthor Commented:
Found the problem. I am working through "PostgreSQL 9 Administration Cookbook". They give

SELECT table_name, pg_relation_size(table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog')
ORDER BY size DESC
LIMIT 10;

Open in new window


as the way to find the 10 biggest tables in the database, but the top line should be

SELECT table_name, pg_relation_size(table_schema || '.' || table_name) as size

Open in new window

0
Daniel WilsonCommented:
Glad you solved it!
0
DerekWatlingAuthor Commented:
I found another issue where some relation names contained spaces. The solution is to add double quotes around schema name and relation name. This would also cater for names containg capitals.

SELECT table_name, pg_relation_size('"' || table_schema || '"."' || table_name || '"') as size

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DerekWatlingAuthor Commented:
Daniel's suggestion about capitalisation was part of the final solution, but also needed the schema included to work in all situations.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.