Tutorial Instructions

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:

\?

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

Loading terminal...

Loading terminal...