TILMay 15, 2019by Alexander Spitsyn

Handling IP addresses using PostgreSQL

PostgreSQL provides a inet and cidr datatypes for storing net addresses and proceed operations with them.

Host address and it's subnet can be stored with inet, while cidr can contain only network address:

select inet '';



select cidr ''; -- valid cidr



select cidr ''; -- invalid: cidr must not be a host address

ERROR:  invalid cidr value: ""

LINE 1: select cidr '';


DETAIL:  Value has bits set to right of mask.

In case there's no number after slash in cidr address the netmask is to equal 32:

select cidr('');



The value above represents a subnet address, while the same value passed to inet represents a host:

select inet('');



Checking inclusion or equality can be performed with >>= and <<= operators:

select inet '' >>= inet ''; -- returns true

select cidr '' >>= inet ''; -- returns false

select cidr '' >>= cidr ''; -- returns true

And getting a netmask by a net address can be performed with netmask:

select netmask(inet('')); -- returns

select netmask(cidr('')); -- returns

TILFebruary 25, 2019by Arina Shmeleva

How to add a line break to PostgreSQL?

You have to insert some data into your PostgreSQL table that has line break. As a result, the cell will look like this:

First line

Second line

To use "escape sequences" in a string literal you need to use an "extended" constant. You can specify the escape character by prefixing the letter E:

UPDATE posts SET body = E'First Line\nSecond line.' WHERE id = 2701;

LongreadsJanuary 22, 2018by Igor Alexandrov

Rails 5 attributes API, value objects and JSONB

A guide on how to use value objects in your Ruby on Rails applications with PostgreSQL JSONB.
Read more