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
461 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 500 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Limit a query to 10 rows per column value 4 733
Don't see the folder 24 331
Syntax how to select past 7 days from PostgreSQL table 4 68
postgreSQL (pgadmin 4) script problem 2 123
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.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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