PublishedBEG-051-L02

psql and a GUI client

Lesson 2 of 6
11 min read
Beginner

On This Lesson 11 topics

Lesson Overview
Learning Objectives
Written Explanation
Examples
Backend Insight
Common Mistakes
Exercises
Mini Challenge
Summary & Next Lesson
References
Practice

Lesson Overview

Lesson 1 left you standing at the front door of the library building: the server runs and you can log in. This lesson teaches you to move around inside with two kinds of client. psql is a text client that lives in the terminal. pgAdmin and DBeaver are graphical clients that show databases and tables as a tree and let you click.

The two are not rivals. The graphical tool is the guest map on the wall: easy to look at. psql and SQL script files are the librarian's logbook: slower to learn, but you can repeat every step exactly. By the end you will know the handful of psql commands that you will use every day, and when to reach for each tool.

Why this matters: the Lab at the end of the module requires a script that rebuilds the whole database from scratch. You can only write that if you know how to run a file through psql.

This lesson builds on Lesson 1 (a running server, psql -U postgres) and BEG-050 (SQL statements).

Learning Objectives

By the end of this lesson you will be able to:

Connect with psql using host, port, user, and database options.
Tell SQL statements apart from psql meta-commands (the ones starting with a backslash).
List databases, schemas, tables, and roles with \l, \dn, \dt, and \du, and describe a table with \d.
Run a single command with -c and a whole script with -f.
Connect a graphical client (pgAdmin or DBeaver) to the same server and browse tables.
Choose between the graphical client and psql for a given job.

Written Explanation

The connection command

A psql connection needs the same five facts you met in Lesson 1. The command-line options below are documented in the psql manual:

bash

psql -h localhost -p 5432 -U postgres -d postgres

Option	Meaning	If you leave it out
`-h`	Host name of the server	Connects to the local machine (on Windows, to `localhost` over the network)
`-p`	Port	The compiled-in default, normally 5432
`-U`	User (role) name	Your operating-system user name (so you often need `-U`)
`-d`	Database name	A database named after the user

Password is not an option here: if the server wants one, psql prompts for it and does not echo what you type. That is the safe way to supply it.

SQL versus meta-commands

Inside psql you type two different kinds of line.

SQL statements end with a semicolon and are sent to the server: SELECT version();.
Meta-commands start with a backslash, are understood by psql itself, and need no semicolon: \dt.

If you forget the semicolon on SQL, psql waits with a continuation prompt such as postgres-#. It is not frozen; it is waiting for the end of the statement.

The meta-commands you need now (all described in the manual):

Command	What it does
`\l`	List databases with owner and encoding
`\c name`	Connect to another database
`\dn`	List schemas
`\dt`	List tables
`\d table`	Describe one table: columns, types, indexes, constraints
`\du`	List roles (users)
`\conninfo`	Show who and where you are connected
`\?`	Help for the backslash commands
`\q`	Quit

On an empty database \dt says Did not find any tables. Once the Library tables exist, a table listing looks like this (from the test server):

text

             List of tables
 Schema |  Name   | Type  |    Owner
--------+---------+-------+-------------
 public | authors | table | library_app
 public | books   | table | library_app
 public | loans   | table | library_app
 public | members | table | library_app
(4 rows)

You will learn in Lesson 3 what public and library_app in that table mean. For now, read it as: four tables, each in the shelf called public, each owned by one user.

Commands and scripts from outside

You do not have to open an interactive session. Two options run something and return to the terminal:

-c "SQL or one meta-command" runs one command string and exits.
-f script.sql reads all commands from a file. The manual notes that with -f, error messages include the file name and line number, which makes a failing script much easier to debug.

bash

psql -U postgres -d library -c "\dt"
psql -U postgres -d library -f schema.sql

A script file is simply the SQL you would have typed, saved as text. Running the same file twice should give the same result: this idea is at the heart of the Lab.

By default psql carries on after an error in a script. The variable ON_ERROR_STOP changes that; setting it on the command line (-v ON_ERROR_STOP=1) stops at the first error. The manual describes this variable only briefly, so test it on a small script before relying on it.

A graphical client

pgAdmin and DBeaver both show a tree of servers, databases, schemas, and tables, let you open a table, and offer a window to type SQL. According to the PostgreSQL Windows download page, the standard Windows installer bundles pgAdmin. DBeaver Community is a separate free, open-source tool that supports PostgreSQL and many other databases.

