CREATE CAST: Casting integer to IP in PostgreSQL

05.2014 / Category: / Tags: | | |

Once in a while, you have to juggle around with IP addresses and store them / process them in an efficient way. To do so PostgreSQL provides us with various data types, including cidr and inet. The beauty here is that those two types make sure that no bad data can be inserted into the database:

However, an IPv4 address is basically just a 4 byte integer, which happens to be displayed in a fairly convenient way. So, why not cast an integer value to an IPv4 address?

Unfortunately there is no automatic type cast available to do the job.


Defining custom type casts

This is exactly when CREATE CAST can come to your rescue. In PostgreSQL defining your own type cast is not more than a 5 minute task.

Here is the syntax of CREATE CAST:

The clue here is that you can use a normal stored procedure to define a type cast. All you have to do is to write a procedure accepting just one parameter returning the proper value. Here is how it works:

First of all we define a simple function. The beauty here is that we can simply add an integer to and return the value. Pretty simple I would say.

Finally we can define the cast ...

... and enjoy the fruits of our work:

Simple casts like that should not take long to implement. However, your life can be a lot easier if you got all the tools in place you will need for your daily work.

For interesting blogs on the topic of PostgreSQL and networks, take a look in our networking blog spot.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram