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
409 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Can you post the DDL?  e.g. Create Table ...
0
 

Author Comment

by:DerekWatling
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:DerekWatling
Comment Utility
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
Comment Utility
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
Comment Utility
Glad you solved it!
0
 

Accepted Solution

by:
DerekWatling earned 0 total points
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now