Using PostgreSQL domains with Hibernate

Hello!

When using the following PostgreSQL definitions with Hibernate/PostgreSQL:

-- integer >= 0
create domain non_negative_integer as integer check (value >= 0);

I can get it to work by using a Dialect, which is inherited from PostgreSQL10Dialect and which on creation calls

this.registerColumnType(Types.INTEGER, "non_negative_integer");

However, when creating two integer based domains, e.g.:

-- integer >= 0
create domain non_negative_integer as integer check (value >= 0);

-- integer > 0
create domain positive_integer as integer check (value > 0);

and in the custom Dialect I do

        this.registerColumnType(Types.INTEGER, "non_negative_integer");
        this.registerColumnType(Types.INTEGER, "positive_integer");

I get an error, when declaring a column of type non_negative_integer:

Schema-validation: wrong column type encountered in column [number_of_items] in table [item_list]; found [non_negative_integer (Types#DISTINCT)], but expecting [positive_integer (Types#INTEGER)]

Thus, it seems, that I did not properly understand the meaning of Dialect.registerColumnType(...).

Is there a way to get multiple PostgreSQL domains on the same base type to work in a simple manner?

Thanks!
Clemens

The column type registrations are only the default SQL types for the type code. The second call overrides the first. What you want to use is, @Column(columnDefinition = "positive_integer") on the attribute that should use the type.

Hello Christian

Thank you very much for your reply, which has solved my problem.

Clemens