[rod k]

Here's the need:

Table contains addresses that MUST support both US and Canadian addresses.

state and province field are declared as null and reference appropriate tables (state and province).
country is not null and checks for only values 1 and 2 (US and Canada)
zipcode is not null and checked vs regular expression
postalcode is not null and checked vs regular expression

Now, what I need is a table constraint that will verify that state and zipcode are not null if country==1 and that province and postalcode are not null if country==2.

This is the alter query I used:

ALTER TABLE addresses
ADD CHECK (
CASE
	WHEN country=1 THEN state IS NULL AND zipcode IS NULL
	WHEN country=2 THEN province IS NULL AND postalcode IS NULL
	ELSE true
END
)

But I get the following error:

ERROR: cache lookup of attribute 15 in relation 110121 failed

BTW, two tables inherit from this table, if that makes a difference.

Anybody experience this or have any suggestions?

[rod k]

1) Postgres does NOT propogate constraints to children when you modify the parent

2) In fact, adding a table constraint to any table (with or without children) is often not possible with complicated constraints.

If ne1 is interested I ended up having to drop the table (and it's children) and recreate it. Below is the CREATE query:

create table addresses
(
    addressID SERIAL8 NOT NULL PRIMARY KEY,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(64) NOT NULL,
    state INT2 REFERENCES states(stateID) ON UPDATE CASCADE,
    canProv INT2 REFERENCES provinces(provID) ON UPDATE CASCADE,
    provName VARCHAR(64),
    zip CHAR(10),
    canZip CHAR(7),
    postalCode VARCHAR(12),
    country INT2 NOT NULL REFERENCES countries(countryID) ON UPDATE CASCADE,
    CONSTRAINT checkAddress
        CHECK
        (
            CASE
                WHEN country=1 
                    THEN state IS NOT NULL 
                    AND zip IS NOT NULL 
                    AND zip ~ '^[0-9]{5}(-[0-9]{4})?$' 
                    AND canProv IS NULL 
                    AND canZip IS NULL
                    AND provName IS NULL
                    AND postalCode IS NULL
                WHEN country=2 
                    THEN canProv IS NOT NULL 
                    AND canZip IS NOT NULL 
                    AND canZip ~ '^[A-Z][0-9][A-Z] [0-9][A-Z][0-9]$'
                    AND zip IS NULL
                    AND state IS NULL
                    AND provName IS NULL
                    AND postalCode IS NULL
                WHEN country>2 
                    THEN state IS NULL
                    AND zip IS NULL
                    AND canProv IS NULL
                    AND canZip IS NULL
                ELSE FALSE
            END
        )
)

[rycamor]

Yes, unfortunately, PostgreSQL's inheritance mechanism is really one of the oldest parts of the code, and according to Bruce M. and Tome Lane, has not really been updated since PostgreSQL was turned into an open source project. Basically, that is pure Ingres code, from the 80's. And no, inheritance in PG never propagates as changes are made to the parent. It is a very limited inheritance model.

FYI, there has been some discussion in the [pgsql-hacker] lists about whether to start extending the inheritance behavior, or to eventually just drop inheritance altogether . (Some claim that a proper relational database should be able to handle any logical operation that one would use inheritance for.) I haven't thought through that one all the way, but I hope PG keeps inheritance, and extends it according to the ideas in The Third Manifesto by Chris Date.

Anyway, at the moment, I am a little leery of depending too much on inheritance until I see where they are going with it.

Also, PostgreSQL in general is not really "change-friendly". Often, when I am making big changes in structure, such as with relationships and constraints, I just dump the whole DB, re-arrange things, and re-create.

The error message you received above is familiar to anyone who has tried too many structural changes on a live PG database. Oftne, if you drop and re-create a table that other objects are depending on, it causes problems like this. (functions especially don't like tables to disappear and re-appear) I'm hoping version 7.3 will take care of some of these problems.

[rod k]

there has been some discussion in the [pgsql-hacker] lists

I read some of those when I was searching for solutions, but the posts were a bit dated. Inheritence is a powerful feature and I hope it stays but it'll definately need some work before it's really useful. Not being able to have modified constraits cascade to the children is a major drawback.

PostgreSQL in general is not really "change-friendly".

As I've discovered
I've stuck with MySQL longer than I should have. Coding around transactions and constraints was fine as long as I was the only one writing the code that would access the DB. I really wanted to change but the benefit vs cost (time spent) didn't make it possible. (Un)fortunately, I know have to worry about other coders accessing DBs I design, so DB constraints are a must.