2 psql Basics

Run SQL files, explore a database interactively, and get help from psql.

psql is the official command-line client for PostgreSQL, and it is excellent. Most professionals reach for it before any GUI — it is fast, scriptable, and ships with every PostgreSQL install. You use it two ways: in batch mode (psql -f file.sql) to run a script, or interactively (psql) to type SQL and meta-commands one at a time. This lesson focuses on the basics; a later lesson covers more meta-commands.

What you’ll learn

The two modes of psql: file and interactive
How to identify your session (current user, database, schema)
The difference between SQL statements and psql meta-commands
Where to get help: \? for meta-commands, \h for SQL

Example

Example 2.1 psql Basics

-- List user databases. Equivalent to \l in interactive psql.
SELECT datname
FROM pg_database
WHERE datistemplate = false
ORDER BY datname
LIMIT 5;

-- Identify the session: who am I, where am I, what's on my search_path?
SELECT current_user, current_database(), current_schema();

-- A few server settings worth knowing about
SHOW timezone;
SHOW server_encoding;

pg_database is one of many system catalogs — internal tables PostgreSQL exposes for inspection. The datistemplate = false filter hides template0 and template1, which are the templates new databases are cloned from. current_user, current_database(), and current_schema() are session functions; they answer questions you will ask yourself often when debugging connection or permission issues. SHOW displays runtime parameters — these can be changed at the server, database, role, or session level.

When you use psql interactively, you can also type meta-commands (also called backslash commands) that the client interprets locally:

\l         list databases
\c db      connect to database `db`
\dt        list visible tables in the current search_path
\d table   describe a table (columns, types, indexes)
\dn        list schemas
\df        list functions
\du        list roles (users/groups)
\timing    toggle showing query timing
\x         toggle expanded display (great for wide rows)
\?         help for meta-commands
\h SELECT  help for the SELECT command (or any SQL keyword)
\q         quit

Meta-commands are interpreted by the psql client, both interactively and in files read with psql -f. Commands such as \dt send catalog queries to the server; the backslash syntax itself is not server SQL.

Run the example

To run this file:

$ psql -f source/psql-basics.sql postgres
       datname
--------------------
 postgres
(1 row)

 current_user | current_database | current_schema
--------------+------------------+----------------
 your_user    | postgres         | public
(1 row)

 TimeZone
----------
 UTC
(1 row)

 server_encoding
-----------------
 UTF8
(1 row)

Common pitfalls

Meta-commands require the psql client. psql -f executes \dt in a file; a driver or GUI that sends the file directly as SQL will not interpret it.
Confusing current_user and session_user: when a SET ROLE is in effect, current_user reflects the effective role; session_user is the original login.
Encoding mismatches: if your terminal is not UTF-8 and your database is, you may see garbled text — set client_encoding to match your terminal.

Tip

Add \set PROMPT1 '%n@%/%x%R%# ' to ~/.psqlrc so your prompt shows user, database, and transaction state. Use \e to edit the previous query in your $EDITOR. Use \timing on when you are tuning queries.

Try it

Start an interactive session with psql postgres, run \l, then \dt, then \d pg_database, then \q to quit. Compare what \dt shows in different schemas using \dt pg_catalog.*.

Downloads

SQL file: psql-basics.sql · All lesson files · Setup guide