Re: Tablespaces oddity?

On Wed, 29 Mar 2006 01:36 am, Tom Lane wrote:
> Philip Yarra <philip(at)utiba(dot)com> writes:
> > Someone else might be able to see a better way to write this query, but I
> > think it would be good if \d could show this information, when you really
> > want to know which tablespace an object is on.
>
> If \d doesn't say anything then the table is in the database's default
> tablespace. I see nothing wrong with that, and I do object to
> cluttering \d output with information that will be of no interest to
> people not using tablespaces.

OK, how about on \d+, if the object is not on pg_default or pg_global, print
the tablespace that this object is on? That way, people not using tablespaces
won't ever see it.

> > Note also that \l won't show you the tablespace for a DB, so you need
> > to query pg_database to even know which is the default tablespace for
> > a DB.
>
> I wouldn't object to adding default tablespace to \l output, or maybe \l+.

OK, not fussed which one it's on, so long as it's there - this should do it
for \l+

SELECT d.datname as "Name",
r.rolname as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
pg_catalog.obj_description(d.oid, 'pg_database') as "Description",
t.spcname as "Tablespace"
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
LEFT JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid;

On a related note: is there a simple way to show all objects on a given
tablespace? If not, would other people also see this as useful?

Regards, Philip.

--

"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan

-----------------
Utiba Pty Ltd
This message has been scanned for viruses and
dangerous content by Utiba mail server and is
believed to be clean.