LongreadsDecember 20, 2019by Igor Alexandrov

Two edge cases in PostgreSQL full-text search

We widely use PostgreSQL full-text search in our projects. It is fast, reliable, and doesn't add any additional technical complexity. But sometimes it may not work as you expect it too.

Read more
TILSeptember 17, 2019by Alexander Budchanov

Renaming keys in PostgreSQL JSON / Migration template for gem Recorder

In our project, we used gem Recorder. It saves changes in jsonb format.

Once we need to rename a column in model Contact, but we didn't want to lose logs.

I introduce you template of migration for this case.

class RenameOldColumnToNewColumn < ActiveRecord::Migration[5.2]

  def up

    rename_column :contacts, :old_column, :new_column

    execute <<~SQL

      UPDATE recorder_revisions SET data = jsonb_set(data #- '{changes,old_column}', '{changes,new_column}', data#>'{changes,old_column}') WHERE data#>'{changes}'?'old_column';



  def down

    rename_column :contacts, :new_column, :old_column

    execute <<~SQL

      UPDATE recorder_revisions SET data = jsonb_set(data #- '{changes,new_column}', '{changes,old_column}', data#>'{changes,new_column}') WHERE data#>'{changes}'?'new_column';




You can simple replace contacts, new_column and old_column to your table name and columns names.

TILSeptember 04, 2019by Vitaly Platonov

Postgres. Search Array type columns

Say we have a table with a column of an array type. At some point, we want to be able to select records with a specific value(s) which the array column may have.

Here are three ways to do different kinds of searches.

1) Use ANY operator when searching with one value:

SELECT * FROM mytable WHERE 'first_type' = ANY(types_column);

2) Go with the "contains" operator (“@>”) when you look for a specific set of values (the order of values doesn’t matter):

SELECT * FROM mytable WHERE types_column @> '{"first_type", "second_type"}';

The values “first_type” and “second_type" must be in the types_column column for a record to be selected.

3) Whenever you need to search any values that a column may have - use the "overlap" operator (“&&”)

SELECT * FROM mytable WHERE types_column && '{"first_type", "second_type"}';

One of the values “first_type” or “second_type" must be in the types_column column for a record to be selected.

TILJuly 31, 2019by Igor Alexandrov

Migrate tags in Rails to PostgreSQL array from ActsAsTaggableOn

ActsAsTaggableOn is a swiss army knife solution if you need to add tags to your ActiveRecord model.

Just by adding one gem to your Gemfile and acts_as_taggable to the model you get everything you need: adding tags, searching for a model by tag, getting top tags, etc. However, sometimes you don't need all these.

In our project, we used acts_as_taggable to store tags for Note model. Then we displayed a list of notes on several pages with assigned tags and had autocompleted input for tags on Note form. Everything worked well, but since we use PostgreSQL, I decided to store tags as an array in Note model.

First of all, I added tags Array<String> column to Note, after this migrated actsastaggable tags to notes table with migration.

class MigrateNoteTags < ActiveRecord::Migration[5.2]

  def change

    execute <<-SQL

    UPDATE notes 

    SET tags = grouped_taggings.tags_array 





        ARRAY_AGG ( tags.NAME ) tags_array 



        LEFT JOIN tags ON taggings.tag_id = tags.ID 


        taggable_type = 'Note' 

      GROUP BY


      ) AS grouped_taggings 


      notes.ID = grouped_taggings.taggable_id




To have backward compatibility, I added Note#tag_list method:

def tag_list

  tags.join(', ')


The last thing is to add the ability to search for tags. Since there about 500k records in the Notes table, I decided to create an SQL view:



  ( tags ) AS name,

  COUNT ( * ) AS taggings_count 





That's it! It takes from 100ms to 150ms to search for tags in this view, which is fine for me.

If you have more significant data sets, then the best would be to create tags table and add triggers to notes table that will update tags on INSERT/UPDATE/DELETE.

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;