[GENERAL] appropriate column for storing ipv4 address

Поиск
Список
Период
Сортировка
От jonathan vanasco
Тема [GENERAL] appropriate column for storing ipv4 address
Дата
Msg-id 7B929C61-7474-430A-BEB2-7C90AFABEF76@2xlp.com
обсуждение исходный текст
Ответы Re: [GENERAL] appropriate column for storing ipv4 address  (Paul Jungwirth <pj@illuminatedcomputing.com>)
Re: [GENERAL] appropriate column for storing ipv4 address  (Steve Atkins <steve@blighty.com>)
Список 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
foran 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
thatthe right move?  is there a better option? 

thanks in advance.

/ jonathan

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] is (not) distinct from
Следующее
От: Paul Jungwirth
Дата:
Сообщение: Re: [GENERAL] appropriate column for storing ipv4 address