pgAdmin Features: 5 Cool Tips to Try

pgAdmin, the free, open-source GUI for the PostgreSQL database, has come a long way since version 1.0 released in September 2016. While writing a new edition of Practical SQL, five cool pgAdmin features and tips caught my eye. They include theme options, a Geometry Viewer, a JSON editor, SQL formatter, and import/export wizard.

Let’s take a look at each. For this description, I’m referencing pgAdmin version 6.0, released in October 2021.

Dark Mode

For those who wish to reduce screen glare, pgAdmin features the addition of a dark theme as of version 4.15. To access this pgAdmin feature, select File > Preferences > Miscellaneous > Themes, then choose Dark. A high contrast theme is also available.

pgAdmin features a dark mode

Geometry Viewer

If you’re a PostGIS user, one of the handiest pgAdmin features is its built-in Geometry Viewer. Instead of exporting data to a spatial analysis product like QGIS, you can get a quick view of it right in pgAdmin.

Using the Geometry Viewer is straightforward. Run a query that contains a spatial data column in the output. Then click the eye icon in the column header to open the Geometry Viewer and show your shapes plotted on an OpenStreetMap layer:

pgAdmin features a geometry viewer

The example, from the “Working with JSON Data” chapter in Practical SQL (code here), shows a selection of earthquakes near Tulsa, Oklahoma. As long as your data is set with a spatial reference identifier of 4326 (WGS 84), the features will display on the OSM layer; otherwise they’ll show on a blank canvas.

The map is interactive. Click any displayed feature to show other data from the row in the source query output. It’s handy for quick explorations.

SQL Formatting

There’s no standard way to format SQL, and every coder tends to have their own style. Uppercase, lowercase, indents — it generally comes down to personal preference. Still, for the sake of readability, it helps to have a consistent style. A pgAdmin feature called “Format SQL” analyzes your code and reformats it according to settings you customize.

To see the options, select File > Preferences > Query Tool > SQL formatting. For this example, I chose lowercase identifiers, uppercase keywords, re-indentation, use spaces, and no comma-first notation. Then, I wrote a simple query:

pgAdmin features SQL formatting

Under the editing menu icon, choose the last item, “Format SQL.” With my settings, it produced these results:

pgAdmin's SQL formatting

Keywords have been uppercased and the code aligned into clauses. Nice.

You’ll likely need to play with the settings and try them out on different types of queries to find a style that works for you.

JSON Viewer/Editor

With PostgreSQL’s relatively new support for JSON data, pgAdmin has recently added more robust viewing and editing abilities. Double clicking on a column with data type JSON or JSONB opens a compact viewing/editing tool:

The pgAdmin JSON viewer/editor

The tool includes a JSON formatter, a minify option, and several view modes. Read the documentation for details.

Import/Export Wizard

PostgreSQL users are familiar with the COPY statement for importing and exporting data. Sometimes, though, COPY is impractical — for example, when you’re working with a server in the cloud. That’s because COPY can only access the local file system. You’d have to first transfer a file to the cloud server, then run COPY. That’s not always practical.

Users often get around this limitation by employing the psql command-line utility and its \copy command, which can pipe a file on your computer to the server. pgAdmin’s import/export wizard puts a friendly GUI face on top of \copy, removing the need to remember some of its arcane commands. For example:

The pgAdmin import/export wizard

Right-click a table name in pgAdmin’s object browser and select “Import/Export.” At the top, move the slider to choose which type of operation, then select options and your source file. When you click “OK,” pgAdmin runs \copy in the background to perform the operation.

Wrapping Up

All the pgAdmin features covered here are included in the exercises for the second edition of Practical SQL: A Beginner’s Guide to Storytelling with Data, from No Starch Press. You can check out all the code and data on GitHub.

csvkit: A Swiss Army Knife for Comma-Delimited Files

If you’ve ever stared into the abyss of a big, uncooperative comma-delimited text file, it won’t take long to appreciate the value and potential of csvkit.

csvkit is a Python-based Swiss Army knife of utilities for dealing with, as its documentation says, “the king of tabular file formats.” It lets you examine, fix, slice, transform and otherwise master text-based data files (and not only the comma-delimited variety, as its name implies, but tab-delimited and fixed-width as well). Christopher Groskopf, lead developer on the Knight News Challenge-winning Panda project and recently a member of the Chicago Tribune’s news apps team, is the primary coder and architect, but the code’s hosted on Github and has a growing list of contributors.

As of version 0.3.0, csvkit comprises 11 utilities. The documentation describes them well, so rather than rehash it, here are highlights of three of the utilities I found interesting during a recent test drive:
Continue…

Test Drive: Freebase Gridworks 1.1

Update, 11/10/2010: Since I originally reviewed Freebase Gridworks, it has been acquired by Google. It’s now called Google Refine, and version 2.0 has been released. Original post follows:

——–

Data journalists spend lots of time wrestling dirty data, so when I heard the News Applications team at the Chicago Tribune raving about the data-handling abilities of Freebase Gridworks, my interest was piqued. Anything that can lessen the pain of cleaning data is worth a closer look!

Freebase Gridworks is a Java-based app that runs locally in your web browser. The makers’ pitch describes it best:

… A power tool that allows you to load data, understand it, clean it up, reconcile it internally, augment it with data coming from Freebase, and optionally contribute your data to Freebase for others to use. All in the comfort and privacy of your own computer.

Installation is simple. I chose to load Gridworks on my Windows XP-based work laptop, although you can download Mac and Linux versions from the code page. I was up and running in about five minutes, which included loading a new version of Java. Once running, the opening screen looks like so (click for larger version):

You can open an existing project or create a new one by importing a data file — and Gridworks hints at its utility by providing options to parse delimited or non-delimited files, limit the import to specific rows, etc. For testing, I grabbed the Academic Libraries: 2008 Public Use Data file from the National Center for Education Statistics — a tab-delimited text file of about 4,100 rows.
Continue…