Picking a baby name with PostgreSQL® and FuzzyStrMatch
Tags: postgres, fuzzystrmatch, stringmatching
Published: 2022-10-20 12:30:37

---
title: Picking a baby name with PostgreSQL® and FuzzyStrMatch
published: true
date: 2022-10-20 08:45:00 UTC
tags: postgresql, fuzzystrmatch, stringmatching
canonical_url: https://aiven.io/blog/picking-a-baby-name
---

![Picking a baby name with PostgreSQL® and FuzzyStrMatch](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r9fqlf3pb8tsehsfpdz2.png)

Naming variables is one of the hardest problems in Computer Science, but when the variable is a new life, the challenge can rise quite significantly. Family names, personal preferences, celebrities, vetoes, word sound, it all goes into the decision mix! Even more, the possible pool of names is huge - and we all know that the traditional "let's browse for names in a book" approach doesn't scale anymore with the ever-increasing set of options. 

Help is needed, and this blog post shows how PostgreSQL® can help us refine our search and get us closer to a final selection.

> **Alert:** all choices, decisions, and names used in this example are pure imagination and don't represent any kind of recommendation!


## Get the dataset

To start with, we need a dataset of valid names. This post uses [this collection of baby names from data.world](https://data.world/alexandra/baby-names), for which you need a valid account to access the data.

Once you have created the account, you will be able to download the dataset in a `babynames-clean.csv` file on your local machine.

## Create a PostgreSQL service and load the data

You can run PostgreSQL locally or, if you prefer, create an instance using Aiven for PostgreSQL® with the [Aiven CLI command](https://developer.aiven.io/docs/tools/cli/service.html#avn-service-create), as shown below:

```shell
avn service create demo-pg      \
    --service-type pg           \
    --cloud google-europe-west3 \
    --plan hobbyist
```

The command starts creation of a PostgreSQL service (`--service-type pg`) on the Google Europe West 3 cloud region with the minimal `hobbyist` plan, which is enough for our needs. The service takes a couple of minutes to start, and we can wait for it to be up with:

```shell
avn service wait demo-pg
```

Once the service is in `RUNNING` state, we can connect to it with the following command that calls `psql` under the hood:

```shell
avn service cli demo-pg
```

After connecting we need to create a table, since `babynames-clean.csv` doesn't contain any header:

```sql
create table names (name text, gender text);
```

Now it's time to copy the data:

```sql
\copy names from  'babynames-clean.csv' csv
```

Now the data is loaded in the `names` table and we can start the research of our imaginary baby's name.

## Initial scanning and filtering of the data 

Okay, time to choose a name! Is it going to be a boy or a girl? If you already have that information, it's going to cut out half of the options, since the query below shows that we have a more or less equal amount of male and female names.


```sql
select 
    gender, 
    count(*) nr_names 
from names 
group by gender;
```

The result shows slightly more boys' names than girls'.

```
 gender | nr_names 
--------+----------
 girl   |     3345 
 boy    |     3437 
(2 rows)
```

What if we don't know the gender yet, and we have almost _seven thousand names_ to pick from? Scanning all of them one by one is not an option. We need to apply some filters.
Do you have a long surname? It might be nice to save the poor kid from the stress of writing out a long first name as well. Let's filter for names with fewer than 6 characters.

```sql
select count(*) from names where length(name) < 6;
```

We still have lots of results (2632), so some more pruning is needed ... I personally like names ending with vowels - can we add this constraint? Yes we can, with the help of [regular expressions](https://www.postgresql.org/docs/current/functions-matching.html):

```sql
select count(*) from names 
where length(name) < 6
and name similar to '%(a|e|i|o|u|y)';
```

With the help of regular expressions we can take it even further, like specifying 1 `l` or 2 consecutive `l`s:

```sql
select * 
from names 
where 
    length(name) < 6
    and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
limit 10;
```

Regular expressions can only help so much, and we still have too many names in our list. We need some more ways to exclude options.

## Avoid similar names

We all have names we never want to hear again. Maybe they remind us of someone we don't want to think about, or they're just taken by somebody else we hear about way too often. It's easy to avoid these names, but we can use PostgreSQL's [FuzzyStrMatch](https://www.postgresql.org/docs/current/fuzzystrmatch.html) to go a step further and eliminate anything even vaguely similar. We can install the extension with:

```sql
create extension fuzzystrmatch;
```

The extension brings us several functions. The first one, named [Levenstein difference](https://en.wikipedia.org/wiki/Levenshtein_distance), helps us understand how many characters make two strings differ. Let's say, for example, we don't want anything that is written too similar to `Allie`.

```sql
select *, 
    levenshtein(name, 'Allie') diff
from names 
where 
    length(name) < 6
    and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
order by diff
limit 10;
```

Returns

```
 name  | gender | diff
-------+--------+------
 Allie | boy    |    0
 Altie | girl   |    1
 Alfie | boy    |    1
 Arlie | boy    |    1
 Alvie | boy    |    1
 Alcie | girl   |    1
 Algie | boy    |    1
 Ellie | boy    |    1
 Ollie | boy    |    1
 Alvia | boy    |    2
(10 rows)
```

We can see that the Levenshtein difference between:

* `Allie` and `Allie` is 0 since they are the same string
* `Allie` and `Altie` is 1 (the `t`)
* `Allie` and `Alfie` is 1 (the `f`)
* `Allie` and `Alvia` is 2 (the `v` and `a`)

We can remove all names with a Levenshtein difference of less than 3, since this helps us avoid names written too similarly to `Allie`.

```sql
select count(*)
from names 
where 
    length(name) < 6
    and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
    and levenshtein(name, 'Allie') > 3;
```

We still have 235 rows, but the difference in the number of characters is just the beginning. We don't even want to hear a sound _similar_ to `Allie`! Luckily the FuzzyStrMatch extension brings us another function, named `soundex` that provides the [Soundex code](https://en.wikipedia.org/wiki/Soundex) of a string. Let's try with `Allie` itself:

```sql
select soundex('Allie');
```

The result is `A400` which, by itself, is not very useful. But FuzzyStrMatch also offers `difference` which, despite the name, provides a Soundex similarity score between 0 (two strings sound different) to 4 (two strings sound the same). If we try with our dataset:

```sql
select *, 
    soundex(name) sdx, 
    difference(name, 'Allie') sdx_score
from names 
where 
    length(name) < 6
    and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
    and levenshtein(name, 'Allie') > 3
order by sdx_score desc
limit 5;
```

This shows that `Clay`, `Milo`, `Riley`, `Wiley` and `Rolla` sound a bit too close to our `Allie`.

```
 name  | gender | sdx  | sdx_score
-------+--------+------+-----------
 Clay  | boy    | C400 |         3
 Milo  | boy    | M400 |         3
 Riley | boy    | R400 |         3
 Wiley | boy    | W400 |         3
 Rolla | boy    | R400 |         3
(5 rows)
```

We want to go for an entirely different sound, so let's remove anything with a Soundex score greater than 2.

```sql
select count(*)
from names 
where 
    length(name) < 6
    and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
    and levenshtein(name, 'Allie') > 3
    and difference(name, 'Allie') <= 2;
```

## The final touch: the metaphone

We still have 115 rows, a few too many to choose from. The final step in the selection could be based on a name of which we like the sound, and therefore we'd like to explore similar-sounding ones. Let's take `Marlo` as an example. To retrieve names that sound similar we can either use the Soundex function again, or we could evolve our approach and use the [Metaphone](https://en.wikipedia.org/wiki/Metaphone) or double metaphone for higher accuracy.

```sql
select dmetaphone('Marlo') dmet;
```

The double metaphone function returns the `MRL` string. We can use that, together with the Levenshtein difference, to find all strings which vary from it by only one character.

```sql
select count(*)
from names 
where 
    length(name) < 6
    and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
    and levenshtein(name, 'Allie') > 3
    and difference(name, 'Allie') <= 2
    and levenshtein(dmetaphone(name), dmetaphone('Marlo')) <=1;
```

Now we have to parse only 26 rows, and frankly, this is where the hard choices begin. Still, with the help of PostgreSQL, some regular expressions and the FuzzyStrMatch extension, we were able to prune a lot of our possible dataset. 

Which name is best? The final selection is, of course, up to you.

## Ready-to-use functions for string similarity

String similarity is not an easy topic, but luckily the [FuzzyStrMatch extension](https://www.postgresql.org/docs/current/fuzzystrmatch.html) provides a few functions like Levenshtein, Soundex and Metaphone that can help us solve some common problems when comparing texts.

Some further resources:
* [Aiven for PostgreSQL®](https://aiven.io/postgresql)
* [FuzzyStrMatch](https://www.postgresql.org/docs/current/fuzzystrmatch.html): to understand functions and additional parameters
* [Pg similarity](https://github.com/eulerto/pg_similarity): another PostgreSQL extension providing a different set of text similarity functions
* [Pattern matching](https://www.postgresql.org/docs/current/functions-matching.html): to review the details of regular expression usage