Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-10-23
9
Medium Priority
?
519 Views
Last Modified: 2014-11-04
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.
0
Comment
Question by:DerekWatling
  • 6
  • 3
9 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 2000 total points
ID: 40399638
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
 

Author Comment

by:DerekWatling
ID: 40399698
All my table/column names are lowercase with underscores. Can't stand having to put quotes around anything except strings.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40399786
Can you post the DDL?  e.g. Create Table ...
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:DerekWatling
ID: 40402036
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
 

Author Comment

by:DerekWatling
ID: 40402093
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
 

Assisted Solution

by:DerekWatling
DerekWatling earned 0 total points
ID: 40402113
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40404797
Glad you solved it!
0
 

Accepted Solution

by:
DerekWatling earned 0 total points
ID: 40415112
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
 

Author Closing Comment

by:DerekWatling
ID: 40421167
Daniel's suggestion about capitalisation was part of the final solution, but also needed the schema included to work in all situations.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Screencast - Getting to Know the Pipeline
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question