Re: A question about inheritance and sequence

On Wed, Jun 24, 2009 at 01:43:20PM +0200, Marko Pahić wrote:
> Hello,
> I have two databases, and I want the same structure, and if I change the
> structure of one database it changes the structure of the other. In
> documentation I saw that the syntax goes something like this: CREATE TABLE
> "User" () INHERITS database2."User"; But it's not working like this, and I
> couldn't find the example. Can you please write the correct example?

If they're two separate databases, you can't have objects in one inherit from
objects in another. Two separate databases know nothing of each other. If, on
the other hand, you have separate schemas within the same database, objects in
one schema can inherit from objects in another. The partitioning documentation
shows an example of the basic syntax; if the tables involved are in different
schemas, simply write <schemaname>.<tablename> instead of just <tablename>

> The other problem I have if with sequence. I have this table structure:
> CREATE TABLE "Notes" (
> userkey character(40) NOT NULL,
> noteid SERIAL NOT NULL,
> note text,
> PRIMARY KEY (userkey, noteid),
> FOREIGN KEY (userkey) REFERENCES "User"(userkey) ON UPDATE CASCADE ON
> DELETE CASCADE
> );
>
> How can I make the noteid go from 1 to max for each user? Because if I add
> a row it goes +1 for all users? How would I do that the note id would go
> +1 for each userkey. Do I have to make nested queries and where would I do
> it?

You could theoretically make a sequence for each user, but that would likely
be so painful to manage that you'd quickly give it up. Note, by the way, that
having a table name that you're required to double-quote all the time is also
often found to be a big pain. The other way to do it involves issuing a query
each time you add a new Note to find out what the user's last noteid was, and
add one to that. That sounds both slow and painful, though perhaps not as
painful as maintaining one sequence per user.

- Josh / eggyknap