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
\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
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 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
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);
\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
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
\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
\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
\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.
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
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
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
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.
\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.
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
\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 firstname.lastname@example.org. We're happy to hear from you.
attribution S W