PostgreSQL数据类型：网络地址类型

概述感谢原作者整理分享。在此mark一下。 官方文档：http://www.postgresql.org/docs/9.4/interactive/datatype-net-types.html 一、cidr postgres=# create table test (id int, name text);CREATE TABLEpostgres=# \d test      Table "pu

感谢原作者整理分享。在此mark一下。

官方文档：http://www.postgresql.org/docs/9.4/interactive/datatype-net-types.html

一、cIDr

postgres=#createtabletest(IDint,nametext);CREATEtablepostgres=#\dtesttable"public.test"Column|Type|ModifIErs--------+---------+-----------ID|integer|name|text|postgres=#altertabletestaddcolumnipcIDr;ALTERtablepostgres=#\dtesttable"public.test"Column|Type|ModifIErs--------+---------+-----------ID|integer|name|text|ip|cIDr|

postgres=#insertintotestvalues(1,'a','192.168.1.100');INSERT01postgres=#select*fromtest;ID|name|ip----+------+------------------1|a|192.168.1.100/32(1row)postgres=#insertintotestvalues(2,'b','192.168.0.0/16');INSERT01postgres=#select*fromtest;ID|name|ip----+------+------------------1|a|192.168.1.100/322|b|192.168.0.0/16(2rows)postgres=#insertintotestvalues(3,'c','192.168.1.0/24');INSERT01postgres=#select*fromtest;ID|name|ip----+------+------------------1|a|192.168.1.100/322|b|192.168.0.0/163|c|192.168.1.0/24(3rows)

查询使用

postgres=#select*fromtestwhereip='192.168.1.100';ID|name|ip----+------+------------------1|a|192.168.1.100/32(1row)postgres=#select*fromtestwhereip>='192.168.1.0/24';ID|name|ip----+------+------------------1|a|192.168.1.100/323|c|192.168.1.0/24(2rows)postgres=#select*fromtestwhereip>='192.168.0.0/16';ID|name|ip----+------+------------------1|a|192.168.1.100/322|b|192.168.0.0/163|c|192.168.1.0/24(3rows)postgres=#updatetestsetip='192.168.1.101/32'whereID=2;UPDATE1postgres=#updatetestsetip='192.168.1.102/32'whereID=3;UPDATE1postgres=#select*fromtest;ID|name|ip----+------+------------------1|a|192.168.1.100/322|b|192.168.1.101/323|c|192.168.1.102/32(3rows)postgres=#select*fromtestwhereipbetween'192.168.1.100'and'192.168.1.101';ID|name|ip----+------+------------------1|a|192.168.1.100/322|b|192.168.1.101/32(2rows)postgres=#select*fromtestwhereipbetween'192.168.1.100'and'192.168.1.102';ID|name|ip----+------+------------------1|a|192.168.1.100/322|b|192.168.1.101/323|c|192.168.1.102/32(3rows)

二、inet

将cIDr修改为inet

postgres=#\dtesttable"public.test"Column|Type|ModifIErs--------+---------+-----------ID|integer|name|text|ip|cIDr|postgres=#altertabletestaltercolumniptypeinet;ALTERtablepostgres=#\dtesttable"public.test"Column|Type|ModifIErs--------+---------+-----------ID|integer|name|text|ip|inet|postgres=#select*fromtest;ID|name|ip----+------+---------------1|a|192.168.1.1002|b|192.168.1.1013|c|192.168.1.102(3rows)postgres=#updatetestsetip='192.168.0.0/16'whereID=3;UPDATE1postgres=#select*fromtest;ID|name|ip----+------+----------------1|a|192.168.1.1002|b|192.168.1.1013|c|192.168.0.0/16(3rows)postgres=#updatetestsetip='192.168.1.0/24'whereID=2;UPDATE1postgres=#select*fromtest;ID|name|ip----+------+----------------1|a|192.168.1.1003|c|192.168.0.0/162|b|192.168.1.0/24(3rows)

可见，inet默认32位掩码的ip是不带'/32'的

postgres=#select*fromtestwhereip>='192.168.1.100';ID|name|ip----+------+---------------1|a|192.168.1.100(1row)postgres=#select*fromtestwhereip>='192.168.1.1';ID|name|ip----+------+---------------1|a|192.168.1.100(1row)postgres=#select*fromtestwhereip>='192.168.1.101';ID|name|ip----+------+----(0rows)postgres=#select*fromtestwhereip>='192.168.1.0/32';ID|name|ip----+------+---------------1|a|192.168.1.100(1row)postgres=#select*fromtestwhereip>='192.168.1.0/16';ID|name|ip----+------+----------------1|a|192.168.1.1002|b|192.168.1.0/24(2rows)postgres=#select*fromtestwhereip>='192.168.0.0/16';ID|name|ip----+------+----------------1|a|192.168.1.1003|c|192.168.0.0/162|b|192.168.1.0/24(3rows)

使用跟cIDr差不多

三、macaddr

postgres=#\dtesttable"public.test"Column|Type|ModifIErs--------+---------+-----------ID|integer|name|text|ip|inet|postgres=#altertabletestaddcolumnmacmacaddr;ALTERtablepostgres=#\dtesttable"public.test"Column|Type|ModifIErs--------+---------+-----------ID|integer|name|text|ip|inet|mac|macaddr|postgres=#select*fromtest;ID|name|ip|mac----+------+----------------+-----1|a|192.168.1.100|3|c|192.168.0.0/16|2|b|192.168.1.0/24|(3rows)postgres=#updatetestsetmac='08:00:2b:01:02:03'whereID=1;UPDATE1postgres=#select*fromtest;ID|name|ip|mac----+------+----------------+-------------------3|c|192.168.0.0/16|2|b|192.168.1.0/24|1|a|192.168.1.100|08:00:2b:01:02:03(3rows)postgres=#updatetestsetmac='08:00:2b:01:02:04'whereID=2;UPDATE1postgres=#updatetestsetmac='08:00:2b:01:02:05'whereID=3;UPDATE1postgres=#select*fromtest;ID|name|ip|mac----+------+----------------+-------------------1|a|192.168.1.100|08:00:2b:01:02:032|b|192.168.1.0/24|08:00:2b:01:02:043|c|192.168.0.0/16|08:00:2b:01:02:05(3rows)查询使用postgres=#select*fromtestwheremac='08:00:2b:01:02:03';ID|name|ip|mac----+------+---------------+-------------------1|a|192.168.1.100|08:00:2b:01:02:03(1row)postgres=#select*fromtestwheremac>'08:00:2b:01:02:03';ID|name|ip|mac----+------+----------------+-------------------2|b|192.168.1.0/24|08:00:2b:01:02:043|c|192.168.0.0/16|08:00:2b:01:02:05(2rows)

Postgresql默认还不支持iprange，需要安装ip4r的扩展，详见：http://pgfoundry.org/projects/ip4r/

总结

以上是内存溢出为你收集整理的PostgreSQL数据类型：网络地址类型全部内容，希望文章能够帮你解决PostgreSQL数据类型：网络地址类型所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。