From: | Mike Rylander <mrylander(at)gmail(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Partitioning |
Date: | 2004-09-16 10:58:32 |
Message-ID: | b918cf3d04091603581afece67@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Schaber | 2004-09-16 13:38:21 | Re: Data Warehouse Reevaluation - MySQL vs Postgres -- |
Previous Message | Markus Schaber | 2004-09-16 10:39:04 | Re: Data Warehouse Reevaluation - MySQL vs Postgres -- |