table size and storage location

[chuckee]

Hi,
I have two questions:

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

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

Thanks!
Charlie

[paul rivers-2]

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


-
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[chuckee]

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!

[Andrej Ricnik-Bay]

On 25/03/2008, chuckee <mi6@...> 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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[paul rivers-2]

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




--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Tom Lane-2]

"Andrej Ricnik-Bay" <andrej.groups@...> writes:
> On 25/03/2008, chuckee <mi6@...> 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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general