Oracle vs PostgreSQL Develop（18） - Boolean

在Oracle和PG中都可以使用int类型模拟布尔类型，但通过JDBC接口（JDBC驱动，Oracle为11.2.0.4，PG为9.3）获取出来的值却不一致，这一点需要注意。
测试脚本

drop table tbl1;create table tbl1(id int,c1 int);insert into tbl1 values(1,1);insert into tbl1 values(2,-1);insert into tbl1 values(3,2);insert into tbl1 values(4,0);

Java代码

/* * */package testPG;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestBoolean {  public static void main(String[] args) {    System.out.println("---------- PG -----------");    try (Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.26.28:5432/testdb", "pg12",        "pg12")) {      TestBool(conn);    } catch (SQLException se) {      System.out.println(se.getMessage());    } catch (Exception e) {      e.printStackTrace();    } finally {    } // end try    System.out.println("---------- Oracle -----------");    try (Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.18:1521:orcl", "test",        "test")) {      TestBool(conn);    } catch (SQLException se) {      System.out.println(se.getMessage());    } catch (Exception e) {      e.printStackTrace();    } finally {    } // end try  }  public static void TestBool(Connection conn) {    try (PreparedStatement pstmt = conn.prepareStatement("SELECT id,c1 from tbl1");        ResultSet rs = pstmt.executeQuery();) {      conn.setAutoCommit(true);      while (rs.next()) {        int id = rs.getInt("id");        int c1 = rs.getInt("c1");        boolean b1 = rs.getBoolean("c1");        System.out.println("id:" + id + ",c1:" + c1 + ",b1:" + b1);      }    } catch (SQLException se) {      System.out.println(se.getMessage());    } catch (Exception e) {      e.printStackTrace();    } finally {    } // end try  } // end} // end Class

执行结果

---------- PG -----------id:1,c1:1,b1:trueid:2,c1:-1,b1:falseid:3,c1:2,b1:falseid:4,c1:0,b1:false---------- Oracle -----------id:1,c1:1,b1:trueid:2,c1:-1,b1:trueid:3,c1:2,b1:trueid:4,c1:0,b1:false

使用JDBC，在PG中，只有1视为T，其他为F；而在Oracle中，只有0视为F，其他视为T。
但在PG中，如把int转换为boolean，则行为与Oracle一致。

[local]:5432 pg12@testdb=# create table tbl2(id int,c1 int);CREATE TABLETime: 13.911 ms[local]:5432 pg12@testdb=# insert into tbl2 values(1,1);INSERT 0 1Time: 5.091 ms[local]:5432 pg12@testdb=# insert into tbl2 values(2,-1);INSERT 0 1Time: 2.653 ms[local]:5432 pg12@testdb=# insert into tbl2 values(3,2);INSERT 0 1Time: 2.716 ms[local]:5432 pg12@testdb=# insert into tbl2 values(4,0);INSERT 0 1Time: 2.625 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# select id,c1 from tbl2; id | c1 ----+----  1 |  1  2 | -1  3 |  2  4 |  0(4 rows)Time: 3.183 ms[local]:5432 pg12@testdb=# alter table tbl2 alter column c1 type boolean using c1::boolean;ALTER TABLETime: 30.581 ms[local]:5432 pg12@testdb=# select id,c1 from tbl2; id | c1 ----+----  1 | t  2 | t  3 | t  4 | f(4 rows)Time: 2.566 ms[local]:5432 pg12@testdb=#