psql is the PostgreSQL interactive terminal. psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks. These are some example of psql option command:
psql -c : run only single command
example
-bash-4.2$ psql -c "select now();"
now
-------------------------------
2021-08-31 09:41:36.006457+07
(1 row)
psql -d : database name to connect to (default is postgres)
example
connect to database imam_test
-bash-4.2$ psql -d imam_test
psql (12.7)
Type "help" for help.
imam_test=#
connect to database template1
-bash-4.2$ psql -d template1
psql (12.7)
Type "help" for help.
template1=#
psql -f : execute commands from file, then exit
example
-bash-4.2$ psql -f testpsqlf.sql
BEGIN
INSERT 0 1
id | a
----+-----------------
4 |
5 |
6 |
7 |
8 |
9 |
10 |
1 | suzuki
11 | Trumph
2 | honda
3 | yamaha
12 | Hurley Davodson
(12 rows)
ROLLBACK
Note : testpsql.sql contain a transaction which insert 1 data (12, hurley davodson), select everything from table test2, and rollback the transaction.
Also ‘psql -f … ’ is really handy when restoring database from pg_dump function.
psql -l : list available databases, then exit
example
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileg
es
--------------+----------+----------+-------------+-------------+------------------
-----
imam_test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
max | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgis_test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sqlite_test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/post
gres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/post
gres
(7 rows)
psql -V : output version information, then exit
example
-bash-4.2$ /usr/pgsql-12/bin/psql -V
psql (PostgreSQL) 12.7
psql -E : display queries that internal commands generate
example
not using ‘-E’
-bash-4.2$ psql
psql (12.7)
Type "help" for help.
imam_test=# \d test1;
Table "public.test1"
Column | Type | Collation | Nullable | Default
---------+-----------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('test1_id_seq'::regclass)
time | timestamp without time zone | | |
content | text | | |
id2 | integer | | |
Adding -E parameter to psql
-bash-4.2$ psql -E
psql (12.7)
Type "help" for help.
imam_test=# \d test1;
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(test1)$' COLLATE pg_catalog.default
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = '118225';
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
a.attidentity,
a.attgenerated
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '118225' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************
********* QUERY **********
SELECT pol.polname, pol.polpermissive,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '118225' ORDER BY 1;
**************************
********* QUERY **********
SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname,
(SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')
FROM pg_catalog.unnest(stxkeys) s(attnum)
JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND
a.attnum = s.attnum AND NOT attisdropped)) AS columns,
'd' = any(stxkind) AS ndist_enabled,
'f' = any(stxkind) AS deps_enabled,
'm' = any(stxkind) AS mcv_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '118225'
ORDER BY 1;
**************************
********* QUERY **********
SELECT pubname
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid
WHERE pr.prrelid = '118225'
UNION ALL
SELECT pubname
FROM pg_catalog.pg_publication p
WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('118225')
ORDER BY 1;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '118225' AND c.relkind != 'p' ORDER BY inhseqno;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relkind FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '118225' ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
**************************
Table "public.test1"
Column | Type | Collation | Nullable | Default
---------+-----------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('test1_id_seq'::regclass)
time | timestamp without time zone | | |
content | text | | |
id2 | integer | | |
psql -o : send query results to file (or | pipe)
example
-bash-4.2$ psql -o output1.txt
psql (12.7)
Type "help" for help.
imam_test=# select now();
imam_test=# select * from test2;
imam_test=# \q
observe, there is no output after we ran a query, it is because the output is directly send to output1.txt file. If we open that file using vim, it will look like this:
now
-------------------------------
2021-09-01 10:20:17.544837+07
(1 row)
id | a
----+--------
4 |
5 |
6 |
7 |
8 |
9 |
10 |
1 | suzuki
11 | Trumph
2 | honda
3 | yamaha
(11 rows)
psql -A : unaligned table output mode
example
-bash-4.2$ psql -A
psql (12.7)
Type "help" for help.
imam_test=# select * from test2;
id|a
4|
5|
6|
7|
8|
9|
10|
1|suzuki
11|Trumph
2|honda
3|yamaha
(11 rows)
psql -H : HTML table output mode
example
-bash-4.2$ psql -H
psql (12.7)
Type "help" for help.
imam_test=# select * from test2;
<table border="1">
<tr>
<th align="center">id</th>
<th align="center">a</th>
</tr>
<tr valign="top">
<td align="right">4</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="right">5</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="right">6</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="right">7</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="right">8</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="right">9</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="right">10</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">suzuki</td>
</tr>
<tr valign="top">
<td align="right">11</td>
<td align="left">Trumph</td>
</tr>
<tr valign="top">
<td align="right">2</td>
<td align="left">honda</td>
</tr>
<tr valign="top">
<td align="right">3</td>
<td align="left">yamaha</td>
</tr>
</table>
<p>(11 rows)<br />
</p>
psql -t : print rows only
example
-bash-4.2$ psql -t
psql (12.7)
Type "help" for help.
imam_test=# select * from test2;
4 |
5 |
6 |
7 |
8 |
9 |
10 |
1 | suzuki
11 | Trumph
2 | honda
3 | yamaha
psql -h : database server host or socket directory
psql -p : database server port
psql -U : database user name
example
-bash-4.2$ /usr/pgsql-12/bin/psql -h localhost -p 7777 -U postgres
psql (12.7)
Type "help" for help.
postgres=#
another example combining multiple psql’s option:
-bash-4.2$ psql -h localhost -p 7777 -U postgres -d imam_test -t -A -c "SELECT * FROM test2;" -o output1.txt
line command above is connect to database imam_test on localhost, port 7777, execute one query (SELECT * FROM test2) return unaligned rows without header and save that on output1.txt file.
Leave a Reply