Is it possible to set a composite NOT NULL constraint in PostgreSQL

For logging purposes, in a table I design, I want to store the IP information of users when they logged in.

When a user logs in, IPv4, IPv6 or both IP addresses should be stored. But I want to set a constraint that both cannot be NULL.

Is it possible to do that in Standard SQL with constraints or should I do that using PL/pgSQL in PostgreSQL or in the business tier?

Let's assume the structure of your table is this one:

CREATE  TABLE log_logins 
(
    user_id INTEGER NOT NULL,
    login_time TIMESTAMP NOT NULL DEFAULT now(),
    ip_v4 TEXT /* or any other representation */,
    ip_v6 TEXT /* or any other representation */,
    PRIMARY KEY (user_id, login_time) 
) ;

You can just add a CHECK that guarantees that either one of ip_v4 or ip_v6 is not null, but not both (it makes no sense to me to have both a v4 address and a v6 one; you don't normally use both protocols at the same time). That would be done with the following statement:

ALTER TABLE log_logins
  ADD CONSTRAINT one_and_only_one_of_ip_v4_or_ip_v6
  CHECK ((ip_v4 IS NULL) <> (ip_v6 IS NULL));

If it is reasonable to have both a v4 and a v6 address at the same time, the constraint to use would be:

ALTER TABLE log_logins
  ADD CONSTRAINT at_least_one_of_ip_v4_or_ip_v6
  CHECK ((ip_v4 IS NOT NULL) OR (ip_v6 IS NOT NULL));

Alternatively, take a look at the RhodiumToad/ip4r extension. If you use it, you can represent ip4, ip6 or ipaddress (that can contain either an IPv4 or IPv6 address) in a compact and efficient way, and have a collection of operators on both IP addresses and IP address-ranges. I'd actually recommend it.

Although PostgreSQL includes the inet data type, that can be used to store internet addresses (together with netmasks), the ip4r extension provides a few advantages if you only need an IP address (without the netmask). One of the advantage is a more compact representation, that may play a role if the volume of data to be recorded is big. This extension is really helpful if you need to use ip ranges. One use case would be to join the IP column of the log_logins table to another table containing (arbitrary) IP ranges and the countries to which they correspond.

StackExchange Database Administrators Q#160343, answer score: 18