timz (2) [Avatar] Offline
#1
Hi there,

Great book got me up and running with PostGIS very quickly.

I have created a hierarchy of spatial data tables using the example in "Listing 3.6 Creating a parent with children" and it all works fine.

But I'm struggling to see how to add new tables into my hierarchy without recreating all the tables and indexes each time. Especially because you add "the parent without boundaries" and "more index goodness to trick the planner" after creating all the tables.

Any help would be gratefully received.

Thanks.

Tim
regina.leo (265) [Avatar] Offline
#2
Re: Section 3.2.3 Remodeling using Inheritance
Tim,

To add new tables to a hierarchy, just use the

INHERITS clause

So


CREATE TABLE somechildtable
(

CONSTRAINT pk_somechildtable PRIMARY KEY (gid)
)
INHERITS (someparenttable)

To add an existing table to a hierarchy , this article we wrote will hopefully clarify it.

http://www.postgresonline.com/journal/index.php?/archives/59-How-to-Inherit,-Unherit-and-Merge-Inherit.html

The main caveat with adding an existing table is that all the columns of the existing have to include the parent columns and be of same data type.

Leo
timz (2) [Avatar] Offline
#3
Re: Section 3.2.3 Remodeling using Inheritance
Thanks Leo

I read your article and it all seems to make sense.

The real reason I asked which perhaps I didn't explain very well in my original question was because in your example "Listing 3.6 Creating a parent with children" you wait until all the tables have been created before you do the following :-

(5)
ALTER TABLE hello.poi_all ADD poi_geom geometry;
(6)
CREATE INDEX idx_poi_all_poi_geom ON hello.poi_all USING GIST(poi_geom);

And you explain this as "5 .... when we do this PostgreSQL recognizes its children already have a geometry column with the same name and marries them together as if they were one column".

And I couldn't see how this could be done when you add a table later.

Tim
regina.leo (265) [Avatar] Offline
#4
Re: Section 3.2.3 Remodeling using Inheritance
Tim,

Okay I understand the confusion, but yes you can have the table inherit after the fact and in fact that is preferable if each of your tables has different geometry constraints. Your use case is probably a more common one than what we described.

If you create a table and include the inherit clause in there, the child inherits all the check constraints of its parent. (but it doesn't inherit the primary key or indexes by the way never does).


The other benefit of inheriting after the fact is for speed purposes. I'll have to check and see if we mentioned that. While you are mass loading a table, since you usually load without having an index in place, it would slow down the performance of querying the parent if you inherit before hand. So by inheriting after the fact, you don't impact query performance while you are loading, and inheriting is a fairly split second process even for a large table.

In fact when we are bulk loading a table, we usually uninherit it first, load and then reinherit.