How are you supposed to correctly insert and select Bytea data from a postgres database using pqxx in C++? Since std::binary_string was marked as deprecated and std::basic_string<std::byte>> is the new standard not many resources have been posted on how to achieve this.

Here is what I have so far;

Creation of the data to insert:

std::string username = "username";
unsigned char hashedPassword[SHA512_DIGEST_LENGTH] = { ... }; //SHA512_DIGEST_LENGTH = 64

std::basic_string<std::byte> hashedPasswordAsBasicString((std::byte*)&hashedPassword, sizeof(hashedPassword));

std::cout << "hashedpassword during creation:" << std::endl << hashedPasswordAsBasicString.c_str() << std::endl;

//Insert new entry into the database.
pqxx::work worker(*connectionObject);
pqxx::result response = worker.exec_params(
    "INSERT INTO users (username, hashedpassword) values ($1, $2);",
        username,
    hashedPasswordAsBasicString
);

worker.commit();

The “hashedpassword” column is of the type Bytea in the postgres database. The database encoding is set to “Cp1252” but I have also tried setting it to UTF-8. I have tried setting the “bytea_output” setting to ‘escape’ instead of the default ‘hex’ using psql, but the data before the insert and after the select still do not match.

This is how I try to retrieve the data:

pqxx::nontransaction nt(*connectionObject);
pqxx::row response = nt.exec1("SELECT hashedpassword FROM users WHERE username = '" + username + "';");

nt.commit();

pqxx::row::reference p = response["hashedpassword"];

std::basic_string<std::byte> hashedPassword((std::byte*)&p, sizeof(p));

std::cout << "hashedpassword after select:" << std::endl << hashedPassword.c_str() << std::endl;

Running this gives me the following results:

hashedpassword during creation:
000002A09C304010
hashedpassword after select:
000002A09C30B910

As you can see the data is not the same before sending it to the postgres database and after retrieving it. I have tried to look around for information and have read the documentation, but I have not found any concrete answers. The only possible problem I could “potentially” see is regarding this line in the documentation where it says (https://www.postgresql.org/docs/current/datatype-binary.html): “Bytea Storage Size: 1 or 4 bytes plus the actual binary string”. Does that mean that the “1 or 4 bytes” that are included in a Bytea variable by default could be invalidating my results? Do I need to remove those when fetching the data using the Select operation? Or is it something else completely?

I do not think it is relevant, but for completeness sake, I host the postgres database in Docker Desktop and I use DbVisualizer to view the database.