CODEWITHSUNDEEP
pythonsqlpostgresql
Derek Poehls
Derek Poehls

Reputation: 3

Convert bytea with multiple octals to an integer

I'm trying to get a value out of a Postgres DB that is stored as a bytea.
The values are VLAN ID's (so integers between 1-4096).

However, it's stored in the db (as an example) as:

\000\000\001\221 (equal to 401)

I'd like to have my SQL query return the integer value if possible to my python code.

After some searching, I can use get_byte to get one of those 4 octals (by specifying the position):

select get_byte(sub.datavlan_bytes,3) AS vlan -> this gives me the value of /221 (145)

However, I can't get the entire value.

Is there a good way to get the data in a select query, or does that need to happen in my script?

Upvotes: 0

Views: 238

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

Plain SQL by casting:

select ('x' || encode(o, 'hex'))::bit(32)::int
from (values ('\000\000\001\221'::bytea)) s (o)
;
 int4 
------
  401

Upvotes: 2

aghast
aghast

Reputation: 15310

Postgres 9+ offers a hex format for bytea values. If you can set the bytea_output connection setting to hex (which is the default, so it probably already is...) you can get back a string that can be fed to Python's int(..., 16) function. That would get your 401 directly.

Edit: Postgres docs: https://www.postgresql.org/docs/9.0/static/datatype-binary.html

Upvotes: 0

Related Questions