Oracle vs PostgreSQL Develop (18)-Boolean

You can use the int type to simulate Boolean types in both Oracle and PG, but the values obtained through the JDBC interface (JDBC driver, Oracle 11.2.0.4 and PG 9.3) are inconsistent, which should be noted.

Test script

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

Java code

/ * * / 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

Execution result

-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

With JDBC, in PG, only 1 is considered T and the other is F, while in Oracle, only 0 is considered F and the others are considered T.

However, in PG, if you convert int to boolean, the behavior is the same as 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 (1jue 1); INSERT 0 1Time: 5.091 ms [local]: 5432 pg12@testdb=# insert into tbl2 values (2meme Lexi 1); INSERT 0 1Time: 2.653 ms [local]: 5432 pg12@testdb=# insert into tbl2 values (3jue 2); INSERT 0 1Time: 2.716 ms [local]: 5432 pg12@testdb=# insert into tbl2 values (4jue 0) INSERT 0 1Time: 2.625 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# select id,c1 from tbl2; id | C1-+-1 | 12 |-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 purse Booleanscape 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=#