Return true if the input string is a valid IPv6 address.
IS_IPV6(str)## Overview Returns TRUE if the input string is a syntactically valid IPv6 address, FALSE otherwise, and NULL for a NULL input. Use this function to validate log-ingest columns, partition rows by address family, or pre-check input before casting to an INET type. ## Validation rules - Eight colon-separated groups of 1 to 4 hexadecimal digits, or a compressed form using `::` to elide one run of all-zero groups. - Mixed notation with a trailing IPv4 literal is accepted (for example, `::ffff:192.168.1.1`). - Zone identifiers (for example, `fe80::1%eth0`) are rejected; strip the `%zone` suffix before validation. - Leading and trailing whitespace is rejected; trim first if needed. - CIDR suffixes (`/64`) are rejected. - At most one `::` sequence is allowed. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Case insensitive for hexadecimal digits. - Deterministic and side effect free. - Does not verify reachability or assignment. ## Compatibility - Accepts the textual forms defined by RFC 4291 and normalized by RFC 5952.
| Name | Type | Description |
|---|---|---|
str | Specifies the string to validate. Full, compressed, and mixed IPv4-in-IPv6 forms are accepted. |
-- Full eight-group form
SELECT IS_IPV6('2001:0db8:85a3:0000:0000:8a2e:0370:7334'); -- true-- Compressed loopback
SELECT IS_IPV6('::1'); -- true-- IPv4-mapped IPv6 address
SELECT IS_IPV6('::ffff:192.168.1.1'); -- true-- IPv4 dotted-decimal is not IPv6
SELECT IS_IPV6('192.168.1.1'); -- false-- Garbage input
SELECT IS_IPV6('not-an-ip'); -- false-- Partition a log table by address family
SELECT family, COUNT(*)
FROM (
SELECT CASE WHEN IS_IPV6(source_ip) THEN 'v6' ELSE 'v4' END AS family
FROM obs.logs.access
) t
GROUP BY family;