Commanding PostgreSQL 9.6: New psql Meta-Commands
PublishedAmong 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.
CREATE TABLE customers (
customer_id SERIAL NOT NULL PRIMARY KEY,
name TEXT,
city TEXT
);
INSERT INTO customers (name, city) VALUES
('Roger Einstein', 'Cambridge'),
('Nick Newton', 'Boston'),
('Michele Curie', 'Oxford'),
('Michael Hawking', 'Portland'),
('Ned Copernicus', 'Portland'),
('Neil Galilei', 'Boston'),
('David Tesla', 'Princeton'),
('John Edison', 'Cambridge'),
('Phil Darwin', 'Boston'),
('Fred Nobel', 'Portland'),
('Anthony Fermi', 'Boston'),
('Howard Bell', 'Princeton'),
('Josh Kepler', 'Princeton'),
('Jessica Lovelace', 'Cambridge'),
('Peter Hubble', 'Oxford'),
('Wilson Sagan', 'Portland'),
('Paul Boyle', 'Cambridge'),
('Jonas Hahn', 'Oxford'),
('Tom Hooke', 'Boston'),
('Omar Sina', 'Princeton'),
('Adam Al-Khawarizmi', 'Cambridge'),
('Sara Faraday', 'Princeton'),
('Ken Mendel', 'Cambridge'),
('Dana Franklin', 'Portland'),
('Maria Rutherford', 'Oxford');
\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:
SELECT
format('CREATE TABLE city_%s (customer_id INT PRIMARY KEY, name TEXT, city TEXT);', c.city)
FROM (SELECT DISTINCT city FROM customers) AS c;
This will generate a table of CREATE TABLE commands from the number of DISTINCT cities. Each of these commands will be executed in order, by row once we add \gexec.
format
----------------------------------------------------------------------------------
CREATE TABLE city_Princeton (customer_id INT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE city_Oxford (customer_id INT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE city_Portland (customer_id INT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE city_Cambridge (customer_id INT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE city_Boston (customer_id INT PRIMARY KEY, name TEXT, city TEXT);
Substituting \gexec for the semicolon at the end of the SQL statement tells psql to execute each of these SQL commands where we'll get:
SELECT format('CREATE TABLE city_%s (customer_id INT PRIMARY KEY, name TEXT, city TEXT);', c.city) FROM (SELECT DISTINCT city FROM customers) AS c \gexec
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
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:
SELECT format('INSERT INTO city_%s SELECT * FROM customers WHERE name = $$%s$$;', c.city, c.name) FROM customers AS c \gexec
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
...
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".
CREATE OR REPLACE VIEW us_cities AS (
SELECT
*
FROM
city_boston
UNION
SELECT
*
FROM
city_princeton
UNION
SELECT
*
FROM
city_cambridge
UNION
SELECT
*
FROM
city_portland
);
This will produce a table containing ten customers with their customer ids and city locations in no particular order:
customer_id | name | city
-------------+--------------------+-----------
16 | Wilson Sagan | Portland
4 | Michael Hawking | Portland
21 | Adam Al-Khawarizmi | Cambridge
13 | Josh Kepler | Princeton
22 | Sara Faraday | Princeton
12 | Howard Bell | Princeton
2 | Nick Newton | Boston
9 | Phil Darwin | Boston
6 | Neil Galilei | Boston
10 | Fred Nobel | Portland
7 | David Tesla | Princeton
14 | Jessica Lovelace | Cambridge
8 | John Edison | Cambridge
17 | Paul Boyle | Cambridge
11 | Anthony Fermi | Boston
1 | Roger Einstein | Cambridge
23 | Ken Mendel | Cambridge
5 | Ned Copernicus | Portland
19 | Tom Hooke | Boston
24 | Dana Franklin | Portland
20 | Omar Sina | Princeton
With \sv [view_name] we'll get the definition of the view,
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:
ERROR: 42P16: cannot change name of view column "name" to "customer_name"
LOCATION: checkViewTupleDesc, view.c:299
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:
customer_id | name | city
-------------+-----------------+----------
4 | Michael Hawking | Portland
5 | Ned Copernicus | Portland
10 | Fred Nobel | Portland
16 | Wilson Sagan | Portland
24 | Dana Franklin | Portland
If we try to add another customer with a customer_id of 4, it will produce an unique key error since we set that column up as a primary key column.
INSERT INTO city_portland (customer_id, name, city) VALUES (4, 'Brian Bosworth', 'Portland');
ERROR: duplicate key value violates unique constraint "city_portland_pkey"
DETAIL: Key (customer_id)=(4) already exists.
Runnng \errverbose after this error will give us:
ERROR: 23505: duplicate key value violates unique constraint "city_portland_pkey"
DETAIL: Key (customer_id)=(4) already exists.
SCHEMA NAME: public
TABLE NAME: city_portland
CONSTRAINT NAME: city_portland_pkey
LOCATION: _bt_check_unique, nbtinsert.c:433
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.
attribution S W