Toad World® Forums

Dictionary Types and serial / bigserial pseudo-types in postgresql


#1

Postgres 8.1

Problem generating DDL for Dictionary Types serial and bigserial

I have a model that I moved over from a DM2 model. In the DM2 model I had user defined types. E.g. I had an ‘identifier’ type that I mapped to the ‘BigSerial’ pseudo-type. If I am correct, it looks like Dictionary Types in DM3 corresponds to user defined types in DM2. So far so good. Correct me if I am wrong (the way these are used, calling them dictionary types makes some sense to me btw, rather than user defined types).

In the DM2 model when I generated the DDL, instead of using the user defined types as domains (which is what DM3 is doing… and it is causing some problems), it would substitute in the base data types of the ‘user data type’. A couple of examples to illustrate… first with what DM3 is doing and then what DM2 does and why DM2 behaviour is better.

example:
I define two user defined types:
identifier bigserial
foreign_key bigint

case 1) generated DDL if it treated them as a domain (DM3)

create table foo (
my_pk identifier,
a_foreign_key foreign_key
);

DM3 is creating domains out of ‘dictionary types’. However, the ‘identifier’ dictionary type can’t be used as a domain since it is a ‘serial’ pseudo-datatype. It generates this error when you try to run the DDL script:

ERROR: type “bigserial” does not exist
SQL state: 42704

see this link for more info: http://archives.postgresql.org/pgsql-sql/2006-12/msg00192.php I found this problem in another data modeller and was happy that DM2 didn’t replicate it.

case 2) how DM2 would generate the DDL

create table foo (
my_pk bigserial,
a_foreign_key bigint
);

this is how I think the the DDL generation for dictionary types should be implemented, as it also solves the issue around the serial pseudo-data-types.

I think the dictionary types should probably be used as ‘conveniences’ and the option maybe on each type to have it be generated as either a domain or as the underlying data type during DDL generation. e.g. I can select to have my ‘identifier’ type generated in the DDL code as a ‘bigserial’.

If someone really wanted them as domains… they should have created them as domains.


#2

Hello Bill,

Thank you for this question.

Here’s a brief explanation:

In TDM 2, there is only the User Defined Types section. In TDM 3, this section is divided to:

  • User Data Types,
  • Dictionary Types,
  • Domains.

Let me explain the differences among them now:

1. DomainsThey have only a logical meaning.
During DDL script generation, they are not generated. If a domain is used in attribute, only values of the domain are transferred to attribute during the DDL script generation process.
2. User Data TypesThey can be generated in final DDL script. They are data types defined by users. User data types are not derived from data types.

3. Dictionary TypesThey can be generated in final DDL script. They are data types that are derived from other data types.

In PostgreSQL, the data types that are generated via SQL command CREATE DOMAIN are stored among Dictionary Types.

How import from TDM 2 works now: It behaves as if TDM2 checkbox “User-defined types as domains” is always selected in the Script Generating dialog | How to Generate tab, which is not a correct behaviour.
(Probably, you don’t have it selected, but the import considers it as if it is selected.)

Nevertheless, in next TDM 3 beta version, it will be taken into account and therefore your problem will be fixed - “identifier” and “foreign_key” will be loaded among Domains.

Note: We know there’s much confusion about terminology. Domain in PostgreSQL corresponds to Dictionary Type in TDM 3. Domain in TDM 3 is just a logical information as mentioned above.

I believe this information will help you to understand a bit more. If you have any questions, please write us back. Thank you.

Regards,

Vladka + Mario