psql basics

Let’s cover the basics

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:

 Copy
\?

The results will be paged; you can use space to go through the list.

List of possible SQL commands

 Copy
\h

Get help on a specific SQL command

 Copy
\h create database

List all the databases

 Copy
\l

Want to see the root server directory?

 Copy
\! 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.

 Copy
\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

 Copy
\pset pager 0

\pset pager 1 turns it back on.

Queries

Try a quick query

 Copy
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.

 Copy
\r

Now try a multi-line query.

 Copy
SELECT DISTINCT(event_type) 
FROM weather 
WHERE state = 'HAWAII';

Timing

You can have psql run a timer for your queries by setting

 Copy
\timing

try a query again and you’ll see how long it took to run

 Copy
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.

 Copy
UPDATE weather
SET magnitude = 40
WHERE episode_id = 57676;

Quit

 Copy
\q