CODEWITHSUNDEEP
sqloracle-databasepostgresqlhibernatejpa
Gajendra Kumar
Gajendra Kumar

Reputation: 928

incompatible types: numeric and bigint. In PostgreSQL

While migrating to postgresql, I'm facing below issue

Key columns veh_reg_authority and id are of incompatible types: numeric and bigint. Below are mappings:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "VEH_REG_AUTHORITY", columnDefinition ="Numeric(12,0)")
private VehicleRegistrationAuthority vehicleRegistrationAuthority;

VehicleRegistrationAuthority has id of type Long so which in converted to BIGINT as per PostgreSQL.

So one simple solution is to change columnDefinition ="Numeric(12,0)" accordingly, but we can't do that as it works well with oracle and mysql, and it's been used heavily in out code so not possible to change.

Below are types in Oracle, Mysql, and PostgreSQL:

column                          java type             Oracle     Mysql   PostgreSQL
id                               Long                 NUMBER     BIGINT  BIGINT
veh_reg_authority VehicleRegistrationAuthority        NUMBER   decimal(12,0) NUMERIC

While creating foreign key hibernate gives below issue.

Caused by: org.postgresql.util.PSQLException: ERROR: foreign key constraint "fk7gyqskn6x2l2910xhhtjgvh0j" cannot be implemented
Detail: Key columns "veh_reg_authority" and "id" are of incompatible types: numeric and bigint.

Upvotes: 1

Views: 5403

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246483

You should use the same data type for both columns if you want to define a foreign key constraint.

If your application works fine in Oracle using the data type NUMBER for both columns, why should it not work in PostgreSQL if you use numeric for both? These data types are comparable.

Upvotes: 1

Related Questions