Postgresql/JDBC fails with or without UTF8 encoding

Question

Using Java 8 and a Postgres 10 Server, and the v 42.2.16 Postgresql Driver, I have a basic JDBC query function:

public List<Map<String, Object>> Query(String sql) throws Exception {
    Connection con = null ;
    PreparedStatement pstmt;
    List<Map<String, Object>> resultSetToList = null;
    try {
        
        con = DriverManager.getConnection(ConnectionString, Properties);
        pstmt = con.prepareStatement( sql );
        pstmt.execute();
        
        ResultSet resultSet = pstmt.getResultSet();
        resultSetToList = resultSetToList(resultSet);

        pstmt.close();

    } catch(Exception e){
        throw e;
    }
        finally {
    
        if (con != null)
            con.close();
    }
    return resultSetToList;
}

I execute a query like this:

Query("SELECT * FROM bi_functions");

But it throws an Exception

org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0xa3

The database (provided by 3rd party) has encoding SQL_ASCII. 0xA3 is the 'British Pound' character.

So I change the query to:

SET LOCAL CLIENT_ENCODING TO 'SQL_ASCII'; SELECT * FROM bi_functions;

It fails with:

org.postgresql.util.PSQLException: The server's client_encoding parameter was changed to SQL_ASCII. The JDBC driver requires client_encoding to be UTF8 for correct operation.

Is there a way using plain JDBC to circumvent this error?

No, you cannot do that. The interesting question is: what is the server encoding, and what bytes are stored in the database? — Laurenz Albe
– Laurenz Albe, Commented Sep 21, 2020 at 5:50
DB (provided by 3rd party) has encoding SQL_ASCII. 0xA3 is the 'British Pound' character. Are you saying I can't read this DB via JDBC? — Black
– Black, Commented Sep 21, 2020 at 5:59

Laurenz Albe · Accepted Answer · 2020-09-21 06:12:04Z

1

Java uses a Unicode encoding internally, so you cannot use a client_encoding different from UTF8 with the JDBC driver.

You should figure out the actual encoding of the database (probably one of the ISO 8859 or a Windows encoding). Then create a database with that encoding and dump the original database and load your dumped database into it.

Otherwise you won't be able to use this database with JDBC.

answered Sep 21, 2020 at 6:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Black Over a year ago

On the 3rd party server, the DB is listed as Encoding SQL_ASCII; Collation en_GB; Character Type en_GB. Is there another way to determine the "actual encoding" ? It's a Windows server, so probably 1252 encoding. If I create a DB locally with that encoding, won't I encounter the same issue when querying it via JDBC?

Laurenz Albe Over a year ago

If all the data in the database are consistent with Windows-1252, you won't have a problem. If they are not (say, some UTF-8 data have sneaked in), you'll have to repair the data in the database.

Black Over a year ago

I'm restoring the DB on a Mac (Catalina) server -does that make a difference?

Laurenz Albe Over a year ago

No, the platform shouldn't make a difference.

Black Over a year ago

restoring the backup to a DB created with ENCODING = 'WIN1252' worked, thanks

Starship · Accepted Answer · 2025-06-09 20:23:30Z

0

Add ?allowEncodingChanges=true to the connection URL.

edited Jun 9 at 20:23

Starship

1,3164 gold badges18 silver badges34 bronze badges

answered Jun 9 at 13:52

Giorgio Cassetti

1

Collectives™ on Stack Overflow

Postgresql/JDBC fails with or without UTF8 encoding

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related