9

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?

7
  • 4
    Why do you need two different columns? The inet data type can store IPv4 and IPv6 addresses in the same column: postgresql.org/docs/current/static/…
    – user1822
    Commented Jan 8, 2017 at 21:23
  • Can they be both NOT NULL? And what would that mean, can a user have 2 different IP addresses, one IP4 and one IP6? Commented Jan 8, 2017 at 21:24
  • @TypoCubeᵀᴹ Only one of them can be NULL.
    – ukll
    Commented Jan 8, 2017 at 21:42
  • I ask again: can they both be NOT NULL? Commented Jan 8, 2017 at 21:49
  • @TypoCubeᵀᴹ I am looking for the combination. Either (IPv4) or (IPv6) or (both IPv4 and IPv6 together) can have value(s). If both are NOT NULL, then I cannot have a situation such as IPv4 with a valid value and IPv6 with a NULL value. (It is just what I want. I don't discuss if it is the proper way to do it or not.) Did I understand your question right?
    – ukll
    Commented Jan 8, 2017 at 22:09

3 Answers 3

18

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.

3
  • Thank you for the explanation and the link for ip4r extension.
    – ukll
    Commented Jan 8, 2017 at 21:16
  • 2
    No need for an extension, Postgres' inet data type can store ipv4 and ipv6 addresses.
    – user1822
    Commented Jan 8, 2017 at 21:23
  • @a_horse_with_no_name: Yes, it can, but read the RATIONALE on github.com/RhodiumToad/ip4r, and you'll find that they have a different use-case, and some performance gains. This module is specially useful when you use not just IPs, but arbitrary ranges (for instance, when you want to JOIN an IP address on table a to a range of IPs on table b, that might be used, for instance, to assign countries to IP ranges). On the other hand, if you need to make huge logs, the data representation of IPs takes less space than that of INETs, and that might count.
    – joanolo
    Commented Jan 8, 2017 at 21:29
8

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 
);
3
  • @ukll did you mark the right one Commented Jan 8, 2017 at 21:23
  • 1
    Yes, I am satisfied with your answer. But I accepted the other one for showing how to not accept both fields to have a value too (not for the external link) in addition to the answer of my question.
    – ukll
    Commented Jan 8, 2017 at 21:34
  • That's cool, didn't realize that's what you were looking for. Commented Jan 8, 2017 at 21:43
-3

This is my solution for sequelize migration file in "up" function

queryInterface.sequelize.getQueryInterface().addConstraint('log_logins', {
  fields: ['ipv4', 'ipv6'],
  type: 'check',
  name: 'ipv4_or_ipv6_is_null',
  where: { [Sequelize.Op.or]: [{ ipv4: null }, { ipv6: null }] },
}),
1
  • why not? :(((((
    – hellvisor
    Commented Jun 7, 2022 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.