Toad World® Forums

Postgress and inherits for table


#1

Postgress supports creating table with inheritance

CREATE TABLE cities (
name text,
population real,
altitude int – (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

(excerpt from postgressql documentation)

Will TDM3 support this sometimes in the future ?

I am definitely sure I will “convert” myself to the PostgreSQL world and this inheritance is very tempting to use.

I tried to create 2 tables in logical designer with inheritance. Changed the inheritance type to “Physical as logical”, but when createing tables in physical model, tables are created but no inheritance in PostgreSQL DDL script.

I see that there are “with OIDS” checkboxes in tables for PostgreSQL physical tables. Maybe similar check could be added for this inheritance when there is foreign key added from one table to another.


#2

Hello,

In Physical model, please see the Entity Properties form, General tab, Inherited Tables box where the list of inherited tables should be written. (In your case, “cities”.)

Concerning the LER to PER conversion, do we understand well that you like to convert the inheritance the way that you can find appropriate entity in the Inherited Tables box in your PER model then?
(Unfortunately, now it is not possible.)

Thanks for the details.

Regards,

Vladka + Mario


#3

Yes, I need to propagate the inheritance from logical model to physical.

Because this way, the foreign key is in child tabled duplicated.
When using inheritance, none of parent’s fields should be used in child tables. That applies also to primary keys and foreign keys from primary keys.
I can remove the foreign key manually, but will lost graphical inheritance.

After DDL script generating CREATE TABLES run (even with the duplicate id)
but foreign key contstraints generate error

ALTER TABLE “dieta1” ADD CONSTRAINT “Unique Identifier1” PRIMARY KEY (“idcko”);
ALTER TABLE “dieta2” ADD CONSTRAINT “Unique Identifier1” PRIMARY KEY (“idcko”);

both have identical names. I used verify on the fysical model, but number of errors, warnings and hints is ZERO.

I am using postgresql 8.1


#4

Hello,

Thanks for the details.

We will deal with this issue. CR # 36 882.

Verification problem: Thanks! We will fix this bug. CR # 36 883.

Thanks for your co-operation!

Regards,

Vladka


#5

Inheritance continues:

Maybe I already said that, but I migrated my project to PostgreSQL.
Everything went OK, except for ENUM dictionary types. PostgreSQL does not support them. So definitions stayed in the definition input withouth proper meaning.
That’s not bad… we do not want to loose the data. Maybe some warning could help to address this issue after model transform. Or even some data conversion rule or even wizard could help to transfer it to “constants” like table, or normal char field plus regular expression.

I have begun to convert enums to “constants” like table that has many children, one for each enum data type.

I want to convert it to logical model, but the inheritance is then lost in logical model.
It is converted to normal PK-PFK relation. I think inherited tables should be converted to inheritance in logical view.

Maybe you know about it, because conversion to logical model was planned for version 3.1 , though it is functional for almost all situations already in this version.

Another thing: Logical model, Attribute properties, “Valid values” . Why is this tab not visible for charater data type ? For variable character data it is visible, but not all the time.


#6

Hello arki,

Thanks very much for your suggestions.

Firstly, I’d like to note that we do keep improving the conversion. We know it is not perfect and there’s much much to do and improve. Still it’s in development. We thank you for your suggestions and will definitely deal with them for any future versions.

  1. Enum conversion - CR # 36 940.
  2. PostgreSQL inheritance conversion - CR # 36 941.

Another thing: Logical model, Attribute properties, “Valid values” . Why is this tab not visible for charater data type ? For variable character data it is visible, but not all the time.

The tab Valid Values appears only for the data types that support valid values. Otherwise it is hidden.

If you have more questions, please write us back. Thanks.

Regards,

Vladka + TDM Team


#7

And where do I see if the data type supports “valid values” ?

And why it is not available for “character data type”. I don’t see any difference between character data type and variable character data type except for the fixed size ? I still can have valid enumerated values “AUX”, “ERA”, “RTO”, etc.

When converting these “valid values” into physical model, how is it converted in this version ? I did not try every databaze type, I do not know if some database type supports such a thing except for mysql (ENUM).


#8

Hello,

Sorry for so late reply.

And where do I see if the data type supports “valid values” ?

See the Help file, Modeling | Logical Data Model | Attributes.
Valid values can be defined for the following data types:
Bigint
Float
Integer
VarChar

To check it out, select one data type after another from the Data Type box. For those that support valid values, the Valid Values tab will appear.

And why it is not available for “character data type”.
Thanks for your suggestion. We will deal with it. CR # 37 052.

When converting these “valid values” into physical model, how is it converted in this version ?
During the conversion, attribute check constraint is created automatically in the physical model. (It doesn’t matter what database type it is.)

Regards,

Vladka