Never seen Postgres from the command line before? Start here! We’ve loaded a sample database in for you and you’re a superuser.
Let’s cover the basics
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
\h
Get help on a specific SQL command
\h create database
List all the databases
\l
Want to see the root server directory?
\! ls -la
Describe a table
\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.
\d+ weather
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)
Pager
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.
- [Spacebar] will let you page through results.
- down arrow gives you the option to show the rest or go to the bottom
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.
Queries
Try a quick query
SELECT event_type FROM weather LIMIT 20;
Query buffer
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.
\r
Now try a multi-line query.
SELECT DISTINCT(event_type)
FROM weather
WHERE state = 'HAWAII';
Timing
You can have psql run a timer for your queries by setting
\timing
try a query again and you’ll see how long it took to run
SELECT DISTINCT(event_type)
FROM weather LIMIT 40;
SQL
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;
Quit
\q