headers
George Woodring | 24 May 2012 20:01
Favicon

Inherited table identification possible

Is it possible to identify which inherited table data came from in a query?  We have a table that has 3 inherited tables attached to it.  I am looking for a way to identify the source of the data.  


My only thought would be to add a column to the tables that identify the table.  I was just checking if there was a way to do it without the column.

Thanks,
George



--
iGLASS Networks
www.iglass.net
Permalink | Reply |
headers
Jonathan S. Katz | 24 May 2012 20:09
Favicon

Re: Inherited table identification possible

On May 24, 2012, at 2:01 PM, George Woodring wrote:

Is it possible to identify which inherited table data came from in a query?  We have a table that has 3 inherited tables attached to it.  I am looking for a way to identify the source of the data.  

My only thought would be to add a column to the tables that identify the table.  I was just checking if there was a way to do it without the column.

There is a special column on every row of your table called "tableoid" which identifies the original relation the row belongs to.  This identifier can be matched up to the name of the relation in the pg_class table.


Jonathan

Permalink | Reply |
headers
Stephen Belcher | 24 May 2012 20:11
Gravatar

Re: Inherited table identification possible

Yes, the system column "tableoid" identifies the actual table in which the row is stored. If you cast this to "regclass" you'll get the name of the table that the row is stored in:


SELECT tableoid::regclass FROM base_table;

There's more documentation on this available at http://www.postgresql.org/docs/9.1/static/ddl-inherit.html (for version 9.1, at any rate: season to taste with your version of PG)

Hope this helps,
--Stephen


On Thu, May 24, 2012 at 2:01 PM, George Woodring <george.woodring <at> iglass.net> wrote:
Is it possible to identify which inherited table data came from in a query?  We have a table that has 3 inherited tables attached to it.  I am looking for a way to identify the source of the data.  

My only thought would be to add a column to the tables that identify the table.  I was just checking if there was a way to do it without the column.

Thanks,
George



--
iGLASS Networks
www.iglass.net

Permalink | Reply |
headers
George Woodring | 25 May 2012 15:40
Favicon

Re: Inherited table identification possible

Thanks for all of the help, this was exactly what I was looking for.


George

On Thu, May 24, 2012 at 2:11 PM, Stephen Belcher <sycobuny <at> malkier.net> wrote:
Yes, the system column "tableoid" identifies the actual table in which the row is stored. If you cast this to "regclass" you'll get the name of the table that the row is stored in:

SELECT tableoid::regclass FROM base_table;

There's more documentation on this available at http://www.postgresql.org/docs/9.1/static/ddl-inherit.html (for version 9.1, at any rate: season to taste with your version of PG)

Hope this helps,
--Stephen



On Thu, May 24, 2012 at 2:01 PM, George Woodring <george.woodring <at> iglass.net> wrote:
Is it possible to identify which inherited table data came from in a query?  We have a table that has 3 inherited tables attached to it.  I am looking for a way to identify the source of the data.  

My only thought would be to add a column to the tables that identify the table.  I was just checking if there was a way to do it without the column.

Thanks,
George



--
iGLASS Networks
www.iglass.net




--
iGLASS Networks
www.iglass.net
Permalink | Reply |

Gmane