How to Fix Hibernate PSQLException: Bad Value for Type Int 'admin' – Boolean Column Mapping Guide

Table of Contents#

1. Understanding the PSQLException: "Bad Value for Type Int 'admin'"
2. Common Causes of the Error
   • 2.1 Mismatched Java Field Type and Database Column Type
   • 2.2 Incorrect Hibernate Mapping Annotations
   • 2.3 Accidental String Literal in Data Insertion/Update
   • 2.4 Legacy Database Using Integer Columns for Booleans
3. Step-by-Step Guide to Fix the Error
   • 3.1 Step 1: Verify the Database Column Type
   • 3.2 Step 2: Correct the Java Entity Field Type
   • 3.3 Step 3: Validate Hibernate Mapping Annotations
   • 3.4 Step 4: Ensure Data Consistency (Avoid Non-Boolean Values)
   • 3.5 Step 5: Test the Fix
4. Best Practices to Prevent Boolean Mapping Issues
5. References

Understanding the PSQLException: "Bad Value for Type Int 'admin'"#

The error PSQLException: Bad value for type int 'admin' occurs when PostgreSQL expects an integer value for a column but receives a non-integer literal (e.g., the string 'admin'). This is most often triggered by incorrect boolean column mapping between Hibernate and PostgreSQL.

Why "admin"?#

The string 'admin' in the error message is not random—it’s the actual value Hibernate tried to insert into an integer column. For example, if your application accidentally sets a boolean-like field to the string 'admin' (instead of true/false or 1/0), Hibernate will pass this string to PostgreSQL, which rejects it for an integer column.

Common Causes of the Error#

To resolve the error, first identify its root cause. Below are the most frequent scenarios:

2.1 Mismatched Java Field Type and Database Column Type#

A common mistake is using a String type in the Java entity for a database column intended to store boolean values (e.g., smallint or boolean in PostgreSQL).

Example Scenario:
Your PostgreSQL table has a column is_admin defined as smallint (to store 0/1 for false/true). In your Hibernate entity, you mistakenly declare the field as String isAdmin instead of boolean isAdmin. If your application sets isAdmin = "admin", Hibernate will attempt to insert the string 'admin' into the smallint column, causing the error.

2.2 Incorrect Hibernate Mapping Annotations#

Even if the Java field is boolean, missing or incorrect Hibernate annotations can force Hibernate to generate invalid SQL. For example:

• Forgetting to specify columnDefinition for non-standard boolean columns (e.g., smallint instead of PostgreSQL’s native boolean).
• Using outdated Hibernate @Type annotations that mismap Java booleans to database integers.

2.3 Accidental String Literal in Data Insertion/Update#

Even with correct Java and column types, explicitly using string literals (e.g., in HQL/JPQL or raw SQL) can cause issues. For example:

// Bad: Using string 'admin' instead of boolean true/false
query.setParameter("isAdmin", "admin"); 

Hibernate will pass 'admin' as a string parameter, which PostgreSQL cannot convert to an integer.

2.4 Legacy Database Using Integer Columns for Booleans#

Legacy databases often use smallint or int instead of PostgreSQL’s native boolean type. If your Hibernate mapping does not account for this (e.g., missing @Type or columnDefinition), Hibernate may misinterpret the integer column, leading to type mismatches.

Step-by-Step Guide to Fix the Error#

Follow these steps to diagnose and resolve the PSQLException: Bad value for type int 'admin'.

3.1 Step 1: Verify the Database Column Type#

First, confirm the actual data type of the problematic column in PostgreSQL. Run this query to check:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'your_table_name' AND column_name = 'your_boolean_column';

Example Output:
If data_type is smallint, the column uses integers (0/1) for booleans. If it’s boolean, it uses PostgreSQL’s native boolean type (true/false).

3.2 Step 2: Correct the Java Entity Field Type#

Ensure the Java entity field matches the intended boolean logic. Replace String with boolean or Boolean (for nullable columns).

Bad Practice (Incorrect Type):

