Is it possible to set a composite NOT NULL constraint in PostgreSQL
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.
Yes. It is. Just put a constraint on the table.
CREATE TEMP TABLE foo (
userid serial PRIMARY KEY,
ipv4 inet CHECK (family(ipv4) = 4),
ipv6 inet CHECK (family(ipv6) = 6),
CHECK (ipv4 IS NOT NULL OR ipv6 IS NOT NULL)
);
Or, you can just use the same type (inet
) which will support ipv4 or ipv6..
CREATE TEMP TABLE foo (
userid serial PRIMARY KEY,
userip inet NOT NULL
);