Strings with null characters produce exceptions when selected or inserted. Attempts to select messages with null bytes produces "ERROR: insufficient data left in message". And inserting produces "ERROR: invalid byte sequence for encoding \"UTF8\": 0x00". Since a null character is a valid UTF code point why is it rejected by the JDBC driver? The attached test can work with Mysql and their JDBC driver.

Strings with null characters produce exceptions when selected or inserted. Attempts to select messages with null bytes produces "ERROR: insufficient data left in message". And inserting produces "ERROR: invalid byte sequence for encoding \"UTF8\": 0x00".

Since a null character is a valid UTF code point why is it rejected by the JDBC driver?

It can work with Mysql and their JDBC driver.

import java.sql.*;

public class TestStringsWithNullBytes {
public static void main(String[] args) {
String noNulls = "No null bytes";
String nulls = "Null bytes \000\000";
try {
Class.forName("org.postgresql.Driver");
Connection db = DriverManager.getConnection(
"jdbc:postgresql://localhost/test?user=test&password=secret");
/*
Class.forName("com.mysql.jdbc.Driver");
Connection db = DriverManager.getConnection(
"jdbc:mysql://localhost/test?user=test&password=secret");
*/
db.setAutoCommit(true);
tryQuery(db, "SELECT * FROM test");
tryQuery(db, "DROP TABLE IF EXISTS test");
tryQuery(db, "CREATE TABLE test (name TEXT)");
tryQuery(db, "SELECT '" + noNulls + "'");
tryQuery(db, "SELECT '" + nulls + "'");
tryQuery(db, "INSERT INTO test (name) VALUES('" + noNulls + "')");
tryQuery(db, "INSERT INTO test (name) VALUES('" + nulls + "')");
tryPreparedQuery(db, "SELECT ?", noNulls);
tryPreparedQuery(db, "SELECT ?", nulls);
tryPreparedQuery(db, "INSERT INTO test (name) VALUES(?)", noNulls);
tryPreparedQuery(db, "INSERT INTO test (name) VALUES(?)", nulls);
tryQuery(db, "SELECT * FROM test");
}
catch(Exception e) {
e.printStackTrace();
}
}

private static void tryQuery(Connection db, String query) {
try {
Statement s = db.createStatement();
if (s.execute(query)) {
ResultSet rs = s.getResultSet();
while(rs.next()) {
System.out.println(escape(query) + ": " + escape(rs.getString(1)));
}
}
}
catch(Exception e) {
System.err.println(escape(query) + ": " + e.getMessage());
}
}

private static void tryPreparedQuery(Connection db, String query, String s) {
try {
PreparedStatement ps = db.prepareStatement(query);
ps.setString(1, s);
if (ps.execute()) {
ResultSet rs = ps.getResultSet();
while(rs.next()) {
System.out.println(escape(query) + ": " + escape(rs.getString(1)));
}
}
}
catch(Exception e) {
System.err.println(escape(query) + ": " + e.getMessage());
}
}

private static StringBuilder escape(String s) {
StringBuilder sb = new StringBuilder();
for(int i = 0; i < s.length(); i++) {
if ((32 > (int)s.charAt(i)) || (126 < (int)s.charAt(i))) {
sb.append("<" + (int)s.charAt(i) + ">");
}
else {
sb.append(s.charAt(i));
}
}
return(sb);
}
}