CODEWITHSUNDEEP
postgresqljdbcbit
ttqs
ttqs

Reputation: 51

how to insert value for bit column of postgresql with jdbc

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

Upvotes: 4

Views: 9675

Answers (3)

Anna Grigorenko
Anna Grigorenko

Reputation: 1

Any object is extracted from the PostgreSQL in the PGobject format. Next, the JDBC driver determines the data type, if possible, and turns the PGobject into a java data type. Thus, if you create an PGobject with the required database data type, the insertion request will be executed without problems. Creation PGobject:

PGobject pGobject = new PGobject();
pGobject.setValue(value);
pGobject.setType(type);

where:

  • value - the value to insert (should be a string - you can use toString())
  • type - required data type ("bit" in your case)

This pgObject you can set in preparedStatement, using setObject() method.

Upvotes: 0

ubw
ubw

Reputation: 120

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?

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247605

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);

Upvotes: 3

Related Questions