Inserting Text String with Hex into Postgresql as a Bytea

Inserting text string with hex into PostgreSQL as a bytea

You can convert a hex string to bytea using the decode function (where "encoding" means encoding a binary value to some textual value). For example:

select decode('DEADBEEF', 'hex');
decode
------------------
\336\255\276\357

which is more understandable with 9.0's default output:

   decode   
------------
\xdeadbeef

The reason you can't just say E'\xDE\xAD\xBE\xEF' is that this is intended to make a text value, not a bytea, so Postgresql will try to convert it from the client encoding to the database encoding. You could write the bytea escape format like that, but you need to double the backslashes: E'\\336\\255\\276\\357'::bytea. I think you can see why the bytea format is being changed.... IMHO the decode() function is a reasonable way of writing inputs, even though there is some overhead involved.

Is there a good reason to insert HEX as BYTEA in PostgreSQL?

There are three good reasons:

  1. It will require less storage space, since two hexadecimal digits are stored as one byte.

  2. It will automatically check the value for correctness:

    SELECT decode('0102ABCDNONSENSE', 'hex');
    ERROR: invalid hexadecimal digit: "N"
  3. you can store and retrieve binary data without converting them from and to text if your API supports it.

Save a hex-string to PostgreSQL column character varying

If you are sure that there are never more than 10 elements, you can simply cast your hex string to text:

INSERT INTO my_table (hex_text) VALUES (<some hex data>::text);

Or use a bytea column instead?

How can I convert an escaped string to a bytea in postgresql (\ is not a binary digit) with Azure Data Studio

This turns out to be Azure Data studio's behaviour, and not specific to postgresql.

When I run the same query using psql I get a sensibly formatted hex string which works fine in roundtrip queries, that looks like \xbc6b702a12f8adb482ad517eca5af6407c9f172091242aa79845a17b4d479e2d (note different data value but the format is correct)

I filed an issue against the postgresql addin for Azure Data Studio and hopefully it can be resolved that way.

Issue Link: https://github.com/microsoft/azuredatastudio-postgresql/issues/204

As a workaround I will need to use select encode(column, 'hex') in all my queries

How to insert binary data into a PostgreSQL BYTEA column using the C++ libpqxx API?

Figured it out. Here is an example showing how to insert a bunch of binary objects into a table:

pqxx::connection conn( ... );
conn.prepare( "test", "INSERT INTO mytable( name, binfile ) VALUES ($1, $2)" );
pqxx::work work( conn );
for ( ... )
{
std::string name = "foo";
void * bin_data = ...; // obviously do what you need to get the binary data...
size_t bin_size = 123; // ...and the size of the binary data

pqxx::binarystring blob( bin_data, bin_size );
pqxx::result r = work.prepared( "test" )( name )( blob ).exec();
}
work.commit();

Here is how to get the binary data back out of the database:

pqxx::result result = work.exec( "SELECT * FROM mytable" );
for ( const auto &row : result )
{
pqxx::binarystring blob( row["binfile"] );
void * ptr = blob.data();
size_t len = blob.size();
...
}

HDBC-postgreSQL bytea fields get returned as SqlByteString hex strings

This is a longtime known issue with this library. See this bug for example.

The broader problem is that getting raw bytes requires a fair amount of cleverness that the postgres api doesn't make obvious. You have to call your entire query with binary rather than text output (which is arguably better anyway, but would require a rewrite of that portion of the binding layer).

You can see where pqexecparams is called and note it is called with a last parameter of 0, which by the postgres docs means everything comes back in text. And for postgres that means this funny hex representation you see.

If that argument were swapped to 1, then things could be done more efficiently (including getting raw binary for bytea fields) but Statement.hsc would have to be pervasively rewritten to deserialize those binary values.

This is one of those things where its slightly irritating to lots of people, but nobody has yet been sufficiently motivated to go rewrite and debug the whole thing. But, of course, somebody really should! :-)

How can I print bytea data as a hexadecimal string in PostgreSQL / pgAdmin III?

Based on this answer, I found my solution to be

SELECT encode(my_column::bytea, 'hex') FROM my_table;


Related Topics



Leave a reply



Submit