In either tool you register or create a connection and fill in the same five facts: host (localhost), port (5432 unless you changed it), database, user, and password. Menu names and screens differ between versions and operating systems, so look for "new server" or "new connection" rather than following exact clicks. Some tools offer to save the password; on a shared computer, decline.

After connecting, expand the tree: server, then database, then schemas, then public, then tables. That is the same list \dt printed.

When to use which

Job	Better tool
Exploring an unfamiliar database visually	GUI
Looking at table contents quickly	GUI or `psql`
Running a saved script, the same way every time	`psql -f`
Working on a server over a plain terminal	`psql`
Anything that must be repeatable or reviewable	SQL script, run by `psql`

A common trap: building tables by clicking in a GUI. The result is correct on your machine, but nobody can reproduce it. Use the GUI to look, and write what matters as SQL in a file.

Examples

Example 1: finding your way around (simple)

text

postgres=# \l
postgres=# \c library
You are now connected to database "library" as user "postgres".
library=# \dt
library=# \d loans

\l lists the rooms, \c library walks into one, \dt lists its shelves of tables, and \d loans looks at one table:

text

                   Table "public.loans"
  Column   |  Type   | Collation | Nullable |   Default
-----------+---------+-----------+----------+--------------
 id        | integer |           | not null |
 book_id   | integer |           | not null |
 member_id | integer |           | not null |
 loan_date | date    |           | not null | CURRENT_DATE
Indexes:
    "loans_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "loans_book_id_fkey" FOREIGN KEY (book_id) REFERENCES books(id)
    "loans_member_id_fkey" FOREIGN KEY (member_id) REFERENCES members(id)

Notice that \d shows constraints: Nullable, the primary key, and the foreign keys. It is the quickest way to check that a table is what you intended.

Example 2: the same question, three ways (practical)

How many books are there? In an interactive session:

sql

SELECT count(*) FROM books;

From the terminal in one line:

bash

psql -U postgres -d library -c "SELECT count(*) FROM books;"

And in the GUI: open the SQL window for the library database, paste the statement, run it. All three ask the server the same question and show the same answer. The first two can be put into a script or a shell file; the third is a click-and-forget action. Choose by whether you will ever need to do it again.

Example 3: a script with a typo (realistic)

You save this as fix.sql:

sql

SELECT id, title FROM books;
SELEC id FROM members;
SELECT id FROM authors;

Run it with psql -U postgres -d library -f fix.sql. Without ON_ERROR_STOP, psql runs the first and third statements and reports an error for the second, naming fix.sql and line 2. That is helpful for finding the typo and risky for important scripts: a long script can scroll past an early error and keep going on a half-built database. Adding -v ON_ERROR_STOP=1 makes the script stop where it first fails. For a beginner script, scroll to the first error, fix it, and rerun from the start.

Backend Insight

In a real project the database is changed by files, not by clicking. A team keeps SQL scripts in Git, and every developer, the test server, and production run those same files in order. The graphical client is still valuable (it makes data visible, which helps when a bug is in the data), but it is not the source of truth.

The psql options also map directly onto application configuration: host, port, database, user, password are exactly the pieces of a connection setting in a Java application later. Reading \conninfo output teaches you which of the five values you are using right now, which is the first thing to check when "it connects on my machine but not on yours".

Common Mistakes

Mistake 1: Typing a meta-command with a semicolon or SQL without one

\dt; is not needed (and may behave oddly). Meta-commands end at the line end.
SELECT 1 without ; just waits for more input. Add the semicolon.

Mistake 2: Running a script against the wrong database

