Re: Partitioning

On 15 Sep 2004 23:55:24 -0400, Greg Stark <gsstark(at)mit(dot)edu> wrote:
>
> "J. Andrew Rogers" <jrogers(at)neopolitan(dot)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