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:true id:2,c1:-1,b1:false id:3,c1:2,b1:false id:4,c1:0,b1:false ---------- Oracle ----------- id:1,c1:1,b1:true id:2,c1:-1,b1:true id:3,c1:2,b1:true id: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 TABLE Time: 13.911 ms [local]:5432 pg12@testdb=# insert into tbl2 values(1,1); INSERT 0 1 Time: 5.091 ms [local]:5432 pg12@testdb=# insert into tbl2 values(2,-1); INSERT 0 1 Time: 2.653 ms [local]:5432 pg12@testdb=# insert into tbl2 values(3,2); INSERT 0 1 Time: 2.716 ms [local]:5432 pg12@testdb=# insert into tbl2 values(4,0); INSERT 0 1 Time: 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 TABLE Time: 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=#