CODEWITHSUNDEEP
postgresql
user802599
user802599

Reputation: 828

Postgres SQL read binary value from string field?

I have a column in a Postgres database that is of type varchar but has a binary value stored in it. How can I return the binary value of the column in a way I can read it?

For example, at the moment I see "r" in the column, I want to see the 1's and 0's that make up the value for r.

To be a bit more clear about what I want. I think the application is storing data about ticked checkboxes in binary. So for a group of checkboxes:

it stores the value "r" and I want to see the binary or hex of the value that is stored. So for the value "r" I want to get the hex value "72" or the binary value "0111 0010"

Upvotes: 2

Views: 9693

Answers (2)

klin
klin

Reputation: 121604

Storing binary data in a text column is not a good idea. You can use the type bytea, e.g.:

drop table if exists my_table;
create table my_table(id serial primary key, switch bytea);
insert into my_table (switch) values
('\x7272'),
('\x1111'),
('\xffff');

You can easily set and get values in hex format, convert them to bit strings, get/set individual bytes/bits, e.g.:

select id, 
    switch,
    right(switch::text, -1)::bit(16) as bits, 
    right(switch::text, -1)::bit(16)::int as int,
    get_byte(switch, 0)
from my_table;

 id | switch |       bits       |  int  | get_byte 
----+--------+------------------+-------+----------
  1 | \x7272 | 0111001001110010 | 29298 |      114
  2 | \x1111 | 0001000100010001 |  4369 |       17
  3 | \xffff | 1111111111111111 | 65535 |      255
(3 rows)

You can cast a text (varchar) to bytea:

select 'r'::bytea;

 bytea 
-------
 \x72
(1 row)

Note that in some tools (e.g. PgAdmin III) you should set the parameter to get hex output:

set bytea_output to hex;

Per the documentation:

The output format depends on the configuration parameter bytea_output; the default is hex. (Note that the hex format was introduced in PostgreSQL 9.0; earlier versions and some tools don't understand it.)

Read also in the documentation:

Upvotes: 4

Laurenz Albe
Laurenz Albe

Reputation: 246493

Using varchar for this is a bad idea. For example, you cannot store zero bytes that way.

Anyway, the answer to your question should be a simple type cast:

CAST(textcol AS bytea)

Upvotes: 0

Related Questions