@Entity
@Table(name = "users")
public class User {
    // Wrong: Using String for a boolean column
    @Column(name = "is_admin")
    private String isAdmin; // Accidentally set to "admin"
}

Good Practice (Correct Type):

@Entity
@Table(name = "users")
public class User {
    // Correct: Use boolean/Boolean for boolean columns
    @Column(name = "is_admin")
    private boolean isAdmin; // Stores true/false
}

3.3 Step 3: Validate Hibernate Mapping Annotations#

If the database column uses a non-standard type (e.g., smallint instead of boolean), use Hibernate annotations to explicitly map the Java boolean to the database type.

Case A: PostgreSQL Native boolean Type#

For PostgreSQL’s native boolean type, no special annotations are needed—Hibernate auto-maps boolean to boolean.

@Column(name = "is_admin") // Uses PostgreSQL boolean (true/false)
private boolean isAdmin;

Case B: smallint/int Column for Booleans (Legacy)#

If the column is smallint (storing 0/1), use @Column(columnDefinition) and @Type (Hibernate 5+) to map boolean to smallint:

import org.hibernate.annotations.Type;
 
@Entity
@Table(name = "users")
public class User {
    @Column(
        name = "is_admin", 
        columnDefinition = "smallint" // Matches database type
    )
    @Type(type = "org.hibernate.type.NumericBooleanType") // Maps 0→false, 1→true
    private boolean isAdmin;
}

• NumericBooleanType: Converts Java false → 0, true → 1 (and vice versa).
• For Hibernate 6+, use @JdbcTypeCode(SqlTypes.SMALLINT) instead of @Type.

3.4 Step 4: Ensure Data Consistency (Avoid Non-Boolean Values)#

Guarantee that the isAdmin field is only set to true/false (or null for Boolean). Audit your code for accidental string assignments:

Bad Practice (Setting String Value):

// Wrong: Assigning a string to a boolean field
user.setIsAdmin("admin"); // Causes 'admin' to be inserted into smallint column

Good Practice (Setting Boolean Value):

// Correct: Assign boolean true/false
user.setIsAdmin(true); // Inserts 1 (for smallint) or true (for boolean)

3.5 Step 5: Test the Fix#

After correcting the mapping and data, test with an integration test to ensure the error is resolved. Enable Hibernate SQL logging to verify the generated SQL:

application.properties (Spring Boot example):

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Expected SQL Output:
For a smallint column, Hibernate should generate:

INSERT INTO users (is_admin, ...) VALUES (1, ...); -- 1 = true

Best Practices to Prevent Boolean Mapping Issues#

To avoid future PSQLException type errors, follow these best practices:

1. Use PostgreSQL’s Native boolean Type#

Prefer PostgreSQL’s boolean type (not smallint or int) for boolean columns. It natively supports true/false literals and avoids integer-string confusion.

2. Enforce Consistent Java Types#

Always use boolean (non-nullable) or Boolean (nullable) for boolean fields. Never use String, Integer, or other types unless explicitly required for legacy systems.

3. Avoid Raw SQL with String Literals#

In HQL/JPQL, use boolean literals (true/false) instead of strings:

// Good: Use boolean literals
query.setParameter("isAdmin", true); 

4. Validate Hibernate Auto-DDL Cautiously#

If using hibernate.hbm2ddl.auto=update, ensure Hibernate generates the correct column types. For legacy smallint columns, explicitly set columnDefinition to avoid auto-creation of boolean columns.

5. Test with Integration Tests#

Write integration tests that validate entity persistence, especially for boolean fields. Use tools like Testcontainers to mirror production database schemas.

References#

• Hibernate ORM Documentation: Boolean Mapping
• PostgreSQL Documentation: Boolean Type
• Hibernate Types Project (for advanced type mappings)
• Spring Boot Documentation: JPA Properties

By following these steps and best practices, you’ll resolve the PSQLException: Bad value for type int 'admin' and ensure robust boolean mapping between Hibernate and PostgreSQL.