Commanding PostgreSQL 9.6: New psql Meta-Commands

Among the new features of PostgreSQL 9.6 are new meta-commands that make creating database-based scripts, working with views, and reading errors so much simpler and quicker. So join us as we explore the new powers of psql.

Psql is a powerful command line tool that enables not just SQL command editing and execution but file import and export, database discovery and more through the meta-commands. If you want to get the best out of PostgreSQL, psql is an essential power-tool, and the new meta-commands add more blades to its cutting edges.

Some of the new meta-commands that come with PostgreSQL 9.6 are \crosstabview,\gexec, \sv, \ev, and \errverbose. We covered \crosstabview in a previous article and now we'll introduce you to the others.

To demonstrate how these commands work, let's first generate some data. To do that, we'll create a table of 25 customers with a customer id, name, and city column and then insert some customers into that table.

\gexec

With \gexec we can generate multiple queries and have psql execute them for us. To see how this works, let's imagine that we need to divide our "customers" table into separate tables by city in order to shard them later on. On a small data set it might be relatively easy to select records and create new tables according to a distinct data field. However, for a large data set this could be a very daunting task, especially if we needed to run multiple queries and execute them individually.

To create new tables for our customers, we'll have to find all the cities that they share. Using DISTINCT, we could find the distinct cities, but we'd still have to use CREATE TABLE for each one. With format() function, we can generate a list of CREATE TABLE commands for each city like:

Next, we'll have to insert each customer into a table based on their city. To do that, we'll create a similar SQL query as above also using the format() function. This time, we'll use the INSERT INTO command to select each customer by their city name, which we'll use as the table name for the insert. Also, we'll make sure to select their name using the WHERE clause so that we don't accidentally place customers into the wrong table.

SELECT
format('INSERT INTO city_%s SELECT * FROM customers WHERE name = $$%s$$;', c.city, c.name)
FROM customers AS c;

Again, executing this query we can view each INSERT INTO command before they're executed using \gexec.

format
-----------------------------------------------------------------------------------------
INSERT INTO city_Cambridge SELECT * FROM customers WHERE name = $$Roger Einstein$$;
INSERT INTO city_Boston SELECT * FROM customers WHERE name = $$Nick Newton$$;
INSERT INTO city_Oxford SELECT * FROM customers WHERE name = $$Michele Curie$$;
INSERT INTO city_Portland SELECT * FROM customers WHERE name = $$Michael Hawking$$;
...

Now, subtituting the semicolon for \gexec, each query will be run in sequence:

After that's finished, we can inspect our tables to make sure that all the customers have been added correctly.

\sv and \ev

The view meta-commands \sv (show view) and \ev (edit view) have also been added to psql. Historically, since version 9.1, we've had the \sf and \ef commands that allow us to view and edit PostgreSQL functions. Now in 9.6, the command has been extended to views.

The view meta-commands are particularly useful when figuring out how your views are constructed and editing them right from the terminal, instead of having to drop and recreate them. Using the view commands are pretty straightforward, so let's take a look at how \sf and \ef work by using the five city tables we created above.

First, we'll create a view that combines the customers located in the cities of Boston, Princeton, Cambridge, and Portland and call it "us_cities".

and adding a + to it (\sv+ [table_name]) will give us a more verbose version of this by appending line numbers next to each line of the view definition.

If any updates need to be made to the view, we can do that using \ev [view_name], which will open an edit buffer in your default text editor. Suppose we want to take out Cambridge because the city refers to Cambridge in the UK not Cambridge, Massachusetts. We'd write \ev us_cities, and remove the customers from Cambridge:

After editing, save it. It will close and take you back into psql. From there, type either a semicolon or \g to execute the changes to the view. You'll get the CREATE VIEW acknowledgment that the view has been successfully replaced.

Editing a view doesn't just mean adding or subtracting tables or columns, we can also create new views by simply changing the name. For example, if we wanted to edit the name of the "us_cities" view to "us_customers" to reflect what our view really comprises, we'll open the editor again using \ev us_cities, then change the name, save it, then again when back in psql type a semicolon or \g to create the new view:

Once you've received the CREATE VIEW acknowledgment, you'll notice that you still have the old "us_cities" view and the new "us_customers" view. That's because psql uses the CREATE OR REPLACE VIEW command which looks for a view called "us_customers". Since it the view doesn't exist a new view is created. From there, you can drop any views that you don't need.

\errverbose

Now we'll turn to the last meta-command we'll cover, \errverbose. If you want to see a verbose version of the most recent server error message, we now can use \errverbose. Perviously, if you wanted to view an expanded version of an error message, you'd have to set psql's [VERBOSITY] variable to verbose. The drawback of that is it'd give you a verbose error report every time an error occured, which might not be what you're looking for.

Now, you don't have to fiddle with psql settings with the introduction of \errverbose. Running this command, you'll get a verbose version of the last error message produced in psql. The output that you'll get varies depending on the error. However, what you'll always get are the PostgreSQL error code, as well as the function, file name, and line number in the source code that triggered the error.

To show you how this works, we can try it out by editing the "us_customer" view that we created above. We'll try to rename the column name to customer_name. We'll type \ev us_customers to edit the view like:

After saving the view in the editor, trying to execute the new view in psql will give us an error:

ERROR: cannot change name of view column "name" to "customer_name"

This error is due to the way views are set up in the source code. This error code doesn't give us much detail except that we can't change name to customer_name. To get a better understanding of why we can't change the name, we'll run \errverbose which gives us:

The error code that's returned (42P16) corresponds to an invalid table definition; or an invalid view. Below that, we are given the function checkViewTupleDesc, file name view.c, and line number in the PostgreSQL source code #299 which triggered the error. This function validates views to make sure that the old view matches with the new view, with the exception of changed column attribute names.

Just to see another error, let's attempt to add another record to the "city_portland" table. This time, we'll try to add a record with an id that already exists. We have five customers in this table:

This message provides us with the error code 23505 that corresponds to a unique violation error. We're given the same information in "DETAIL" as the previous error, but it also includes the schema, table, and constraint names, which might be useful if we have several schemas involved. Finally, the location of the error is provided again, producing the function, file name, and the line number in the source code that triggered the error.

Summary

The meta-commands that come with psql are very useful for developers who want to spend more time analyzing their data than writing SQL commands or changing their PostgreSQL settings. While it might not offer the bling that GUIs have, the tradeoff is that you have access to these meta-commands. With PostgreSQL 10 right around the corner, psql will get even more powerful with the addition of even more meta-commands such as nestable conditional block commands \if, \elif, \else, and \endif. So, to start taking advantage of PostgreSQL 9.6, look nowhere else except your terminal, and start using psql.

If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

Abdullah Alger is a former University lecturer who likes to dig into code, show people how to use and abuse technology, talk about GIS, and fish when the conditions are right. Coffee is in his DNA. Love this article? Head over to Abdullah Alger’s author page to keep reading.