Unique/Primary key not inherited in partition, workaround?

From: Nicholas Wilson <nwilson5(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unique/Primary key not inherited in partition, workaround?
Date: 2012-11-07 18:58:02
Message-ID: CABgBcYPYkKnqUh+jXb-iqmgp0guBruEG91=x7Wt3i988DiKfpw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Regarding the caveats here
http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS

I am attempting to logically structure my location data. Say for example I
have cities/states/countries. I have objects that reference a location, but
at any level. An object may reference a city or it may reference a state,
depending on how granular we know its location to be. If the city is known
for an object, the state and country can be inferred, so the object need
not point to all levels in fact that would be redundant and require
checking consistency. Ideally, the object would have a foreign key
reference to a generic "location" table (which would have child tables:
cities, states, countries). The cities, states and countries could then
have foreign keys pointing to each other (cities point to states, which
point to countries).

Does anyone know a workaround for my problem? Initially I was thinking a
single locations table with a location "type" and a parent location id
(references itself) although that's not ideal because I then have to do
checks to ensure location type country does not end up being "contained" in
a city. But perhaps I can write checks that ensure that never happens.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2012-11-07 18:59:05 Re: Plug-pull testing worked, diskchecker.pl failed
Previous Message Ryan Delaney 2012-11-07 18:57:48 Re: How to verify pg_dump files