Re: CREATE TABLE with REFERENCE - Mailing list pgsql-general

> Your programmers must be really smart :-)
> Are you saying that you have never seen a person writing a piece of sql
> like:
> insert into mytable (id, data) select max(id) + 1 from mytable, 'mydata'
> ???
>
> If so, you must be really lucky :-)
>

I would never hire such a person.

> Exactly. But 'copy from ' does *not* - so, after you have loaded, your
> sequnce next_val () will return 1.

We just use pg_dump, which re-sets the sequence to its proper value.

> >The OID type/column is ideal for this.
> >
> No, it isn't. Not all tables have oids. The ones that do, do not
> guarantee, that they will be unique.

I wasn't indicating that the current implementation was ideal.  In fact, I
indicated exactly the opposite.  I was talking about the idea behind it.

> >even do record merges with automatic database support.
> >
> What do you mean by "record merges"?
> Any meaning of that phrase I can imagine can be easily done with the
> currently supported database features... so, you must mean something
> different by that, I assume...

Kind of.  Lets say that you build a commodity database application, which
has customer tables, invoice tables, etc.  Let's say you had two
customers, A and B, who merged, and you wanted to merge their records
together.  You could write a program to do it, but it would be specific to
customer records, and if other kinds of record merges were needed you
would have to write separate programs for those (say, contact merges or
something).  So, you have to write a custom application for every type of
record merge, and it won't even attempt to take into account any custom
tables taht someone else defines.

Let's say that instead you used the following pattern when building your
database:

* All rows had OIDs
* All foreign keys that related to OIDs had a specific, OID type (not just
generic integer)

Now, if you want to merge record 1345 with record 1765, and you wanted
1765 to be the new master, you could do the following:

Search the database catalog for columns of type OID.
For each instance, update all rows having 1345 to have 1765 instead
Not each instance this generates an exception
If successful, great, if not, report back which rows had integrity
problems after the merge.
Mark record 1345 as being deleted.  In addition, you could have a generic
"merge" table which recorded every record and what record it was merged
into.

With this, you can apply this generic merge function to any record of any
table at all, and it will continue to work in user-defined custom modules.

> No. They would have a base class of "Object" (or whatever), and the
> 'notes' would be linked to the Object.
> This would in fact, be a *beatiful* solution... it's a shame really that
> it doesn't work.

Hmm, on the one hand you think this is a beautiful solution, but on the
other hand you reject my notion that the database does not have all the
power it could?

> I am wonderring if what postgres does with those inherited FK
> constraints is specified by the standard, or if it is just an
> implementation feature, that can be improved...

I'm not sure that inheritance is part of any standard.