Having access to the psql command-line tool is essential for any developers or DBAs that are actively working with and connecting to PostgreSQL databases. In our first article, we discussed the brief history of psql and demonstrated how to install it on your platform of choice and connect to a PostgreSQL database.
In this article we’ll get you up and running with all of the essential things you need to know to start on your journey to becoming a psql power-user. From basic command syntax to the most common (and helpful) meta-commands, it’s all covered throughout the rest of the article.
To get the most value out of this content, you should have psql installed and connected to a PostgreSQL database so that you can try the commands as you read.
Essential Usability Tips
The psql utility is packed with many helpful commands to help you explore and manage your database. Any slash command (\) is used to specify a meta-command that will typically run the necessary SQL queries in the background and return the results in a readable format.
First, let’s look at a few tips about how psql works as a command line tool. Knowing how to enable certain features or find help for each meta-command will go a long way in helping you to be a successful user of the tool.
The Semicolon is Required
PostgreSQL adheres to the ANSI SQL standard which specifies the semicolon for statement termination. In psql, you signal the end of a SQL statement by adding the semicolon and pressing enter.
If you don’t add a semicolon, psql will simply provide a new line for you to keep typing as shown below.
pagila_dev=# select * from film pagila_dev-# ;
The query will not be executed until the semicolon is added.
Command History and Paging
psql is a terminal application and as such it keeps a history of the queries and commands that you have executed. Therefore, you can use the up and down arrows on your keyboard to page through previous commands and statements. This is helpful when you want to run a statement multiple times but change a filter each time.
The history is stored in a file on your local client which means it will be different from computer to computer. You can also configure psql to create a different history file per database or server using the HISTFILE variable in the .psqlrc file.
To see a list of the commands that be been run, use the \s command.
postgres=# \s \c pagila_ \c pagila_dev \df \x \df select * from film ; \s
Autocompletion
psql supports tab-based autocompletion. For many commands, you can use the TAB key to trigger autocompletion or suggestion. If you want to list a table or connect to a different database, begin with the meta-command and then begin to type the object name and press TAB. If there would be more than one match, psql will provide possible matches similar to a Linux terminal.
Expanded Results Table Formatting
One of the first helpful hints I learned early on with psql was that there are two modes for displaying both query and meta-command results. Normally, psql will do the hard work of formatting rows and columns of data in a monospaced font with the right amount of padding for everything to line up – within reason. When terminal wrapping takes place, however, it can be challenging to figure out which data goes with which column.
In these cases, you can print the results in an “expanded” format which is essentially a crosstab of each row showing the column headings on the left and each value on the right. In this format you can page through results one at a time.
You can toggle expanded mode on and off by using the \x command. Below we show a listing of objects in the database, first in the normal table mode and then with expanded mode turned on.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | example_tbl | table | postgres
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
(3 rows)
postgres=# \x
Expanded display is on.
postgres=# \d
List of relations
-[ RECORD 1 ]-------------------
Schema | public
Name | example_tbl
Type | table
Owner | postgres
-[ RECORD 2 ]-------------------
Schema | public
Name | pg_stat_statements
Type | view
Owner | postgres
-[ RECORD 3 ]-------------------
Schema | public
Name | pg_stat_statements_info
Type | view
Owner | postgres
Quitting the psql Session
Finally, I’d hate for you to feel like you can’t get out of the interactive shell. Any time you want to quit your current psql session, simply use the \q meta-command to get back to your terminal prompt.
postgres=# \q ryan@redgate-laptop:~$
System Objects and Additional Details
As we review some of the essential commands below you will notice a pattern where many of the commands have multiple forms. Adding a capital S to the end of many commands will include system objects in the output. Also, including a + at the end of the command will return additional details, analogous to a an advanced output mode.
For example, the two commands below both list the tables, views, and sequences of a database, but the second form provides additional details.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | example_tbl | table | postgres
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
...
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------------+-------+----------+-------------+---------------+------------+-------------
public | example_tbl | table | postgres | permanent | heap | 8192 bytes |
public | pg_stat_statements | view | postgres | permanent | | 0 bytes |
public | pg_stat_statements_info | view | postgres | permanent | | 0 bytes |
postgres=# \dS+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
------------+---------------------------------+-------+----------+-------------+---------------+------------+-------------
pg_catalog | pg_aggregate | table | postgres | permanent | heap | 56 kB |
pg_catalog | pg_am | table | postgres | permanent | heap | 40 kB |
pg_catalog | pg_amop | table | postgres | permanent | heap | 88 kB |
pg_catalog | pg_amproc | table | postgres | permanent | heap | 72 kB |
pg_catalog | pg_attrdef | table | postgres | permanent | heap | 8192 bytes |
...
public | example_tbl | table | postgres | permanent | heap | 8192 bytes |
public | pg_stat_statements | view | postgres | permanent | | 0 bytes |
public | pg_stat_statements_info | view | postgres | permanent | | 0 bytes |
Getting help
Although the output can be overwhelming, you can always view the psql help file that will show you an expansive list of meta-command patterns. Notice that the output is grouped into categories.
postgres=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display result in crosstab
\errverbose show most recent error message at maximum verbosity
\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);
\g with no arguments is equivalent to a semicolon
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
...
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\dA[+] [PATTERN] list access methods
…
Essential Meta-commands
In my experience with PostgreSQL over the last few years, particularly coming from many years of SQL Server work, learning how to effectively use basic commands in psql has proven to be useful and time saving. Below are the initial set of commands that will help you navigate the PostgreSQL cluster and databases.
There are certainly many more commands than these, but I believe these are the high-level set that you should learn at the outset.
Connecting to a Different Database
The \c command will connect to a new database on the same server. If you connected to the server with the -W switch, then every time you connect to a different database, you will be prompted for the password again, which is almost always a nuisance so I don’t recommend it.
postgres=# \c flywaytest psql (15.0 (Ubuntu 15.0-1.pgdg20.04+1), server 15.1 (Debian 15.1-1.pgdg110+1)) You are now connected to database "flywaytest" as user "postgres". flywaytest=#
Notice that the default psql prompt shows the database name of the current connection.
List All Databases
The \l command will list any databases in the current server along with the specified access privileges. Seeing that the database exists in this list does not imply that the current role can connect (\c) to the database. Adding the + will provide additional information including database size.
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-------------------+----------+----------+------------+------------+------------+-----------------+-----------------------
advent_of_code | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | postgres=CTc/postgres+
| | | | | | | rptusr=c/postgres
bulk_example | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
flywaytest | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
flywaytest_shadow | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
Display Object Details
There are many \d commands in psql. Although most commands aren’t given specific names, most folks in the community refer to this as either the “describe”, “display”, or “details” command. Much like a CLI that allows you to get help for each command by typing something like “help [command]”, psql uses the \d command almost like that.
Most of the \d commands will provide additional details by adding the + to the end. I’ll only show a few examples of that below, but feel free to try it with any of these commands to get more detail.
Display table, view, and sequence objects
The default action for the describe command is to list all tables, views, and sequences in the current database. We showed examples of the detailed output above when discussing the + operator.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | example_tbl | table | postgres
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
Describe table, view, or sequence details
You can name a specific object to get additional information. Using the + operator will also list constraints and indexes of a table.
postgres=# \d+ example_tbl
Table "public.example_tbl"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
notes | text | | | | extended | | |
Indexes:
"idx_example_notes" btree (notes)
Access method: heap
Display Specific Object Types
As you might expect, the psql developers provided specific commands for listing individual object types. Adding different letters after the \d command will list only that type of object. All lists support additional detail using the +.
- E = foreign table (provided by one of many foreign data wrappers)
- i = indexes
- m = materialized views
- s = sequences
- t = tables
- v = views
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | example_tbl | table | postgres
Display Roles and Members
Roles are used for connecting to PostgreSQL, assigning database privileges, and specifying object ownership. See our series of posts on PostgreSQL security for more details about Roles.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+---------------
dev1 | | {devgrp}
dev2 | | {devgrp}
devgrp | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
read_only | Cannot login | {pg_read_all_data}
rptusr | | {}
Display Installed Extensions
Extensions are one of the more powerful features of PostgreSQL. Knowing what extensions are installed in a specific database helps you maintain better visibility into what features your application may be relying upon.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
tablefunc | 1.0 | public | functions that manipulate whole tables, including crosstab
Display Schemas
Often, your application database will have multiple schemas depending on the design and access requirements of your database. By default, psql will only show you the schemas that are part of your search_path, which for most users is just the public schema by default. If you want to see what other schemas are available in your database, \dn will show you user-defined schemas only.
postgres=# \dn
List of schemas
Name | Owner
----------+-------------------
accounts | postgres
public | pg_database_owner
Display User-defined Functions
PostgreSQL is a function-heavy database. If you come from another database like SQL Server, this may feel a bit counterintuitive because a lot of conversation and training talks about the pitfalls of using functions, particularly in regards to query performance. That said, being able to quickly list the various kinds of functions that are currently in the database, including the input types, is very helpful.
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------------------------+------------------+--------------------------------------------------------------------+------
public | _group_concat | text | text, text | func
public | film_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | func
public | film_not_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | func
public | get_customer_balance | numeric | p_customer_id integer, p_effective_date timestamp with time zone | func
public | inventory_held_by_customer | integer | p_inventory_id integer | func
public | inventory_in_stock | boolean | p_inventory_id integer | func
public | last_day | date | timestamp with time zone | func
public | rewards_report | SETOF customer | min_monthly_purchases integer, min_dollar_amount_purchased numeric | func
Editing SQL Outside of the Terminal
Most of the time psql is being used as an interactive terminal. Sometimes, however, it’s useful to open a query in an editor for larger changes and iterations.
Using the \e command will open the current query buffer (or the most recently executed command) into the editor. Upon exiting the editor, if the SQL query is complete (terminated with a semicolon), it will be executed immediately.
Alternatively, adding a filename after the \e command will open that file for editing and then execute the query if it is complete. (eg. \e my_query.sql)
Finally, you can view the code for a function or view in an external editor by using a specific version of the edit command.
\ef {function name}
\ev {view name}
Show Hidden Meta-command SQL
One final thing to know as you learn more about the psql interactive shell is that it is possible to see the SQL queries that are being run for each meta-command that you run. This can be a very helpful way to learn more about the catalog tables that help run your PostgreSQL instance. Fair warning, there are often multiple SQL queries being executed to get the desired output, so it’s often helpful to only enable it for short periods as you’re trying to learn about something specific.
There are two ways to enable this mode in psql.
Option 1: psql -E connection parameter
This will enable hidden queries (which means they are displayed) for the duration of your psql connection.
psql -E postgresql://[username]:[password]@[hostname]:[port]/[database name]
Option 2: \set meta-command
If you are already connected to a database, you can set the variable which show hidden queries at will.
postgres=# \set ECHO_HIDDEN true
Conclusion
Knowing how to install and use the psql command line tool is an essential skill when using PostgreSQL. Because there is no fully standardized IDE, easily querying details about your database from the catalog tables can be challenging at times, especially if you are new to PostgreSQL. Learning how the basic meta-commands work can dramatically improve your development and administration tasks.
The post PostgreSQL Basics: Essential psql Tips and Tricks appeared first on Simple Talk.
from Simple Talk https://ift.tt/alHYjD4
via
No comments:
Post a Comment