Postgres by Example: psql Basics

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

-- 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 tables in the current schema
\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 do not run on the server — they are interpreted by the psql client. That is why they don't work in a .sql file run via psql -f.

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 in script files: putting \dt in a .sql file does nothing when piped through -f. Use the SQL equivalent (a pg_catalog or information_schema query) instead.
• 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@%/%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.*.

Source: psql-basics.sql

Next: SELECT Basics

Home: Postgres by Example