Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

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.
0
DerekWatling
Asked:
DerekWatling
  • 6
  • 3
3 Solutions
 
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now