inet versus text for ip addresses

Поиск
Список
Период
Сортировка
От Dan Langille
Тема inet versus text for ip addresses
Дата
Msg-id 3F05DEA4.5526.6F4CF1C5@localhost
обсуждение исходный текст
Ответы Re: inet versus text for ip addresses  (Bruce Momjian <pgman@candle.pha.pa.us>)
Список pgsql-sql
The PostgreSQL inet datatype stores an holds an IP host address, and 
optionally the identity of the subnet it is in, all in one field.  
This requires 12 bytes.   

Using my "random" data of approximately 8000 IP addresses collected 
during previous polls, I've found the average length of an IP address 
is 13.1 bytes.    An integer requires 4 bytes.

First question: Why not store an option to store just an IP address?  
That should require less than the 12 bytes for inet.

On to the real question:

The existing tables are:

create table recount_ips
(   ipid        serial                not null,   ipaddress   inet                  not null,   primary key (ipid)
);

create unique index recount_ips_ip_address on recount_ips 
(ipaddress);

create table recount_iptopolls
(   pollid      integer               not null,   ipid        integer               not null,   primary key (pollid,
ipid)
);

alter table recount_iptopolls   add foreign key  (pollid)      references recount_polls (pollid) on update restrict on
delete
 
restrict;

alter table recount_iptopolls   add foreign key  (ipid)      references recount_ips (ipid) on update restrict on delete

restrict;



I think a better solution is one table:

create table recount_iptopolls
(   pollid      integer               not null,   ipaddress   inet                  not null,   primary key (pollid,
ipaddress)
);

alter table recount_iptopolls   add foreign key  (pollid)      references recount_polls (pollid) on update restrict on
delete
 
restrict;

It removes a table and the associated primary key, and removed a 
foreign key from the modified recount_iptopolls table.

Comments?
-- 
Dan Langille : http://www.langille.org/



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

Предыдущее
От: Evil Azrael
Дата:
Сообщение: Re: INSERT ... SELECT problem in Mysql
Следующее
От: Ian Barwick
Дата:
Сообщение: Re: INSERT ... SELECT problem in Mysql