Postgres SQL read binary value from string field?

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:

• Binary Data Types
• Binary String Functions and Operators
• Bit String Types
• Bit String Functions and Operators

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)