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 |
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014