How can I unpack an integer into a float in PostgreSQL?

Question

I'm using Python's struct.pack function to pack various data types into a common 32-bit integer field in PostgreSQL. The drawback is that I can't operate with these values in the database, I have to perform a struct.unpack in Python on the data to know what it represents.

If I pack a 32-bit float into this field, is there any way to get PostgreSQL to do the conversion for me?

I tried this but it didn't work:

select cast(cast(value as bit(32)) as float4) ...

It will successfully cast the integer to bit(32) but it won't convert that back to a float.

Answer 1

The simplest solution, and one I'm sure you considered and abandoned, is to store 32-bit floating point values in a column defined to use a 32-bit floating point data type.

CAST() won't work for you, because a) CAST() doesn't know anything about C structs, endian-ness, or padding bytes, and b) you can't cast a bit data type to a floating point data type. (You can cast a bit data type to integer, though.)

Answer 2

An answer you are probably not looking for: Don't do that.

This is a violation of the basics: a value in a database should be atomic, not divisible into other values. All of the database's operations (did I mention all) are tuned to handling individual values. You are "fighting the framework" here.

Even if you can pull it off, it will be a drag on performance. Plus the database is not reportable, it's not just that you are having this issue now, every attempt to read this data for any reason will run into this problem.

Well, I don't want to go into a rant, 'nuf said.