Postgresql CLI

psql — PostgreSQL Interactive Terminal

psql is PostgreSQL's feature-rich interactive terminal. It lets you write and execute queries, inspect database objects, import/export data, script batch operations, and customize output formatting — all from the command line.

When to Use

Use this skill when the user needs to:

• Connect to a PostgreSQL database from the command line
• Inspect database objects (tables, views, indexes, functions, roles, schemas)
• Import or export data using \copy or COPY
• Write and run SQL scripts with psql meta-commands
• Format query output (CSV, HTML, expanded, unaligned)
• Use pipeline mode, \watch, \crosstabview, or conditional scripting (\if)
• Troubleshoot connection issues, query plans, or lock contention
• Automate database tasks via psql -c or psql -f

Trigger keywords: psql, postgresql cli, backslash commands, meta-commands, \d, \copy, \watch, \crosstabview, pgpass, pg_service.conf, ON_ERROR_STOP, FETCH_COUNT.

Prerequisites

Verify psql is installed and available:

psql --version

If not found, install PostgreSQL client tools:

# Windows (PowerShell) — use the official installer or scoop/chocolatey
winget install PostgreSQL.PostgreSQL
# or
choco install postgresql

# macOS (Homebrew)
brew install libpq
brew link --force libpq

# Ubuntu / Debian
sudo apt install postgresql-client

# CentOS / RHEL
sudo yum install postgresql

# Alpine
apk add postgresql-client

The server package (postgresql) is not required — you only need the client to connect to a remote PostgreSQL instance.

Procedure

1. Connect to a Database

CLI flags (most explicit):

psql -h host -p port -U user -d dbname

Connection URI — WARNING: password in URI is visible in shell history and process listings. Prefer ~/.pgpass for production:

psql "postgresql://user:YOUR_PASSWORD@host:port/dbname"

Environment variables (no flags needed):

export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=mydb
export PGUSER=postgres
# WARNING: PGPASSWORD is visible in process listings (e.g. `ps aux`).
#          Use ~/.pgpass in production instead.
export PGPASSWORD=YOUR_PASSWORD
psql

~/.pgpass file (RECOMMENDED for passwords):

# Format: hostname:port:database:username:password
touch ~/.pgpass && chmod 600 ~/.pgpass
# Then manually edit ~/.pgpass and add entries (avoids password in shell history):
# localhost:5432:mydb:postgres:YOUR_PASSWORD
psql -h localhost -U postgres -d mydb   # no password prompt

Execute and exit (script mode):

psql -f script.sql dbname                        # execute file then exit
psql -c "SELECT 1" dbname                        # run single command then exit
psql -1 -f migration.sql dbname                  # run in single transaction

Service connection (reads from pg_service.conf):

psql service=mydb_prod

Reconnect within a session:

\c dbname                                       # reconnect to different db
\c -reuse-previous=on sslmode=require           # change only sslmode
\c "host=newhost port=5432 dbname=mydb"         # conninfo string

Connection precedence: CLI flags > environment variables > pg_service.conf > defaults. Password precedence: connection string/password flag > PGPASSWORD env > ~/.pgpass. Use ~/.pgpass instead of PGPASSWORD in production — PGPASSWORD is visible in process listings (ps aux).

Key flags: -h host, -p port, -U user, -d database, -w no password prompt, -W force password prompt, -1 single transaction, -f execute file, -c execute command, -t tuples only, -x expanded, -A unaligned, -E echo hidden queries (\d internals), -L log file, -X skip ~/.psqlrc.

On connection failure: interactive mode keeps the previous connection; script mode closes it and all subsequent database commands fail until the next successful \c.

2. Inspect Database Objects (\d family)

Modifiers (append to most \d commands):

• + — extra info (size, description): \dt+, \l+, \du+
• S — include system objects: \dtS, \dfS+
• x — expanded display mode: \dt+x (note: \dx is a different command; x must follow S or +)

Provide a name for details: \d table_name shows columns, types, indexes, constraints, foreign keys.

Pattern matching in \d commands:

