converting hex string in ipv6 format in postgresql

Question

I have a hex string like \xfc80000000000000ea508bfff217b628 in bytea format and I want to convert it into fc80:0000:0000:0000:ea50:8bff:f217:b628 in select query, I tried:

select '0:0:0:0:0:0:0:0'::inet + encode(ip::bytea,'hex') from a;

but following error is coming

ERROR:  operator does not exist: inet + text
LINE 1: select '0:0:0:0:0:0:0:0'::inet + encode(stationipv6::bytea,'...
                                       ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

substring() works with bytea values and you can use that to extract the individual parts of the bytes to convert it to an inet:

select concat_ws(':', 
                 encode(substring(stationipv6,  1, 2), 'hex'), 
                 encode(substring(stationipv6,  3, 2), 'hex'), 
                 encode(substring(stationipv6,  5, 2), 'hex'), 
                 encode(substring(stationipv6,  7, 2), 'hex'),
                 encode(substring(stationipv6,  9, 2), 'hex'), 
                 encode(substring(stationipv6, 11, 2), 'hex'), 
                 encode(substring(stationipv6, 13, 2), 'hex'), 
                 encode(substring(stationipv6, 15, 2), 'hex')
                 )::inet
from your_table

works on bytea columns