how to insert value for bit column of postgresql with jdbc

user_6102418 0
Viewed 3706

If the preparedStatement sql is fixed, how can I do the insert?

table schema

create table if not exists ttqs_a (b bit(1));

code

try(PreparedStatement ps = conn.prepareStatement("insert into ttqs_a values(?)")){
  ps.setObject(1,1, Types.BIT);
  ps.execute();
}

exception

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "b" is of type bit but expression is of type boolean
bit jdbc postgresql
2 Answers

I don't think you can tell the JDBC driver to use the data type bit on the database side, you you will have to add a type cast:

INSERT INTO ttqs_a VALUES (CAST(? AS bit))

Then use any of the types that can be cast to bit, such as text

stmt.setString(1, "0");

or integer

stmt.setInt(1, 0);
user_64643080

With MySQL you can use PreparedStatement#setByte to insert into BIT(M) fields, but I'm not sure whether this is also true for PostgreSQL.

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO ttqs_a VALUES (?);")) {
    ps.setByte(1, (byte) 1);
    ps.executeUpdate();
}

But if it's just BIT(1), why not use BOOLEAN?

user_184646790
Related