Never seen Postgres from the command line before? Start here! We’ve loaded a sample database in for you and you’re a superuser.
The first command to know is how to get internal help:
The results will be paged; you can use
space to go through the list.
List of possible SQL commands
Get help on a specific SQL command
\h create database
List all the databases
Want to see the root server directory?
\! ls -la
\d without any parameters will show a list of all tables and objects in the current database. If
+ is appended, you'll also see extended information such as each table's size on disk.
There are more in the
\d set of meta-commands that you can use. Examples of more common ones are
\dn (all schemas),
\dv (all available views),
\du (all users),
\df (all functions),
\dp (table, view, and sequence access privileges)
By default psql comes with a pager that shows you just a snippet of what you’re looking at and you can page through results.
If you just ran the above table description,
\d+ weather and you had to page through lots of results, you can turn off the psql pager with
\pset pager 0
\pset pager 1 turns it back on.
Try a quick query
SELECT event_type FROM weather LIMIT 20;
You can also write queries as multiline entries. These will string together across many lines in
psql until you put a
; at the end of the query.
psql will create what is called a query buffer until you end the query with a
\r will reset your query buffer.
Now try a multi-line query.
SELECT DISTINCT(event_type) FROM weather WHERE state = 'HAWAII';
You can have psql run a timer for your queries by setting
try a query again and you’ll see how long it took to run
SELECT DISTINCT(event_type) FROM weather LIMIT 40;
psql will allow you to update DDL, tables, column, and data using basic sql. You can run insert, delete, update statements from this command line. Try a quick update statement.
UPDATE weather SET magnitude = 40 WHERE episode_id = 57676;