General
If there’s only one thing you can takeaway, is the below 2 commands.
\?: Show help for backslash commands.\h [name]: Show help on SQL commands.
Knowing the frst command, will allow you to quickly find the other commands
you’ll need. The second command will basically show the documentation of any
SQL commands. For example, \h copy will return the following:
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
[ WHERE condition ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
# ...
Formatting:
Sometimes, you might want to quickly know how long does a query take to return
or happen to query a row with a lot of data. The following psql meta commands
will help with the issues above:
\timing: Turn on timing of commands (including SQL commands).\x: Expand output. Useful for data with lots of columns or very long.
Here’s, how turning on \timing will looks like:
example=# select count(*) from contents;
count
-------
66
(1 row)
Time: 3.875 ms
For \x, each column of each row will be printed on a separate line as follow:
example=# select * from tags;
-[ RECORD 1 ]--------------------
id | 1
name | html
inserted_at | 2022-09-09 07:06:47
updated_at | 2022-09-09 07:06:47
-[ RECORD 2 ]--------------------
id | 2
name | css
inserted_at | 2022-09-09 07:06:47
updated_at | 2022-09-09 07:06:47
-[ RECORD 3 ]--------------------
id | 3
name | elixir
inserted_at | 2022-09-09 07:06:47
updated_at | 2022-09-09 07:06:47
Info:
Apart from formatting and some general commands, there are quite a few useful
psql meta commands that is useful to get a better overview of our database
schemas, indexes, views and users:
\d[+]: List all tables, views and sequences.\dt[+]: List all tables.\di[+]: List all indexes.\dT[+]: List all data types.\dx: List all extensions.\du: List all roles (users).
For example, to know what are the tables available in your database, you can
just use the \dt command:
example# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+----------
public | admin_users | table | postgres
public | contents | table | postgres
public | contents_tags | table | postgres
public | schema_migrations | table | postgres
public | tags | table | postgres
You could get more information about a particular table by adding the table
at the end. For example, \d tags:
Table "public.tags"
Column | Type | ... | Nullable | Default
-------------+--------------------------------+-----+----------+----------
id | bigint | | not null | ...
name | character varying(255) | | |
inserted_at | timestamp(0) without time zone | | not null |
updated_at | timestamp(0) without time zone | | not null |
Indexes:
"tags_pkey" PRIMARY KEY, btree (id)
"tags_name_index" UNIQUE, btree (name)
Referenced by:
TABLE "contents_tags" CONSTRAINT "contents_tags_tag_id_fkey" FOREIGN KEY (tag_id) REFERENCES tags(id)
Most options also accept an optional + behind to show extra information. For
example \dt+ will include information like persistence, access method and
size of each table.
Others:
\copy: Copying data from a tables to a file, and vice versa.\! [command]: Excute command in shell. If no command is provided, it start an interactive shell.
\copy can come in handy when you need to quickly export the data of a table.
Here’s how you can quickly export all the rows of a table to a CSV file:
\copy table_name to 'table.csv';
So far, I have not found any usage for \! [command], but I have read
about how it come in handy for operators.
Anyway, that’s all for now, hope you learn something useful!
(575 words)