Navigating Postgresql with Psql Command Line – 10 Simple Commands

Navigating Postgresql with Psql command line can be frustrating until you have a quick cheat sheet of simple commands used for typical navigation.  Once you get these commands memorized, you can quickly do nearly anything with just a few keystrokes.

Navigating Postgresql with Psql Command Line

10 commands to make your Postgresql / Psql command line life easier:

1.  Change database

Use \c <database_name> to change database names:

mary=> \c marydb
You are now connected to database “marydb” as user “mary”.


2.  Edit Your Command with Default Editor

Use \e to edit the command you are typing in the shell’s default editor:

mary-> select * from my-tabel
mary-> \e

3.  List databases

Use \l to list databases:

mary=> \l
List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
———–+———-+———-+————-+————-+———————–
marydb   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
|          |          |             |             | postgres=CTc/postgres+
|          |          |             |             | mary=CTc/postgres
postgres  | 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/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(4 rows)

4. List tables

Use \d to list database objects:

mary=> \d
List of relations
Schema |                             Name                             |   Type   | Owner
——–+————————————————————–+———-+——-
public | mary_table1                                                  | table    | mary
public | mary_table1_id_seq                                        | sequence | mary(2 rows)mary=>

5.  List permissions

Use \z to list permissions for the logged in user:

mary=> \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
——–+——+——+——————-+————————–
(0 rows)mary=>

6.  exit psql

Use \q to exit psql:

mary=> \q
[mary@www.uptimemadeeasy.com ~]$

7.  Get Postgresql Syntax Help

Use \h to get help on command syntax:

mary=> \h create database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] dbowner ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace ]
[ CONNECTION LIMIT [=] connlimit ] ]mary=>

8.  Use File as Input

Use \i <filename> to use a file as input for commands:

mary=> \i /home/mary/myfile1.sql

9.  Send Psql Output to a File

Use \o to send the Psql output to a file:

mary=> \o /home/mary/myfile1.out

10.  Save Psql Command History

Use \s <filename> to save the psql command history to a file.  If no file is listed, the psql command history will be sent to standard output:

mary=> \s psql-history.sql

 

 

The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *