table size and storage location

chuckee wrote:

1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR: tablespace "capture" does not exist

You're looking for pg_relation_size('capture') or
pg_total_relation_size('capture'). A tablespace is a named location for
creating objects.

2) how do I find out where the actual files for the database are stored on
my system?
Where is the default storage location?

The complete story is laid out in the docs here:
http://www.postgresql.org/docs/8.3/interactive/storage-file-layout.html

HTH,
Paul

paul rivers-2 wrote:

chuckee wrote:

1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR: tablespace "capture" does not exist

You're looking for pg_relation_size('capture') or
pg_total_relation_size('capture'). A tablespace is a named location for
creating objects.

Thanks but I still get the error 'ERROR: relation "capture" does not exist'
when trying these two alternative functions you mention above. There is
definitely a table called 'capture' in my database!
--
View this message in context: http://www.nabble.com/table-size-and-storage-location-tp16230697p16249922.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 25/03/2008, chuckee <mi6@orcon.net.nz> wrote:

Thanks but I still get the error 'ERROR: relation "capture" does not exist'
when trying these two alternative functions you mention above. There is
definitely a table called 'capture' in my database!

Are you sure you're connected to the right database
when running that?

--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

chuckee wrote:

paul rivers-2 wrote:

chuckee wrote:

1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR: tablespace "capture" does not exist

You're looking for pg_relation_size('capture') or
pg_total_relation_size('capture'). A tablespace is a named location for
creating objects.

Thanks but I still get the error 'ERROR: relation "capture" does not exist'
when trying these two alternative functions you mention above. There is
definitely a table called 'capture' in my database!

Is the schema for capture in your search_path? If not, include that in
the function call: function('yourschema.capture'). Otherwise, what
version are you on? I don't know when these functions were added;
perhaps you're stuck doing the math yourself on page counts in pg_class.

Paul

"Andrej Ricnik-Bay" <andrej.groups@gmail.com> writes:

On 25/03/2008, chuckee <mi6@orcon.net.nz> wrote:

Thanks but I still get the error 'ERROR: relation "capture" does not exist'
when trying these two alternative functions you mention above. There is
definitely a table called 'capture' in my database!

Are you sure you're connected to the right database
when running that?

Case-sensitivity is the other standard gotcha ... maybe the table is
really named "Capture" or something like that?

regards, tom lane