appropriate column for storing ipv4 address

From: jonathan vanasco <postgres(at)2xlp(dot)com>
To: pgsql-general general <pgsql-general(at)postgresql(dot)org>
Subject: appropriate column for storing ipv4 address
Date: 2017-03-01 16:39:43
Message-ID: 7B929C61-7474-430A-BEB2-7C90AFABEF76@2xlp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I have to store/search some IP data in Postgres 9.6 and am second-guessing my storage options.

Would anyone mind giving this a quick look for me?

Right now I have two tables, and am just using cidr for both:

create table tracked_ip_address (
id SERIAL primary key,
ip_address CIDR not null
);

create table tracked_ip_block (
id SERIAL primary key,
block_cidr CIDR not null,
ownserhip_data TEXT
);

The types of searching I'm doing:

1. on tracked_ip_address, I'll search for neighboring ips.
e.g.
select * from tracked_ip_address where ip_address << '192.168'::CIDR;
select * from tracked_ip_address where ip_address << '192.168.1'::CIDR;

2. on tracked_ip_block, i search/join against the tracked_ip_address to show known ips in a block, or a known block for an ip.

i used cidr instead of inet for the ip_address because it saved me a cast on joins and appears to work the same. was that the right move? is there a better option?

thanks in advance.

/ jonathan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paul Jungwirth 2017-03-01 16:57:51 Re: appropriate column for storing ipv4 address
Previous Message Tom Lane 2017-03-01 16:12:29 Re: is (not) distinct from