PostgreSQL admin: finding the parent table name for a TOAST table given just then numeric id of the TOAST table.

To find the parent table name given a TOAST table (which is kind of like a spillover table, for data too big to put in the regular table – it’s too big to fit into one row; so it goes into multiple rows and is compressed) which has a name like pg_toast_12513885, cast the OID of the toast table to “regclass”*.

For example: Given “monkeys” table which spills over to pg_toast_12513885:

select 12513885::regclass;
regclass
———-
monkeys
(1 row)

*”regclass” stands for registered “class”. Per RhodiumToad on #postgresql,
for reasons too complicated to explain, “class” is used to mean “relation”.

Reference: “casting a table’s OID to regclass is handy for symbolic display of a numeric OID”
http://www.postgresql.org/docs/8.4/static/datatype-oid.html

Update: The more offical/strict way, that does not rely on the implementation artifact that TOAST numeric ID is embedded in the TOAST symbolic name, is:

select oid::regclass from pg_class where reltoastrelid=’pg_toast_12513885′::regclass;

But it’s easier to remember “select NUMBER::regclass”. I’ll use that, but if it ever breaks, I’ll go back to the strict/formal way.