Psql and pgcli

---

PSQL

psqlPostgreSQL Documentation

---

For a Data Engineer, the command-line interface (CLI) is often the fastest way to interact with a database, especially when working on remote servers where a GUI like pgAdmin or DBeaver isn't available.

---

psql vs. pgcli

psql (The Industry Standard)

This is the default terminal-based front-end to PostgreSQL. It is pre-installed with Postgres and is extremely lightweight and powerful.

• Pros: Ships with Postgres, scriptable, supports all administrative commands.

• Cons: No syntax highlighting, no auto-completion by default.

pgcli (The Modern Alternative)

This is a popular open-source wrapper for psql. Think of it as psql with superpowers.

• Pros: Auto-completion as you type (suggests table and column names), syntax highlighting, and a cleaner results table.

• Cons: Must be installed separately (usually via pip install pgcli).

---

Common CLI Navigation Commands

In both tools, "Meta-commands" start with a backslash (\). These are handled by the CLI itself, not the SQL engine.

Command	Action
\l	List all databases on the server.
\c db_name	Connect to a different database.
\dt	Describe Tables: List all tables in the current database.
\d table_name	Describe a specific table's schema (columns, types, indexes).
\dn	List all Schemas (namespaces).
\du	List all Users (roles) and their permissions.
\df	List all Functions.
\q	Quit the CLI.

---

Working with Queries

Inside the CLI, you write standard SQL. However, there are some unique behaviors to keep in mind:

• The Semicolon: Unlike some tools, psql will not execute your query until you add a ;. If you hit Enter without it, the prompt changes from db=# to db-#, signaling it’s waiting for the rest of the statement.

• Expanded Mode (\x): If you query a table with many columns, the output becomes unreadable. Toggle \x to "Expanded display," which flips the rows into a vertical key-value list (perfect for wide tables).

• Timing (\timing): Type this to see exactly how many milliseconds each query takes to execute—essential for performance tuning.

---

Input and Output (DE Essentials)

As a Data Engineer, you often need to move data into or out of the database via the CLI.

Execute a Script

Instead of copying and pasting code, you can run a .sql file directly:

psql -d my_db -f transform_data.sql

The \copy Command

While the SQL COPY command requires superuser access (because the server reads the file), the psql meta-command \copy works from your local machine.

-- Exporting a table to CSV
\copy (SELECT * FROM users) TO 'users_export.csv' WITH CSV HEADER;

---

Visualizing the CLI Connection

---

Summary

• psql is for reliability and scripting; pgcli is for a better developer experience (DX).

• Use \d+ to see "extra" info like table size and comments.

• Use \watch 5 after a query to repeat it every 5 seconds (great for monitoring a long-running migration).

• Environmental Variables: You can set PGPASSWORD, PGHOST, and PGUSER in your shell to connect without typing a long string of flags.

---