Category Archives: Databases

PostgreSQL Command History

The command-line PostgreSQL database client keeps a command history.

Much like on a Linux console, you can use the up arrow to scroll back through previous commands. This can be handy if you want to repeat a command, or to change a parameter on a long query.

One other thing that is less widely-known is that you can view the ENTIRE history by typing “\s”. Backslash-s will function pretty much the same as the Linux “history” command, and can give you multiple pages of commands.

Much like the history of the Bash shell, the Postgres command history is stored in your home directory in the hidden file .psql_history. Although this is handy, it can also be a security risk to keep old commands where prying eyes can find them. You can easily delete this file, or clear it using the command:

 truncate -s 0 .psql_history

I like the truncate command because it’s a nice, clean, one-step way to clear a file without deleting it.

How to Insert Values Into a Table from a Select Query in PostgreSQL

I recently had to do a data migration where I needed to populate a new table with some data from another table.

Rather than perform some sort of export, transform, and import, it was much easier to do this via a select query within Postgres itself.

Although you can use the star operator, it’s safer and smarter to name fields specifically, in order. In addition, you should manually verify your select query first.

In my case, there were a set of a few fields I wanted to insert, and converted into a select query, looked like this:

SELECT date_added, description, last_updated, name, type FROM information;

This gave me all of the fields I wanted, and I verified that the data looked good.

Next, I added this to an insert query:

INSERT INTO information_subset (date_added, description, last_updated, name, type) SELECT date_added, description, last_updated, name, type FROM information;

Although it’s something that seems like it might be complex, inserting into one table using data from another is quite simple in Postgres and most other databases.

Setting Up a Redash Dashboard

This was originally posted on wbsrch.com. It is reproduced here to preserve history.

The more WbSrch evolves, the more it becomes necessary to keep track of a bunch of metrics.

Until now we’ve been using a mix of simple report pages and raw SQL queries. It has worked well enough, but not having a clean way to track things in a single place is a nuisance.

That’s why I was happy to discover the redash.io open source project. It’s a query tool meant to be used for setting up business intelligence dashboards and it works with a wide range of databases.

No stranger to code, I tried to check out the GitHub source and get it running on my local machine. It didn’t quite work out. They have a bootstrap script, and it had some trouble with my particular system setup (it fell over when it came to configuring local database users).

But they also have EC2 AMI images you can launch to get running in AWS. I fired up an Amazon micro instance on the free tier and had the app running in seconds. It only took some minor configuration to get set up with my SSL certificate, and I was ready to go.

Adding a Database Connection to Redash

Connecting my three PostgreSQL databases was easy and the clean interface made it easy to find the query editor. After running a few queries I had the feel for how things worked well enough to save them. It also lets you set a refresh interval on your queries so you can have data refresh daily, hourly, or whatever. Results are cached so you’re not taxing your database gathering totals every page load.

Redash Query Editor

After you have a few queries, you can start adding them to a dashboard as panels. You just select the query name, the visualization type (you get table by default, but can add graphs and charts in the query builder), and the widget size.

This is a dashboard that I built to keep track of the search traffic and index state for the Somali-language version of WbSrch:

Redash Dashboard Example

I created dashboards for each supported language plus an overall meta-dashboard. It was fairly quick, taking about a day to set up 35 dashboards and about 200 queries.

Luckily the interface is pretty good, because once you have the software set up, that’s where the documentation ends. You can figure out most things with experimentation (trial-and-error), but it would be very helpful to have a few getting started tutorials, or at the least an explanation of how the various visualizations work.

A micro EC2 instance may stumble if you have some large queries (selecting an entire table is a bad idea, don’t do it), or a lot of things refreshing, but it kept up pretty well.