IS_IPV4

Return true if the input string is a valid dotted-decimal IPv4 address.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IS_IPV4(str)

Description

## Overview Returns TRUE if the input string is a syntactically valid IPv4 address written in the standard dotted-decimal form, FALSE otherwise, and NULL for a NULL input. Use this function to validate log ingest columns, filter rows by address family, or pre-check input before casting to an INET type. ## Validation rules - Exactly four octets separated by three dots. - Each octet is a decimal integer in the range 0 to 255 with no leading zero padding (for example, `192.168.001.001` is rejected). - No leading or trailing whitespace is allowed; trim the input before calling if needed. - No CIDR suffix. `192.168.1.0/24` returns FALSE; use a separate parser for CIDR ranges. - IPv6 addresses, including IPv4-mapped forms like `::ffff:192.168.1.1`, return FALSE. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Deterministic and side effect free. - Does not verify reachability or assignment. A valid address may still be unroutable in practice. ## Compatibility - Matches the PG-compat IS_IPV4 semantics for dotted-decimal addresses.

Parameters

Name	Type	Description
`str`		Specifies the string to validate as an IPv4 address in dotted-decimal notation.

Examples

-- Canonical private address
SELECT IS_IPV4('192.168.1.1');  -- true

-- Out-of-range octet
SELECT IS_IPV4('256.0.0.1');  -- false

-- IPv6 loopback is not IPv4
SELECT IS_IPV4('::1');  -- false

-- Filter to IPv4-only rows
SELECT request_id, source_ip
FROM obs.logs.access
WHERE IS_IPV4(source_ip);

-- Partition rows by address family
SELECT
  SUM(CASE WHEN IS_IPV4(source_ip) THEN 1 ELSE 0 END) AS ipv4_count,
  SUM(CASE WHEN IS_IPV6(source_ip) THEN 1 ELSE 0 END) AS ipv6_count
FROM obs.logs.access;

-- NULL propagation
SELECT IS_IPV4(NULL);  -- NULL

Pitfalls

CIDR notation is rejected. `192.168.1.0/24` returns FALSE even though it is a legal CIDR block. Strip the suffix before validation or use a dedicated network-range parser.
IPv4-mapped IPv6 addresses (for example, `::ffff:192.168.1.1`) return FALSE because the textual form is not dotted-decimal. Normalize at the caller if you want to accept both.
Octets with leading zeros (`192.168.001.001`) are rejected to avoid ambiguity with octal interpretation. Strip zero padding before validation if your source formats addresses that way.
Leading or trailing whitespace fails validation. Apply TRIM or CLEAN_SPACES first if your ingest format allows stray spaces.