psql command-line tool basics in PostgreSQL - Step-by-Step Execution

Concept Flow - psql command-line tool basics

Start psql tool

↓

Connect to database

↓

Display prompt: dbname=#

↓

Enter SQL command or psql meta-command

↓

Execute command

↓

Show results or messages

↓

Repeat or exit with \q

This flow shows how you start psql, connect to a database, enter commands, see results, and exit.

Execution Sample

PostgreSQL

\c mydb
SELECT * FROM users;
\q

Connect to 'mydb', run a query to get all users, then quit psql.

Execution Table

Step	Input	Action	Output/Result
1	\c mydb	Connect to database 'mydb'	You are now connected to database "mydb".
2	SELECT * FROM users;	Run SQL query	Displays all rows from 'users' table
3	\q	Exit psql	psql session ends, back to shell prompt

💡 User enters \q command to quit psql.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3
Connected Database	none	mydb	mydb	none
Last Command	none	\c mydb	SELECT * FROM users;	\q
psql Session	not started	active	active	ended

Key Moments - 2 Insights

Why do we use a backslash before some commands like \c or \q?

Commands starting with backslash are psql meta-commands, not SQL. They control psql itself, like connecting or quitting. See execution_table rows 1 and 3.

What happens if I type a SQL command without a semicolon?

psql waits for the semicolon to know the command ended. It shows a continuation prompt until you finish the command. This is why in row 2, the query ends with a semicolon.

Visual Quiz - 3 Questions

Look at the execution table, what does the command \c mydb do?

A Runs a SQL query

B Connects to the database named 'mydb'

C Quits the psql tool

D Shows all tables

💡 Hint

Check Step 1 in the execution_table for the action and output.

At which step does the psql session end?

A Step 3

B Step 1

C Step 2

D psql never ends

💡 Hint

Look at the 'psql Session' variable in variable_tracker after each step.

If you forget the semicolon at the end of a SQL command, what will happen?

A psql executes the command immediately

B psql shows an error and exits

C psql waits for more input until semicolon is entered

D psql quits automatically

💡 Hint

Refer to key_moments about command endings and continuation prompts.

Concept Snapshot

psql is a command-line tool to interact with PostgreSQL.
Start it and connect to a database with \c dbname.
Run SQL commands ending with semicolon ;.
Use backslash commands (\q to quit).
Results show in the terminal.
Commands without semicolon wait for completion.

Full Transcript

The psql command-line tool lets you connect to PostgreSQL databases and run commands. You start psql, then connect to a database using \c followed by the database name. The prompt changes to show you are connected. You can enter SQL commands, but remember to end them with a semicolon so psql knows to run them. Some commands start with a backslash; these are special psql commands like \q to quit. When you run a command, psql shows the results or messages. You can repeat commands or exit anytime with \q.

Practice

1. What is the primary purpose of the psql command-line tool in PostgreSQL?

easy

A. To connect to a PostgreSQL database and run SQL commands interactively

B. To create new PostgreSQL database clusters

C. To backup PostgreSQL databases automatically

D. To monitor server hardware performance

Solution

1. Step 1: Understand the role of psql

   psql is a command-line interface tool designed to connect to PostgreSQL databases and allow users to run SQL commands interactively.

2. Step 2: Differentiate from other tools

   Other options like creating clusters or backups are done by different tools or commands, not psql.

3. Final Answer:

   To connect to a PostgreSQL database and run SQL commands interactively -> Option A

4. Quick Check:

   psql = interactive SQL client [OK]

Hint: Remember: psql = PostgreSQL interactive shell [OK]

Common Mistakes:

• Confusing psql with backup tools
• Thinking psql manages hardware
• Assuming psql creates database clusters

2. Which of the following is the correct way to start psql and connect to a database named mydb as user alice?

easy

A. psql start mydb alice

B. psql --connect mydb alice

C. psql connect mydb user alice

D. psql -d mydb -U alice

Solution

1. Step 1: Recall psql connection syntax

   The correct syntax to connect is psql -d database -U user.

2. Step 2: Match options to syntax

   psql -d mydb -U alice matches this syntax exactly, while others use invalid or unsupported flags.

3. Final Answer:

   psql -d mydb -U alice -> Option D

4. Quick Check:

   psql -d db -U user = connect [OK]

Hint: Use -d for database and -U for user [OK]

Common Mistakes:

• Using unsupported flags like --connect
• Writing commands without dashes
• Mixing command words incorrectly

3. Inside psql, what will be the result of running the command \dt?

medium

A. It lists all tables in the current database

B. It deletes the current table

C. It displays the current database connection info

D. It shows the last query execution plan

Solution

1. Step 1: Understand psql meta-commands

   Commands starting with a backslash (\) are special psql commands. \dt lists tables.

2. Step 2: Identify what \dt does

   \dt shows all tables in the current database schema, not deleting or showing connection info.

3. Final Answer:

   It lists all tables in the current database -> Option A

4. Quick Check:

   \dt = list tables [OK]

Hint: Backslash commands show info; \dt lists tables [OK]

Common Mistakes:

• Thinking \dt deletes tables
• Confusing \dt with connection info commands
• Expecting \dt to show query plans

4. You try to run psql -d mydb -U alice but get an error: psql: FATAL: password authentication failed for user "alice". What is the most likely cause?

medium

A. The database mydb does not exist

B. The psql command is misspelled

C. The password entered for user alice is incorrect or missing

D. The PostgreSQL server is not running

Solution

1. Step 1: Analyze the error message

   The error says "password authentication failed for user alice," indicating a login problem.

2. Step 2: Identify the cause

   This usually means the password is wrong or not provided, not that the database or command is missing.

3. Final Answer:

   The password entered for user alice is incorrect or missing -> Option C

4. Quick Check:

   Password error = authentication failed [OK]

Hint: Password errors mean wrong or missing password [OK]

Common Mistakes:

• Assuming database doesn't exist from this error
• Thinking psql command is wrong
• Ignoring server status

5. You want to list all databases available on your PostgreSQL server using psql. Which command should you run inside the psql prompt?

hard

A. SHOW DATABASES;

B. \l

C. \listdb

D. SELECT * FROM pg_databases;

Solution

1. Step 1: Recall psql meta-commands for listing databases

   The command \l (or \list) lists all databases in psql.

2. Step 2: Evaluate other options

   SHOW DATABASES; is MySQL syntax, \listdb is invalid, and SELECT * FROM pg_databases; is incorrect because the catalog table is pg_database, not pg_databases.

3. Final Answer:

   \l -> Option B

4. Quick Check:

   \l = list databases [OK]

Hint: Use \l inside psql to list databases [OK]

Common Mistakes:

• Using MySQL syntax SHOW DATABASES;
• Typing wrong meta-command \listdb
• Querying wrong system table name