In this brief video, I run through the basics of running SQL queries using pgAdmin, a graphical interface for the PostgreSQL database. To learn more about pgAdmin’s features, including how to install it on various platforms, please check out my book, Practical SQL.
Today’s the day. After two years of early mornings, late nights, and working weekends, the 2nd Edition of Practical SQL: A Beginner’s Guide to Storytelling with Data has been released.
The folks at No Starch Press kindly sent me a box of copies. It’s a strange, wonderful feeling to hold a physical copy of an object you created on your laptop. Here it is, live:
Thanks to all at No Starch Press as well as technical reviewer Stephen Frost of Crunchy Data for all the effort that went into making this a reality.
For more on the book, please visit practicalsql.com.
The European website LearnSQL.com recently published an interview with me in advance of the upcoming release of the 2nd Edition of Practical SQL. Journalist-turned-programmer/trainer Jakub Romanowski asked questions ranging from what’s new in the 2nd Edition to why the book uses PostgreSQL.
Along the way, I share some of my philosophy about writing, data, and the value of learning SQL.
Here’s an excerpt:
Most SQL books are pretty boring, like a different form of database documentation with some unrealistic examples. You’ve made learning SQL syntax interesting. What’s your secret?
Thanks for saying that! I write from the perspective of a teacher in a classroom. How would I keep a group of students engaged each week for a whole semester? To me, the answer is to have the book focus on real-life data analysis, with the joy of discovery and sometimes the pain of messy data getting in the way. So, I use real data and try to help readers discover insights about the data while they’re learning. And, as I’m sure you know, data can sometimes be lacking in terms of quality. We learn about that too, and how to deal with it. That’s very real.
You can read the full interview here. For more on Practical SQL, please visit practicalsql.com.
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.
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.
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:
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.
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:
Under the editing menu icon, choose the last item, “Format SQL.” With my settings, it produced these results:
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.
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 tool includes a JSON formatter, a minify option, and several view modes. Read the documentation for details.
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:
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.
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.
YouTuber Python Programmer recently posted a spot comparing Practical SQL to Learning SQL by Alan Beaulieu. He breaks down the highlights of both books and, at the end, gives one of them the edge.
The second edition of Practical SQL is coming in early in 2022 and is available now in early release from No Starch Press.
The second edition has been thoroughly revised with updated data, additional query techniques, and two new chapters: an expanded set of instructions on for setting up your system plus a chapter on using PostgreSQL with the popular JSON data interchange format.