1

Suppose I have a table Bars inheriting a table Foos.

How could I promote a foo to a bar?

Demoting a bar to a foo would be simple, by just deleting the row from Bars.

Flavius
  • 13,566
  • 13
  • 80
  • 126

1 Answers1

3

The doc on Inheritance tells you:

Inheritance does not automatically propagate data from INSERT or COPY commands to other tables in the inheritance hierarchy.

This means, that you have to delete the row from the parent table and insert a new one into the child table.

You say:

Demoting a bar to a foo would be simple, by just deleting the row from Bars.

This makes me think, you misunderstand how inheritance works in PostgreSQL. Basically you have two tables, the Bar table has the same columns as Foo plus the any additional columns. Inserting a Bar writes data only into Bar, no "common data" will be written to Foo. When you delete the row from Bar then your data will not be "demoted", it will be gone completely. So both promoting and demoting require you do shuffle data between tables.

You can read the long story in the fine manual I've linked :-)

A.H.
  • 63,967
  • 15
  • 92
  • 126
  • Are there any plans to support "promoting" and "demoting"? Or do I have to do them in a transaction? – Flavius Feb 22 '12 at 15:25
  • 1
    @flavius: I'm not very deep in knowing the development plans of PostgreSQL. But other, more urgent problems with the inheritance stuff has not solved since a long time and I'm also not aware on any work on this. The most urgent stuff IMHO is: You cannot use UNIQUE constraints covering a common column in `Foo` and `Bar`. You could end up with a row in `Foo` and another one in `Bar` with the same "unique" value. As long as stuff like that is not solved I don't waste time on the PostgreSQL inheritance stuff - besides answering a question now and then ;-) – A.H. Feb 22 '12 at 15:33
  • Oh, then I was really mislead by the name "inheritance". It's just "using a table as a template when creating a new table". Are you aware of any relational db vendor offering "true inheritance" for tables? – Flavius Feb 22 '12 at 16:35
  • @Flavius: (adding to "it's just..."): "Plus some implicit UNIONS when doing INSERT, UPDATE, DELETE, SELECT." The implementation of PostgreSQL is _one_ possibility to implement this- with more work it would be even watertight. To the vendor part: Im don't know any vendor. That's because these stuff is mostly done in the application server by some framework like the Hibernate OR mapper. They do this and more with plain SQL and without any vendor specific stuff. That's much better in the long run IMHO. – A.H. Feb 22 '12 at 17:09