PostgreSQL中bytea格式十六进制字符串转IPv6格式技术问询

First, let's break down why your original query failed: PostgreSQL doesn't have an inet + text operator, so you can't add an inet value to the hex string returned by encode(). That's exactly what the error message is pointing out.

To get the expanded IPv6 format you want (with all 8 groups of 4 hex digits), here's a simple, direct approach using string manipulation:

SELECT rtrim(regex_replace(encode(ip::bytea, 'hex'), '(.{4})', '\1:', 'g'), ':') AS ipv6_expanded
FROM a;

How this works step-by-step: #

• encode(ip::bytea, 'hex') converts your bytea value to a raw 32-character hex string (e.g., fc80000000000000ea508bfff217b628).
• regex_replace(..., '(.{4})', '\1:', 'g') inserts a colon after every 4 characters, resulting in fc80:0000:0000:0000:ea50:8bff:f217:b628: (note the trailing colon).
• rtrim(..., ':') removes that final trailing colon to get the properly formatted IPv6 address you need.

If you also want the result to be a valid inet type (instead of just a string), you can cast the final output:

SELECT rtrim(regex_replace(encode(ip::bytea, 'hex'), '(.{4})', '\1:', 'g'), ':')::inet AS ipv6_inet
FROM a;

Alternative: Cast bytea directly to inet (compressed format) #

If you don't mind the compressed IPv6 format (e.g., fc80::ea50:8bff:f217:b628), you can skip the string manipulation entirely. Since your bytea is exactly 16 bytes (the length of an IPv6 address), PostgreSQL allows direct casting:

SELECT ip::bytea::inet AS ipv6_compressed
FROM a;

But since you specifically asked for the expanded format with all zero groups, the first method is the right fit for your needs.

内容的提问来源于stack exchange，提问作者Ek1234