Show real Postgres data types in Visio Database Model Diagram

Question

Show real Postgres data types in Visio Database Model Diagram

manniongeo 6

I am using:

Visio 2019
Database Model Diagram template
Reverse Engineer Wizard
Official PostgreSQL psqlODBC driver (13.02.0000)

Reverse engineering an existing database generally works, except for the data types. Specifically, Visio:

Seems to force the use of some generically-named data types
Maps almost everything from the source database to the same data type ( LONGVARBINARY ) instead of its closest generic type

In more detail, when running the Reverse Engineer Wizard, the only choices I have for ODBC drivers are Microsoft SQL Server, Oracle Server, and ODBC Generic Driver:

When I view the ODBC Generic Driver option, I can see my Postgres driver in there:

I can create a DSN using that driver, then use the DSN in Visio to reverse engineer the database.

The problem is that the resulting diagram doesn't use the native Postgres data types (or even their ANSI equivalents) - and virtually every column comes over as a LONGVARBINARY:

We're not using anything fancy on the Postgres side. This document table, for example, has columns of the following type:

character varying (16)
character varying (128)
character varying (256)
text
uuid

I'm willing to do some hand-jamming to get things to look right, but there doesn't appear to be a way to force Visio to use the data types I want. I tried using the Visio "user-defined types" feature, but it still displays the data types that Visio thinks are "native" for generic ODBC drivers (plus it takes like 50 clicks per column to set the user-defined type).

My question is:

QUESTION: Is there any way to show native Postgres data types in a Visio 2019 Database Model Diagram, even if I have to force the names by hand?

Thanks.

Ceasar Chen_MSFT 4,481 Reputation points

2022-06-28T09:42:08.127+00:00

Hi，
Your question is related to Postgres data types. I'm in charge of Office General. It is recommended that you visit office
manniongeo 6 Reputation points

2022-06-29T16:34:53.437+00:00

My question is related to Visio's 1) incomplete, and 2) incorrect interpretation of Postgres data types, not about Postgres itself.

Even if Visio insists on treating every database platform other than Oracle and SQL Server as "generic", the Reverse Engineer Wizard still fails to map correctly to the corresponding generic data type. The Visio generic ODBC driver has a VARCHAR data type, for example, yet it maps the Postgres CHARACTER VARYING(16) to LONGVARBINARY instead.

Furthermore, when creating a user-defined type, Visio still displays the generic type to which it maps in the diagram. There does not seem to be a way to force Visio to display the desired type - including Visio's built-in UDTs, like uuid (which our source database uses extensively, and would be helpful to see listed properly in the diagram).

My goal here is not to use Visio to generate Postgres-compatible SQL. Rather, I need to produce a useful diagram of an existing database for documentation purposes. I can do that in Visio if I use a "dumb" template (I mean that descriptively, not pejoratively), like Crow's Foot Database Notation, that does not render automatically from the Database Properties values. I may have to resort to that solution - at considerable time expense - because the Database Model Diagram template that includes actual modeling tools is so limited.

Your answer

Ceasar Chen_MSFT 4,481 Reputation points

2022-06-28T09:42:08.127+00:00

Hi，
Your question is related to Postgres data types. I'm in charge of Office General. It is recommended that you visit office
manniongeo 6 Reputation points

2022-06-29T16:34:53.437+00:00

My question is related to Visio's 1) incomplete, and 2) incorrect interpretation of Postgres data types, not about Postgres itself.

Even if Visio insists on treating every database platform other than Oracle and SQL Server as "generic", the Reverse Engineer Wizard still fails to map correctly to the corresponding generic data type. The Visio generic ODBC driver has a VARCHAR data type, for example, yet it maps the Postgres CHARACTER VARYING(16) to LONGVARBINARY instead.

Furthermore, when creating a user-defined type, Visio still displays the generic type to which it maps in the diagram. There does not seem to be a way to force Visio to display the desired type - including Visio's built-in UDTs, like uuid (which our source database uses extensively, and would be helpful to see listed properly in the diagram).

My goal here is not to use Visio to generate Postgres-compatible SQL. Rather, I need to produce a useful diagram of an existing database for documentation purposes. I can do that in Visio if I use a "dumb" template (I mean that descriptively, not pejoratively), like Crow's Foot Database Notation, that does not render automatically from the Database Properties values. I may have to resort to that solution - at considerable time expense - because the Database Model Diagram template that includes actual modeling tools is so limited.

Share via

Show real Postgres data types in Visio Database Model Diagram

Your answer