Avinash Sultanpur's Blog

Code journeys

Postgres Upserts

November 04, 2016 — Avinash Sultanpur

Here is a contrived example of how to use upserts in PostgreSQL (version 9.5 and above).

So you have a table to keep track of visitors to a website and would like to store the source IP address along with a count of the total number of visits.

CREATE TABLE sitecount (
   host_address inet PRIMARY KEY,
   visit_count integer
);

When it is time to update the number of visits from a particular host, we can perform an INSERT or an UPDATE, depending on whether a row exists for that host or not, with a single query.

INSERT INTO sitecount VALUES ('192.168.1.23', 1)
   ON CONFLICT (host_address)
   DO UPDATE SET visit_count=sitecount.visit_count + excluded.visit_count;

It is important to mention the conflict target host_address and to qualify the column names in the UPDATE clause. excluded is a special table which contains the values provided in the INSERT statement.

More information at PostgreSQL documentation.

Tags: postgres

Disabling some fonts on Debian

November 04, 2016 — Avinash Sultanpur

I often find the need to disable some fonts on a freshly installed Debian system. One font that troubles me particularly is the Nimbus Mono, which gets used where ever a constant-width font is required.

Having had no idea about Font Manager, I located the font file for Nimbus Mono (locate Nimbus) and then found the package which provided that file (dpkg -S <path/to/font/file>) and uninstalled it (libgs9-common). Recently the package got reinstalled when I upgraded all packages on my system which led me to explore other possibilities.

I installed the font-manager package and used it to disable the entire Nimbus family of fonts. It provides a somewhat counter-intuitive GUI for managing fonts on your system. The tricky part to learn was to switch to the Manage mode from the Browse mode by clicking on the top-left part of the window. One can search or browse the font family and disable the fonts within it or the entire family by unchecking the checkbox.

Font manager produces the following snippet to tell the font-config utility to not use some fonts:

$ cat ~/.config/fontconfig/conf.d/78-Reject.conf                     
<?xml version="1.0"?>
<!DOCTYPE fontconfig SYSTEM "fonts.dtd">
<!-- Generated by Font Manager. Do NOT edit this file. -->
<fontconfig>
  <selectfont>
    <rejectfont>
      <pattern>
        <patelt name="family">
          <string>Nimbus Roman No9 L</string>
        </patelt>
      </pattern>
      <pattern>
        <patelt name="family">
          <string>Nimbus Mono L</string>
        </patelt>
      </pattern>
      <pattern>
        <patelt name="family">
          <string>Nimbus Sans L</string>
        </patelt>
      </pattern>
    </rejectfont>
  </selectfont>
</fontconfig>

Tags: fonts, linux, debian

Hello World!

November 04, 2016 — Avinash Sultanpur

A hello world blog post using bashblog.

Tags: