User Data Types, Domains and Enums

A user data type is a type that you define once and then use for many columns, such as a PostgreSQL domain email_address built on text with a check, or an enum order_status. DbSchema keeps user data types in the model like tables, and creates them in the database before the tables that use them.

Which Databases Support Them

DbSchema offers user data types for the databases that can declare them, including PostgreSQL, Oracle and SQL Server. The model tree lists them in a folder of each schema, named the way the database names them:

For PostgreSQL, loading the schema from the database brings in domains, enum types, composite types and range types.

Create a User Data Type

Open Model → Create Table, View... → Create User Data Type, or right-click the folder in the model tree. The dialog asks for:

• Name and Comment.
• Specification — whether a column of this type takes no parameter, a length or precision (x), a decimal (x,y), or an enumeration of values.
• Definition — the statement that creates the type in the database, for example:

CREATE DOMAIN email_address AS text CHECK ( VALUE LIKE '%@%' );
CREATE TYPE order_status AS ENUM ( 'new', 'paid', 'shipped' );

Then choose the type as the data type of a column, like any built-in type.

How the Type Reaches the Database

• Connected to the database — clicking OK in the dialog creates the type in the database at once.
• Working offline — the type stays in the model. Database → Create or Upgrade Model in the Database creates it later, before the tables and columns that use it.
• Virtual — tick Virtual to keep the type in the model only; DbSchema then never creates it in the database.