• * = any sequence of characters, ? = single character
• . separates schema from object: \dt public.* or \dt my_schema.users
• .. separates database.schema.object: \dt mydb.public.* (db must match current db)
• Double quotes stop case folding and wildcard expansion: \dt "FOO" matches FOO not foo
• $ is matched literally (not regex anchor)
• Regex chars like [0-9] work: \dt user[0-9]* matches user1, user2
• No pattern: shows all objects visible in current search_path (not all objects in DB)
• Use *.* to see all objects in all schemas regardless of visibility

When to load references: Load references/meta-commands-inspection.md when you need the full \d command reference, all modifiers, or pattern matching details for exploring database schema.

3. Execute Queries

When to load references: Load references/meta-commands-core.md when you need exact syntax or behavioral details for any backslash command, query buffer behavior, or argument parsing rules.

4. Import / Export Data

-- Server-side (requires superuser for file access, uses server filesystem)
COPY table TO '/path/file.csv' WITH (FORMAT csv, HEADER true);
COPY table FROM '/path/file.csv' WITH (FORMAT csv, HEADER true);

-- Client-side (runs with client permissions, no superuser needed) — PREFERRED
\copy table TO '/path/file.csv' WITH (FORMAT csv, HEADER true)
\copy table FROM '/path/file.csv' WITH (FORMAT csv, HEADER true)
\copy (SELECT ...) TO '/path/output.csv' WITH (FORMAT csv, HEADER true)

-- Advanced: specific columns, NULL handling, custom delimiter
\copy table (col1, col2) FROM 'data.csv' WITH (FORMAT csv, HEADER true, NULL 'N/A')

\copy is the go-to for day-to-day work — it uses the client's filesystem and permissions, not the server's.

\copy syntax detail:

-- FROM (import): sources are 'filename', program 'command', stdin, pstdin
\copy table FROM 'file.csv' WITH (FORMAT csv, HEADER true) [ WHERE condition ]

-- TO (export): destinations are 'filename', program 'command', stdout, pstdout
\copy table TO 'file.csv' WITH (FORMAT csv, HEADER true)

For \copy ... FROM stdin, data rows continue until a line containing only \. is read or EOF is reached. Use pstdin/pstdout to always read/write psql's actual stdin/stdout regardless of \o setting.

WARNING: The program option executes a shell command. If constructed from user input, it can lead to command injection. Never concatenate untrusted data into the program string.

Tip: \copy takes the entire rest of the line as arguments (no variable interpolation). When you need variable interpolation or multi-line queries, use SQL COPY ... TO STDOUT with \g instead:

-- This allows variable interpolation and multi-line queries
COPY (SELECT * FROM :table WHERE id > :min_id) TO STDOUT WITH (FORMAT csv, HEADER true) \g /tmp/output.csv

5. Format Output

\a                  Toggle aligned/unaligned output
\x                  Toggle expanded display (vertical vs table)
\t                  Toggle tuples only (no headers/footers)
\pset format FORMAT  Set output format: aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped
\pset border N       Set border style (0-2; 3 for latex data-row lines)
\pset null STRING    Display NULL as STRING
\pset pager [off]    Control pager usage
\pset title 'TEXT'   Set table title
\pset recordsep SEP  Set record separator for unaligned mode
\pset fieldsep SEP   Set field separator for unaligned mode (default: |)
\pset footer [on|off] Toggle row count footer
\pset columns N      Set target width for wrapped format
\pset csv_fieldsep C  Set CSV field separator (default: comma)
\pset numericlocale [on|off]  Toggle locale-specific number formatting
\pset linestyle STYLE Set border style: ascii, old-ascii, unicode
\pset pager_min_lines N  Minimum lines before pager activates
\pset xheader_width MODE  Expanded header width: full, column, page, or N (PostgreSQL 17+)
\H                   Toggle HTML output (shortcut)
\C [title]           Set table title (shortcut for \pset title)
\f [string]          Set field separator (shortcut for \pset fieldsep)
\T table_options     Set HTML table attributes (shortcut for \pset tableattr)

