pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedError in getting top 10 biggest table
2+ messages / 2 participants
[nested] [flat]
* Error in getting top 10 biggest table
@ 2015-07-25 07:03 Ankur Kaushik <ankurkaushik@gmail.com>
2015-07-25 12:15 ` Re: Error in getting top 10 biggest table Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 1 reply; 2+ messages in thread
From: Ankur Kaushik @ 2015-07-25 07:03 UTC (permalink / raw)
To: pgsql-admin
Hi ,
I am executing below query to get top 10 biggest size table but getting
error
SELECT table_name,pg_relation_size(table_schema || '.' || table_name) as
size FROM information_schema.tables WHERE table_schema NOT IN
('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;
*ERROR: relation "public.contacts" does not exist*
*Original Table : Contacts*
It seems problem is with CAPS letter in Postgres table .
So any possibilities to execute this Caps letter table without any error
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Error in getting top 10 biggest table
2015-07-25 07:03 Error in getting top 10 biggest table Ankur Kaushik <ankurkaushik@gmail.com>
@ 2015-07-25 12:15 ` Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Sabino Mullane @ 2015-07-25 12:15 UTC (permalink / raw)
To: pgsql-admin; +Cc: ankurkaushik@gmail.com
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Ankur Kaushik asked:
> SELECT table_name,pg_relation_size(table_schema || '.' || table_name) as
> size FROM information_schema.tables WHERE table_schema NOT IN
> ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;
> *ERROR: relation "public.contacts" does not exist*
The problem is that you must pass the exact name to the pg_relation_size
function. The information_schema.tables returns the non-canonical
lowercase version. One solution is to make sure that you wrap the
table_name column in the quote_ident function like so:
SELECT table_name,
pg_relation_size(table_schema || '.' || quote_ident(table_name))
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC LIMIT 10;
Another simpler way is to simply use the system catalogs directly, and
trust that both the information_schema and pg_catalog schemas are not
going to have large enough tables to affect your query. In which case,
you can also pass the OID to the pg_relation_size column. It's also nice
to throw in a pg_size_pretty column to make the output a little more
user-friendly:
SELECT relname, pg_relation_size(oid), pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_relation_size(oid) DESC LIMIT 10;
The relkind = 'r' is needed to limit it to only tables, but indexes are
usually quite large as well, so you sometimes want to show those as well.
Just add another column to show you the type (r=table,i=index):
SELECT relname, relkind, pg_relation_size(oid), pg_size_pretty(pg_relation_size(oid))
FROM pg_class
ORDER BY pg_relation_size(oid) DESC LIMIT 10;
Finally, make note of the pg_total_relation_size() function, which acts like
pg_relation_size but includes the indexes (slightly simplified: see
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
for more details)
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201507250813
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAlWzfacACgkQvJuQZxSWSshPNQCgqOCbC7B7hNzqLu7N3DHXZ+o6
FEUAoLSMIj7yk3t3cQzO07iCcloLaymt
=x6lc
-----END PGP SIGNATURE-----
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-07-25 12:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-07-25 07:03 Error in getting top 10 biggest table Ankur Kaushik <ankurkaushik@gmail.com>
2015-07-25 12:15 ` Greg Sabino Mullane <greg@turnstep.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox