On 15 Sep 2004 23:55:24 -0400, Greg Stark <gsstark@mit.edu> wrote:
>
> "J. Andrew Rogers" <jrogers@neopolitan.com> writes:
>
> > We do something very similar, also using table inheritance
>
> I have a suspicion postgres's table inheritance will end up serving as a good
> base for a partitioned table feature. Is it currently possible to query which
> subtable a record came from though?
From the docs on http://www.postgresql.org/docs/7.4/static/ddl-inherit.html :
... In some cases you may wish to know which table a particular row
originated from. There is a system column called TABLEOID in each
table which can tell you the originating table:
SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;
which returns:
tableoid | name | altitude
----------+-----------+----------
139793 | Las Vegas | 2174
139793 | Mariposa | 1953
139798 | Madison | 845
(If you try to reproduce this example, you will probably get different
numeric OIDs.) By doing a join with pg_class you can see the actual
table names:
SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 and c.tableoid = p.oid;
which returns:
relname | name | altitude
----------+-----------+----------
cities | Las Vegas | 2174
cities | Mariposa | 1953
capitals | Madison | 845
--miker