When to load references: Load references/meta-commands-formatting.md when the user needs to control output format, use pipeline mode, \watch, or \crosstabview.

6. Scripting & Control Flow

\i filename         Execute file (relative to current working directory)
\ir filename        Execute file (relative to the script being processed)
\o [filename]       Redirect query output to file (or pipe with |cmd)
\o                   Stop output redirection
\qecho TEXT          Output text to redirected output
\echo TEXT           Output text to stdout (-n suppresses trailing newline)
\warn TEXT           Output text to stderr
\! command           Execute shell command
\cd [dir]            Change working directory
\set NAME VALUE      Set psql variable
\unset NAME          Unset psql variable
\prompt [TEXT] NAME  Prompt user for variable value
\getenv psql_var env_var   Copy environment variable into psql variable
\setenv name [value]       Set or unset environment variable
\p                  Print current query buffer
\w filename         Write query buffer to file (or pipe with |cmd)

Conditional execution:

\if EXPR
  \echo 'true branch'
\else
  \echo 'false branch'
\endif

\elif EXPR           Else-if inside \if block

\if and \elif evaluate their argument as a boolean. Valid values (case-insensitive, unambiguous prefix matching): true, false, 1, 0, on, off, yes, no. Expressions that don't evaluate to true/false generate a warning and are treated as false. Variable references in skipped lines are NOT expanded.

Variables in SQL:

• :'varname' — quoted string value (escapes embedded quotes) — SAFER
• :"varname" — double-quoted identifier (properly escapes embedded double quotes)
• :'varname'::type — with cast
• :varname — unquoted — can break SQL — UNSAFE
• :{?varname} — tests existence, expands to TRUE/FALSE

When to load references: Load references/cli-options-and-variables.md when configuring psql startup behavior, writing scripts that depend on variable state, or customizing prompts. Load references/tips-workflows.md when the user asks how to accomplish a specific task with psql.

7. Pipeline Mode (PostgreSQL 14+)

\startpipeline
  SELECT $1 \bind 42 \sendpipeline
  SELECT $1 \bind 100 \sendpipeline
  \getresults
\endpipeline

Pipeline mode sends multiple queries without waiting for each result, reducing round-trip latency. All queries use the extended query protocol.

Pipeline commands:

• \startpipeline — begin pipeline block
• \endpipeline — end pipeline block and process remaining results
• \sendpipeline — append current query buffer to pipeline without waiting
• \syncpipeline — send sync message without ending pipeline
• \flushrequest — request server flush without sync
• \flush — manually push unsent data to server
• \getresults [N] — read pending results (N=0 or omitted means all)

Pipeline limitations:

• COPY is not supported in pipeline mode
• Meta-commands like \g, \gx, \gdesc are not allowed inside a pipeline
• All queries use the extended query protocol
• Use \bind, \bind_named, \parse, \close_prepared, or \sendpipeline within pipelines
• A %P prompt variable shows pipeline status (on, off, or abort)

8. Large Objects

\lo_import filename [comment]   Import file as large object, returns OID
\lo_export loid filename        Export large object to file
\lo_list[x+]                    List all large objects
\lo_unlink loid                 Delete large object

Large object OIDs are persistent references. Always associate a human-readable comment on import. Use \lo_list to find OIDs.

9. Session Management

\c [dbname [user]]  Connect to database (or reconnect)
\conninfo           Display connection info (includes SSL info)
\encoding [ENC]     Set or show client encoding
\password [USER]    Change password (does NOT appear in command history or server log)
\q                   Quit psql. In a script file, only that script is terminated.
\r                   Reset (clear) the query buffer
\e                   Edit query buffer in external editor
\ef [FUNCNAME]       Edit function definition
\ev [VIEWNAME]       Edit view definition
\sf[+] FUNCNAME      Show function definition (read-only)
\sv[+] VIEWNAME      Show view definition (read-only)
\s [FILE]            Print command history (or save to file)
\restrict KEY        Enter restricted mode (only \unrestrict allowed)
\unrestrict KEY      Exit restricted mode
\timing [on|off]     Toggle query execution time display (milliseconds)
\errverbose          Repeat last error at maximum verbosity
\? [topic]           Help: commands, options, or variables
\h [command]         SQL syntax help (use * for all: \h *)
\copyright           Show PostgreSQL copyright

