sql - 在 bytea 列上使用 ANY 和数组时在 Postgres 中选择记录

Question

我在使用 Postgres 数据库中的 '=ANY(?)' 条件查询 bytea 列时遇到问题。我定义了一个带有 bytea 列的表，插入一个 byte[] 工作正常，但查询不返回任何结果。

看到这个片段：

    private static void selectBytes(String url, String username, String password) throws Exception
{
    Class.forName("org.postgresql.Driver");

    Connection conn = DriverManager.getConnection(url, username, password);

    Statement statement = conn.createStatement();
    statement.execute("create table bytetest(x bytea)");
    statement.close();

    PreparedStatement ps = conn.prepareStatement("insert into bytetest(x) values(?)");
    ps.setBytes(1, new byte[] { 1, 2, 3, 4 });
    ps.execute();
    ps.close();

    statement = conn.createStatement();
    ResultSet rs = statement.executeQuery("select count(*) from bytetest");
    rs.next();
    System.out.println("Number of records: " + rs.getInt(1));
    rs.close();
    statement.close();

    ps = conn.prepareStatement("select count(*) from bytetest where x = ANY(?)");
    Array array = conn.createArrayOf("bytea", new Object[] { new byte[] { 1, 2, 3, 4 } });
    ps.setArray(1, array);
    rs = ps.executeQuery();
    rs.next();
    System.out.println("Number of selected records: " + rs.getInt(1));
    rs.close();
    ps.close();

    ps = conn.prepareStatement("select count(*) from bytetest where x = ?");
    ps.setBytes(1, new byte[] { 1, 2, 3, 4 });
    rs = ps.executeQuery();
    rs.next();
    System.out.println("Number of selected records directly: " + rs.getInt(1));
    rs.close();
    ps.close();

    conn.close();
}

输出是：

Number of records: 1
Number of selected records: 0
Number of selected records directly: 1

日志的输出显示数组的解释方式与单个值不同。

LOG:  execute <unnamed>: select count(*) from bytetest where x = ANY($1)
DETAIL:  parameters: $1 = '{{"\\x31","\\x32","\\x33","\\x34"}}'
LOG:  execute <unnamed>: select count(*) from bytetest where x = $1
DETAIL:  parameters: $1 = '\x01020304'

这是 PostgreSQL 9.6.3 和 postgresql-42.2.10 驱动程序。如何在 PostgreSQL 中使用 ANY 和二进制数据选择我的数据？

标签： sqlarrayspostgresqljdbc