I'm trying to update the value of a enum which is present as a column in table in database using a rest api call from Java spring boot, when I try to do so I'm getting the below error.

colum "status_code" is of type public.status_code but expression is of type character varying

I have a table in db with columns like id(uuid), name(varchar), status(status_code) status_code is a enum created with below sql

Create type status_code as Enum ('SC_1', 'SC_2', 'SC_3', 'SC_4');

My java enum look like below

package monarch;

public enum status_code { SC_1, SC_2, SC_3, SC_4;}

Which is consumed in my entity which looks like below

@Entity
@Table(name = "PLAYER_DETAILS")
@Data
@AllArgsConstructor
public class Player {
@column(name="ID", nullable=false)
private UUID id;

@column(name="NAME", nullable=false)
private String name;

@column(name="STATUS_CODE", nullable=false, columnDefinition = "status_code")
private status_code statusCode;
}

In my service class I have the below function to update the status

public Player updateStatus(UUID id, status_code code) {
Player player = repo.findById(id);
player.setStatusCode(code); //code value is correct says SC_1
return repo.save(id); //throws error saying colum "status_code" is of type public.status_code but expression is of type character varying
}

repo class

public interface PlayerRepository extends CrudRepository<Player, UUId> {
}

Any help to point out what is going wrong here? I'm totally new to Java so please don't down vote and let me know in comments if I should add any additional info. Thanks.

I'm trying to update the value of a enum which is present as a column in table in database using a rest api call from Java spring boot, when I try to do so I'm getting the below error.

colum "status_code" is of type public.status_code but expression is of type character varying

I have a table in db with columns like id(uuid), name(varchar), status(status_code) status_code is a enum created with below sql

Create type status_code as Enum ('SC_1', 'SC_2', 'SC_3', 'SC_4');

My java enum look like below

package monarch;

public enum status_code { SC_1, SC_2, SC_3, SC_4;}

Which is consumed in my entity which looks like below

@Entity
@Table(name = "PLAYER_DETAILS")
@Data
@AllArgsConstructor
public class Player {
@column(name="ID", nullable=false)
private UUID id;

@column(name="NAME", nullable=false)
private String name;

@column(name="STATUS_CODE", nullable=false, columnDefinition = "status_code")
private status_code statusCode;
}

In my service class I have the below function to update the status

public Player updateStatus(UUID id, status_code code) {
Player player = repo.findById(id);
player.setStatusCode(code); //code value is correct says SC_1
return repo.save(id); //throws error saying colum "status_code" is of type public.status_code but expression is of type character varying
}

repo class

public interface PlayerRepository extends CrudRepository<Player, UUId> {
}

Any help to point out what is going wrong here? I'm totally new to Java so please don't down vote and let me know in comments if I should add any additional info. Thanks.

• java
• database
• postgresql
• spring-boot
• enums

3 Answers 3

You need to annotate the enum field with @Enumerated. If you use PostgreSQL, you can use the @JdbcType annotation directly on our enum field to specify a custom JDBC type handler.

@Entity
@Table(name = "PLAYER_DETAILS")
@Data
@AllArgsConstructor
public class Player {
@column(name="ID", nullable=false)
private UUID id;

@column(name="NAME", nullable=false)
private String name;

@Enumerated(EnumType.STRING)
@JdbcType(type = PostgreSQLEnumJdbcType.class)
private status_code statusCode;
}

But if it doesn't work with the @JdbcType handler, you need to write the insert with a native query. For example:

String sql = "UPDATE PLAYER_DETAILS SET status_code = (CAST(:statusCode AS status_code)) WHERE id = :id";
    Query query = session.createNativeQuery(sql);
    query.setParameter("statusCode", status_code.SC_1);
    query.setParameter("id", id);

The default mapping for Enums is using an Integer. If you want to save them as a String, you need to add @Enumerated(EnumType.STRING) Annotation above your Enum field.

Try using a custom handler to tell JPA how to convert to and from database column.

package monarch;

import .hibernate.engine.spi.SharedSessionContractImplementor;
import .hibernate.type.EnumType;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class PostgreSQLEnumType extends EnumType<status_code> {
    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws SQLException {
        if (value == null) {
            st.setNull(index, Types.OTHER);
        } else {
            st.setObject(index, value.toString(), Types.OTHER);
        }
    }

    @Override
    public status_code nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws SQLException {
        String name = rs.getString(names[0]);
        return name != null ? status_code.valueOf(name) : null;
    }
} 

and annotate your column with your custom EnumType definition.

    @Column(name = "STATUS_CODE", nullable = false, columnDefinition = "status_code")
    @Type(type = "monarch.PostgreSQLEnumType")
    private status_code statusCode;