10. \watch Syntax

\watch [i[nterval]=SECONDS] [c[ount]=TIMES] [m[in_rows]=N]

Re-executes the current query buffer at the specified interval (default 2 seconds). Stops after count iterations or when the query returns fewer than min_rows rows. Useful for monitoring live queries or waiting for a condition to become true.

When to Use What

Pitfalls

Security Risks

Variable Interpolation Safety

psql variables are plain text substitution, not parameterized queries:

-- UNSAFE: if :name contains "Robert'); DROP TABLE users;--" it will execute the injection
SELECT * FROM users WHERE name = :'name';

-- SAFER: use \prompt for interactive input (user sees what they typed)
\prompt 'Enter name: ' search_name
SELECT * FROM users WHERE name = :'search_name';

-- SAFEST: use \bind for programmatic parameter passing (truly parameterized)
SELECT * FROM users WHERE name = $1;
\bind 'Robert' \g

The :'varname' form (quoted) is always safer than :varname (unquoted), because unquoted substitution can break SQL syntax or enable injection. Use :"varname" for identifiers (table/column names) — it properly escapes embedded double quotes.

Comment Handling

psql handles two comment styles differently:

• C-style block comments (/* ... */): Passed to the server for processing and removal.
• SQL-standard comments (--): Removed by psql itself, before sending to the server.

This distinction matters when writing scripts that rely on comment behavior — only SQL-standard comments are stripped client-side.

Version Compatibility

psql works best with servers of the same or an older major version. Backslash commands (especially \d family) may fail with newer server versions. When connecting to multiple server versions, use the newest available psql client. The \d commands generally work with servers back to version 9.2.

Connection Failure Behavior

On connection failure: interactive mode keeps the previous connection; script mode closes it and all subsequent database commands fail until the next successful \c. Always check \conninfo before destructive operations.

When to load references: Load references/tips-advanced.md for lock analysis, query plan inspection, EXPLAIN, ECHO_HIDDEN, ON_ERROR_STOP patterns, search_path safety, and common gotchas.

Verification

Verify psql is installed

psql --version
# Expected: psql (PostgreSQL) 16.x (or your installed version)

Verify connection

\conninfo
-- Expected output:
-- You are connected to database "mydb" as user "postgres" on host "localhost" at port "5432".
-- SSL connection (protocol: TLSv1.3, ...)

Verify database listing

\l
-- Expected: list of databases with Name, Owner, Encoding, Collate, Ctype, Access privileges

Verify table inspection

\dt
-- Expected: list of tables in current search_path

\d table_name
-- Expected: columns, types, indexes, constraints, foreign keys for the table

Verify script execution (single transaction)

psql -1 -f migration.sql dbname
# Expected: no errors, transaction committed. If any statement fails, all roll back.

Verify data export

\copy (SELECT count(*) FROM users) TO 'check.csv' WITH (FORMAT csv)
-- Then verify file exists and has expected content
\! cat check.csv

Verify query timing

\timing on
SELECT count(*) FROM users;
-- Expected: Time: X.XXX ms

Reference Files

Load these reference files from the references/ directory when deeper detail is needed:

External References

• PostgreSQL Client Applications (psql)
• Official PostgreSQL Documentation
• The SQL Language
• SQL Syntax
• SQL Commands
• PostgreSQL Wiki

Limitations

• Use this skill only when the task clearly matches its upstream source and local project context.
• Verify commands, generated code, dependencies, credentials, and external service behavior before applying changes.
• Do not treat examples as a substitute for environment-specific tests, security review, or user approval for destructive or costly actions.
• No live secrets are included in this skill; all passwords are shown as YOUR_PASSWORD placeholders.