QSqlQuery inserting QByteArray as string into PostgreSQL

I have a PostgreSQL 9.4.5 table with a column with basic character data type, i.e. created like so:

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL);

I then insert data using QSqlQuery, using a bound QByteArray:

QSqlQuery query;
query.prepare("INSERT INTO films VALUES (1, ?)");
const QByteArray film("Avatar");
query.addBindValue(film);
query.exec();

On Ubuntu 15.10, the film name goes into the table as bytes:

\x417661746172

On Windows, it goes in as characters.

Without explicitly casting the QByteArray to QString, is there any way to tell QSqlQuery or PostgreSQL to treat the data as a string, so it works on Ubuntu as it does on Windows?

Solution

A QByteArray has no information about the encoding of the string it contains (or even that the sequence of bytes it contains can be interpreted as an encoded string).

If it happens to contain an UTF-8 encoded string, you might either

Bind it as a string with QString::fromUtf8(film.constData()) instead of just film
let the Qt driver pass it as bytea, but have PostgreSQL convert it to text through the INSERT query:
```
query.prepare("INSERT INTO films VALUES (1, convert_from(?,'UTF-8')))");
```

This should also work for other encodings, UTF-8 above is an example.

About the difference between Windows/Ubuntu: it's not clear why QtSql would behave differently, but maybe it's a difference in postgres configuration.

'\x417661746172' is the text representation of Avatar in UTF-8 as a binary string but only when bytea_output is set to hex. If bytea_output is set to escape, that would be exactly Avatar and indiscernible from the text itself.

Example in the psql command-line client:

test=> set bytea_output=hex;
SET
test=> select 'Avatar'::bytea;
     bytea      
----------------
 \x417661746172
(1 row)

test=> set bytea_output=escape;
SET
test=> select 'Avatar'::bytea;
 bytea  
--------
 Avatar
(1 row)

That escaping of bytea also happens on the client side within drivers such as QPSQL, and the hex flavor in only available since PostgreSQL 9.0. Before that, escape was the only method and the bytea_output parameter didn't exist. I believe that simply having QtSql linked with a libpq older than 9.0 on the Windows machine might explain why you get the 'text-like' appareance versus the 'hex-like' appearance on a recent Ubuntu.