Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Hibernate insert fails: column is of type bit but expression is of type boolean

ErrorERROR: column "active" is of type bit but expression is of type boolean

Short answer

PostgreSQL has a real boolean type and a separate bit type, and it will not convert one to the other on insert. The value is a boolean, so the column must be boolean too. Find where the bit column came from: a hand written columnDefinition or an older schema. Then change the column type.

  • Applies to PostgreSQL 16, Hibernate ORM 6.6, Spring Boot 3.x, Java 21
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask PostgreSQL what type the column really has. If it says bit, the schema is the problem and the Java side is fine.

    SELECT column_name, data_type, character_maximum_length
    FROM information_schema.columns
    WHERE table_name = 'users' AND column_name = 'active';
  2. 2Match what you see, then jump to its fix

    1. 1data_type is bit, often with length 1, for a field that is a boolean in JavaThe table was created with a bit columnFix
    2. 2The entity field has @Column(columnDefinition = "bit") or bit(1), and the table is generated by HibernateThe entity forces a bit type with columnDefinitionFix
    3. 3ALTER COLUMN fails on a column default of b'0' or b'1'Existing rows or a default value block the type changeFix

What does the error look like?

  • ERROR: column "active" is of type bit but expression is of type boolean

Cause 1 of 3

The table was created with a bit column

1Check

A bit(n) column holds bit strings and needs an exact length. A boolean column holds true or false. They are different types, and the first_step query shows which one you have.

\d users

2Fix

Change the column to boolean. The USING clause tells PostgreSQL how to turn each old value into the new type; here a bit value of 1 becomes true.

ALTER TABLE users
  ALTER COLUMN active TYPE boolean
  USING active = B'1';

3Verify

The column now reports boolean and the insert works.

SELECT data_type FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'active';

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The entity forces a bit type with columnDefinition

1Check

columnDefinition is a SQL fragment used when the DDL is generated, written for one database and not portable. A definition copied from a MySQL project then creates a bit column in PostgreSQL.

grep -rn "columnDefinition" src/main/java

2Fix

Remove columnDefinition and let Hibernate pick the type from the Java type, or write boolean. Then fix the existing column with the ALTER TABLE above.

@Column(name = "active", nullable = false)
private boolean active;

3Verify

Drop and recreate a test schema, or alter the column, and save an entity with active set to true.

SELECT active FROM users ORDER BY id DESC LIMIT 1;

Confirmed by jakarta.ee

Did this fix it?

Cause 3 of 3

Existing rows or a default value block the type change

1Check

The USING expression is not applied to the column default, so an old bit default can stop the change.

SELECT column_default FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'active';

2Fix

Drop the default, change the type and set a new default in one statement.

ALTER TABLE users
  ALTER COLUMN active DROP DEFAULT,
  ALTER COLUMN active TYPE boolean USING active = B'1',
  ALTER COLUMN active SET DEFAULT false;

3Verify

Insert a row without giving active; it becomes false.

INSERT INTO users (name) VALUES ('test') RETURNING active;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Use boolean for true or false values in PostgreSQL and keep bit for real bit strings.
  • Manage the schema with migrations and review generated DDL when you move a project from another database.
  • Avoid columnDefinition unless you need it; it ties the entity to one database.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.