<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:
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
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:
|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 лев 列弗|
|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
You can add a comment via Email. Or use the embedded discourse form shown below: