hi !
I would like to know how can I know on which tablespace belong a table ?
In "information_schema.tables", nothing about a table's tablespace ...
any idea ?
thx !
P.

Patrox <nospam (AT) nospammmmmmm (DOT) org> wrote:
You'll have to query the catalogs for that, something like:

SELECT COALESCE(tbs.spcname, '*default tablespace*') AS tbsname
FROM pg_catalog.pg_class AS t
JOIN pg_catalog.pg_namespace AS s
ON (s.oid = t.relnamespace)
LEFT OUTER JOIN pg_catalog.pg_tablespace AS tbs
ON (tbs.oid = t.reltablespace)
WHERE t.relname = 'tablename' AND s.nspname = 'schemaname';

Yours,
Laurenz Albe

thx a lot Laurenz but something must be missing somewhere as it
retrieves no rows so far :

tbsname
---------
(0 rows)

by the way I found "pg_tables" which tells amongst other things on which
tablespace is located a table; therefore it suits my needs.

Nevertheless as I'm busy discevering Postgres I'm interested to know a
bit more about what you used : pg_class, pg_namespace, pg_tablespace

Laurenz Albe a écrit :

Please don't top-post!

Patrox <nospam (AT) nospammmmmmm (DOT) org> wrote:
Maybe your table is not called 'tablename' or does not live in schema
'schemaname'?

Maybe you don't run 8.2? You never said which version you use.

I didn't know pg_tables. A useful view!

That can be easily done:

http://www.postgresql.org/docs/curre.../catalogs.html

Yours,
Laurenz Albe

Laurenz Albe a écrit :
.... silly of me, ur right ... ;-)

great ! thx !