This site uses Discourse to power the comments section. Discourse has the ability to embed comments using JavaScript. This feature automatically creates a new topic on Discourse when a new post is visited for the first time.

By default, the embedded JavaScript will pull the <title> element from a given page and use that as the title of the topic. In the configuration options, you’re able to customize what parts of the title are extracted using Regular Expressions. A screenshot of what this looks like is shown below:

Screenshot of Discourse configuration screen for embedding comments

The default post title on this Jekyll powered site looks like this:

$NAME OF POST| levlaz лев 列弗

When I first set up the embedded comments feature, I didn’t strip out | levlaz лев 列弗 so it showed up in all of the topics on Discourse. After adding a regex \|.*$ to strip out the | and everything after it, Discourse did the right thing and the titles of the topics looked as I expected them to. Unfortunately, I still had 60 topics in discourse that included the suffix.

To fix this, I could either manually update every topic to strip out the title, or (since I am technically a programmer) I could pop into the database and do this instantly with a query that takes advantage of the built in regexp_replace function of PostgreSQL.

If you find yourself in a similar situation where you need to make bulk-updates to Discourse topic titles using a regular expression, you can follow the steps below to get this done.

Note for accessing PostgreSQL

I installed Discourse with the simple 30 minute install method. This implies that everything related to Discourse is running in a single Docker container. If you installed Discourse using some other method, you'll need to find a different way to access the underlying PostgreSQL database.

Enter the Discourse Docker Container

SSH into the server and run docker ps. The output should look like this:

ubuntu@discourse:~$ sudo docker ps
CONTAINER ID        IMAGE                 COMMAND             CREATED             STATUS              PORTS
                         NAMES
3fe14099312a        local_discourse/app   "/sbin/boot"        2 weeks ago         Up 2 weeks          0.0.0.0:80->80/tcp, 0.0.0.0:443->443/tcp   app

Grab the Container ID from the output, and then enter the container with the following command:

docker exec -it $CONTAINER_ID bash

Execute SQL

By default, the PostgreSQL database runs as the discourse user. You can enter a PostgreSQL shell by switching to that user.

su - discourse 
psql 

Before running any queries, I’d double check the regular expression using the online regex tester.

Once you have the regular expression, you can run the following query to update the title of your topics. Be sure to replace $YOUR_REGULAR_EXPRESSION with the regular expression that you need to strip out any extra information.

I would suggest doing one first to make sure it works.

 update topics set fancy_title = regexp_replace(fancy_title, '$YOUR_REGULAR_EXPRESSION', '') where id=1;

Assuming this works as you expect, you can run the same query again for all topics.

 update topics set fancy_title = regexp_replace(fancy_title, '$YOUR_REGULAR_EXPRESSION', '');

The result for me looked like this:

Before:

31 Notes On Installing CentOS 7 Server | levlaz лев 列弗
16 Ubuntu 16.04 on an Intel NUC | levlaz лев 列弗
54 Upgrading and Restarting Salt on OS X | levlaz лев 列弗
57 Installing Nextcloud on a FreeBSD VPS | levlaz лев 列弗

After:

31 Notes On Installing CentOS 7 Server
16 Ubuntu 16.04 on an Intel NUC
54 Upgrading and Restarting Salt on OS X
57 Installing Nextcloud on a FreeBSD VPS

In hindsight, it would have been much easier to add the regular expression to the configuration settings when I set this up. However, I am glad that updating for all existing topics was relatively painless using regexp_replace.