Thread: table size and storage location
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
Also, 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
--
View this message in context: http://www.nabble.com/table-size-and-storage-location-tp16230697p16230697.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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