psql connects to a default database if you omit -d. A script that creates tables then lands in the wrong place. Check the prompt (library=#) or \conninfo before running anything that changes data.

Mistake 3: Building the schema by clicking in the GUI

Nothing records what you did, so nobody (including you next week) can repeat it. Keep the SQL in a file.

Mistake 4: Saving passwords in the GUI on a shared machine

A saved password lets the next user open your connections. Decline to save it, or use only a throwaway learning password.

Exercises

Try each exercise yourself before opening the solutions.

Level A: Foundation

A1. What is the difference between SELECT 1; and \dt at the psql prompt?

Show solution A1

A1. SELECT 1; is SQL, sent to the server, and ends with a semicolon. \dt is a psql meta-command, handled by psql itself, with no semicolon.

A2. Which meta-command lists tables, and which describes one table?

Show solution A2

A2. \dt lists tables; \d tablename describes one table, including constraints.

A3. Which option runs a file of SQL, and which runs one command string?

Show solution A3

A3. -f file.sql runs a file; -c "command" runs one command string.

Level B: Application

B1. Write one command line that connects to the database library as user postgres on the local machine and lists its tables, without opening an interactive session.

Show solution B1

B1. psql -U postgres -d library -c "\dt" (you may add -h localhost). The -c string contains exactly one meta-command, which the manual allows.

B2. You see the prompt postgres-# after typing SELECT id FROM books. What happened, and how do you finish the statement?

Show solution B2

B2. The statement has no terminating semicolon, so psql is waiting for more input. Type ; and press Enter.

B3. In a GUI client, which five facts do you enter to create a connection to your local server?

Show solution B3

B3. Host, port, database, user, and password.

Level C: Challenge

C1. A colleague created all the Library tables by clicking in a GUI and now says "just copy my database". Explain two problems with that, and describe a better way to share the work.

Show solution C1

C1. Problems: nothing records the steps, so it cannot be repeated or reviewed in Git; and "copy my database" would copy their data and mistakes too, and can differ between machines. Better: write the tables as a SQL script, commit it, and let everyone run it with psql -f.

C2. \conninfo shows Database | postgres right before you run schema.sql that was meant for library. Say what could go wrong and how you would avoid it, using two different methods.

Show solution C2

C2. The script would create (or drop) tables in the wrong database. Avoid it by naming the database explicitly (psql -d library -f schema.sql) so you do not depend on a default, and by checking \conninfo or the prompt before you run a script that changes things.

Mini Challenge

Make a one-page "cheat card" for your own use:

Write the exact psql command that connects to your server, with all five facts (leave the password out).
List five psql commands with a one-line purpose each.
Connect with pgAdmin or DBeaver and write down the path in the tree to the list of tables, in your tool's own words.
Add a line stating, in your own words, which of the two tools you would use to re-create a database on a new laptop and why.

Check your work: the card contains no password, and item 4 says "script", not "click".

Summary & Next Lesson

Key takeaways

psql connects with -h, -p, -U, -d; the password is asked for, not typed on the command line.
SQL ends with a semicolon; meta-commands begin with a backslash and do not.
\l, \dn, \dt, \d table, \du, and \conninfo tell you what exists and where you are.
-c runs one command and -f runs a file; scripts are how work is repeated.
A GUI is excellent for looking; repeatable changes belong in SQL files.

Next lesson

The next lesson, "Databases, schemas, and users", explains the rooms, shelves, and keys you just listed: how to create a database, what a schema is, and why the application should have its own user instead of postgres.

Self-check

What does the prompt postgres-# mean?
Which command shows who and where you are connected?
Why does the manual's mention of line numbers for -f matter?
Name one job where the GUI is better and one where psql -f is better.
Why is clicking tables together in a GUI a weak way to build a schema?

Show answers

psql is waiting for the rest of a SQL statement (usually the missing semicolon).
\conninfo.
Errors in a script then name the file and line, so you can find the failing statement.
GUI: exploring an unfamiliar database visually. psql -f: repeating a saved script exactly.
It cannot be repeated, reviewed, or shared reliably; a SQL script can.

References

PostgreSQL documentation, psql. Supports: the -h, -p, -U, -d, -c, -f options (including line numbers in -f errors), the meta-commands \l, \dt, \dn, \du, \d, \conninfo, \q, and password prompting. The defaults for omitted -U and -d (operating-system user name, database named after the user) are standard PostgreSQL client behavior that I did not confirm on the page I read. The manual text I read mentions ON_ERROR_STOP only briefly, so its behavior here was confirmed by running it on a local PostgreSQL 18.3 server, and the output samples come from that server.
PostgreSQL, Download for Windows. Supports: the installer includes pgAdmin.
DBeaver. Supports: DBeaver Community is a free, open-source database tool supporting PostgreSQL among others. The pgAdmin website could not be reached when I tried, so my description of pgAdmin relies on the PostgreSQL download page only.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson