Top psql commands with examples in 2025

You can handle PostgreSQL databases well by learning important psql commands. The most used commands help you connect to databases, see tables, manage users, and run queries. Knowing these commands lets you do tasks automatically and make fewer mistakes. You work faster when you run SQL statements, make tables, and move data easily.

Think about how you work now. See how these commands and tips can make your daily database work easier.

psql Command Basics

Image Source: pexels

Connect to Database

To use psql, you first connect to your PostgreSQL database. You need to know the server address, database name, port, and username. Here are steps to help you connect:

1. Find the psql tool on your computer.

2. Type the server address. Most times, it is localhost.

3. Put in the database name. The usual one is postgres.

4. Use port 5432 unless you have a different one.

5. Enter your username. The default is postgres.

You can change your connection by using flags. Flags help you connect in different ways. Here are some examples:

Connection Flags

Flags make your psql command better and easier to use:

• -h: Tells the computer name or IP address.

• -p: Picks the port number.

• -d: Names the database you want.

• -U: Shows the username.

• -W: Asks for your password.

Tip: Always use real values for host and user. Do not use general settings in your files. This keeps your database safe.

Environment Variables

You can set environment variables to make connecting easier. Set things like PGHOST, PGPORT, PGUSER, and PGDATABASE in your shell. When you use psql, these fill in the details for you.

For example:

1
2
3
4
5

export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydatabase
psql

This way saves time and helps you make fewer mistakes when you connect a lot.

Interactive Mode

Interactive mode lets you work with your database and see results right away. You type commands, see answers, and make changes fast.

Start and Exit

To start, type psql in your terminal. You will see a prompt. You can type SQL statements or psql commands. To leave, type \q and press Enter.

Note: Always exit when you finish. This keeps your database safe from others.

Single Command Execution

You can run one SQL command from your shell. Use the -c flag with psql. For example:

1

psql -d mydatabase -U postgres -c "SELECT version();"

This connects and shows the PostgreSQL server version.

Tips for Efficient Environment Setup

• Write notes for every line in your pg_hba.conf file. You know who can access and who to ask for changes.

• Only allow certain addresses in listen_addresses. This makes things safer.

• Keep your environment variables neat. You make fewer mistakes and save time.

Tip: Check your connection settings often. You avoid mistakes and keep your database protected.

Navigation Commands

When you use PostgreSQL, you need to move around a lot. Navigation commands help you find things fast. You can list databases, see tables, and switch databases with a few keys.

List Databases

You might want to see all databases on your server. The \l command shows you a list. This helps you know what databases are there before you connect or run queries.

\l Command

To see all databases, type this at the psql prompt:

1

\l

A table will show up with database names, owners, encoding, and who can use them. This helps you pick the right database for your job.

Tip: Use \l+ if you want more details, like database size and tablespace.

List Tables

After you connect, you may want to see what tables are in your database. The \dt command shows all tables in the current database and their schemas. This helps you look at your data structure.

\dt Command

To see all tables, type:

1

\dt

You will see a table like this:

You can also use these commands:

• \d shows tables, views, sequences, and roles.

• \d+ gives more details about each table, like columns and rules.

• \d table_name shows info about one table, like indexes and triggers.

If your database has lots of tables, you can filter by schema:

1

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;

Note: For big databases, make a script to list tables in the public schema. This saves time and helps you make fewer mistakes.

Switch Database

Sometimes you need to move from one database to another. The \c command lets you switch fast. This is good when you debug, do maintenance, or check data in different places.

\c Command

To switch databases, type:

1

\c target_database

For example, if you want to go from ecommerce_dev to ecommerce_prod, type:

1

\c ecommerce_prod

You can use this command to:

1. Jump between development and production databases when debugging.

2. Do maintenance on a staging database without opening a new terminal.

3. Load data into scratch databases for quick checks.

In PostgreSQL, each database is its own object on the server. The \c command connects you to another database on the same server. You do not need to start a new session.

Tip: Always check which database you are using before running any psql command. This helps you avoid mistakes and keeps your data safe.

Data Queries

Image Source: unsplash

Run SQL Queries

You use SQL queries in psql to look at, add, change, or delete data. These actions help you take care of your database every day. You can type queries in interactive mode or use the -c flag for one command.

SELECT, INSERT, UPDATE, DELETE

You usually start with SELECT to see data. For example:

1

SELECT * FROM customers WHERE city = 'New York';

To add new data, use INSERT:

1

INSERT INTO customers (name, city) VALUES ('Alice', 'Boston');

You change records with UPDATE:

1

UPDATE customers SET city = 'Chicago' WHERE name = 'Alice';

To delete data, use DELETE:

1

DELETE FROM customers WHERE name = 'Alice';

When you write hard queries, you can make them faster and easier. Here are some good tips:

1. Use Common Table Expressions (CTEs) to split big queries into small parts. This makes your SQL easier to read and fix.

2. Add indexes to columns you search or join a lot. Indexes make your queries faster.

3. Use views or temporary tables for big or repeated queries. This helps you keep your data neat and easy to manage.

You can also make things faster by using join optimization, partitioning, and caching. The table below shows how these ways compare:

Tip: Always test your queries with EXPLAIN ANALYZE. This shows how they run and helps you find ways to make them faster.

Export Results

You may need to save query results for reports or to share. psql gives you two main ways to export data: the \o command and the COPY command.

\o Command

The \o command lets you send all output to a file. You can use it like this:

1
2
3

\o output.txt
SELECT * FROM customers;
\o

This saves your query results to output.txt. When you are done, type \o again to see output on your screen.

COPY Command

The COPY command writes query results right to a file on the server. You can pick formats like CSV for easy sharing or import. For example:

1

COPY (SELECT * FROM customers) TO '/tmp/customers.csv' CSV HEADER;

This command makes a CSV file with your data. You can later use COPY FROM to put data back into a table.

Here is a quick look at both export ways:

Note: Use the COPY command for big exports or when you need a special file type. Use \o for fast, simple exports.

Data Manipulation

Edit Data

\edit Command

You can use the \edit command in psql to fix your SQL statements. This command opens your text editor, like vim or nano, from the psql prompt. You get a big window to write or change your queries. This makes it easier to work with long or tricky commands.

To use it, just type:

1

\edit

psql will open your editor with a blank file. You can write or paste your SQL command there. Save and close the editor. psql will run your command right away.

If you want to change your last command, type:

1

\edit

Your last command will show up in the editor. You can fix mistakes or make changes. Save and exit. psql will run the new command.

Tip: You can pick your favorite editor by setting the EDITOR environment variable. For example, use export EDITOR=nano in your shell to always use nano.

Benefits of using \edit:

• You do not have to type long queries in the terminal.

• You can find and fix mistakes before running commands.

• You can work better with hard SQL.

Transactions

BEGIN, COMMIT, ROLLBACK

Transactions help you make safe changes in your database. You use three main commands: BEGIN, COMMIT, and ROLLBACK. These commands let you group actions together. All changes happen at once, or none at all.

Here is how you use transactions:

1. Start with BEGIN;.

2. Run your SQL commands, like INSERT, UPDATE, or DELETE.

3. If everything works, type COMMIT; to save your changes.

4. If something goes wrong, type ROLLBACK; to undo all changes since BEGIN;.

1
2
3
4

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If you make a mistake, use:

1

ROLLBACK;

This will undo all changes in the current transaction.

Note: Transactions in PostgreSQL work as one unit. If one part fails, ROLLBACK undoes everything. If all parts work, COMMIT saves the changes. This keeps your data correct and safe. You do not have to worry about half-done updates or broken data.

Why use transactions?

• You keep your data safe from mistakes.

• You make sure your database stays correct, even if something fails.

• You decide when changes are saved for good.

Always use transactions when you make many changes at once. This helps you avoid mistakes and keeps your data safe.

Advanced psql Command Features

When you want to manage PostgreSQL databases like a professional, you need to know more than basic commands. Advanced features in psql help you work faster and smarter. You can use meta-commands to get more information and scripting to automate tasks.

Meta-Commands

Meta-commands in psql give you extra control over your database session. You can use them to display information, change settings, and run shell commands. These commands start with a backslash (\). You do not need to write SQL for these actions.

Here are some of the most powerful meta-commands you can use:

• \c [database name]: Connect to a specific database.

• \l: List all databases.

• \d: Show tables, views, and sequences.

• \dt: Display only tables.

• \dv: Display views.

• \dm: Show materialized views.

• \di: Display indexes.

• \dn: List schemas.

• \dT: Show data types.

• \sv [view name]: Show a view’s definition.

• \x: Toggle expanded display for query results.

• \set: List all internal variables.

• \cd: Change the working directory.

• \! [Command]: Run a shell command from psql.

• \timing: Turn query timing on or off.

• \echo [message]: Print a message to the console.

• \copy: Copy data to a file.

• \i [filename]: Run commands from a file.

• \o [file]: Write output to a file.

• \q: Exit psql.

You can use these meta-commands to explore your database, check performance, and manage files. You save time and avoid mistakes when you use them well.

\timing

You can measure how long your queries take with the \timing command. This helps you find slow queries and improve performance.

To turn timing on, type:

1

#### \timing

After you run a query, psql shows the time it took. You can turn timing off by typing \timing again.

Tip: Use \timing before running big queries. You can compare results and find ways to make your database faster.

\watch

You can use the \watch command to run a query again and again. This is useful when you want to monitor changes in real time.

For example, you want to see how many orders are in your table every 5 seconds:

1
2

SELECT COUNT(*) FROM orders;
\watch 5

psql will show the result every 5 seconds. You can stop watching by pressing Ctrl+C.

Note: Use \watch to track live data, monitor system health, or follow changes during maintenance.

Scripting

Scripting with psql helps you automate tasks and work more efficiently. You can write scripts to run commands, backup databases, and schedule jobs.

Shell Scripts

You can use shell scripts to run psql commands automatically. This is helpful for backups, restores, and regular checks.

Here is a simple backup script using Bash:

1
2
3

#!/bin/bash
DATE=$(date +%Y%m%d)
pg_dump -U postgres mydatabase > /backup/mydatabase_$DATE.sql

You can save this script and run it every day. You do not need to type commands by hand.

Tip: Always test your scripts in a development environment before using them in production.

Automation

You can automate routine tasks with psql scripting. This reduces manual work and keeps your database safe.

Common automation tasks include:

• Running SQL files to update tables or data.

• Scheduling backups with cron jobs.

• Checking database health every day.

• Sending reports to your team.

You can use Bash or Python for automation. Bash is good for simple tasks like backups. Python helps you connect to the database and run queries.

Here is a Python example:

1
2
3
4
5
6
7

import psycopg2

conn = psycopg2.connect(dbname="mydatabase", user="postgres")
cur = conn.cursor()
cur.execute("SELECT COUNT(*) FROM orders;")
print(cur.fetchone())
conn.close()

Best practices for scripting:

• Test scripts before using them on important data.

• Use version control to track changes.

• Document your scripts so others can understand them.

• Schedule tasks with cron jobs for regular automation.

Tip: Automate your daily tasks to save time and avoid mistakes. You can focus on more important work when you use scripts.

You can use the psql command in scripts to run queries, export data, and manage your database. Scripting makes your workflow faster and more reliable.

Productivity Tips

Troubleshooting

Connection Issues

Sometimes you cannot connect to your PostgreSQL database. Here are ways to fix the most common problems: First, check if the PostgreSQL server is running. Use the ps command or pg_ctl status to see if it is on. Next, look at the PostgreSQL logs for any errors about connections. Double-check your hostname, port, username, and password. Make sure they match your setup. If you are connecting from another computer, check the pg_hba.conf file. Make sure it lets your IP address or network connect. Look at your firewall settings. Open the PostgreSQL port, which is usually 5432, if you need to. Try this command to test your connection:

1

psql -h localhost -p 5432 -U myuser -d mydatabase

If you still have trouble, check the log files again for more details.

Problem: You might see errors about connection failures or timeouts. This often means the server cannot be reached or your settings are wrong.

Authentication Errors

Authentication errors happen when your login details or access rules do not match. Here is how to fix them: Check your connection details for mistakes. Make sure your host, port, database name, username, and password are correct. Look at the pg_hba.conf file. Make sure the authentication method and access rules are right for your user and IP address. The authentication method, like password or trust, should match what you use. If you use a remote client, make sure the firewall lets you connect. Check the PostgreSQL logs for any messages about authentication errors.

Shortcuts

Command History

You can use command history in psql to work faster. Press the up and down arrow keys to see old commands. This helps you repeat or change commands without typing them again.

Tab Completion

Tab completion helps you finish commands, table names, and column names. Just press the Tab key. This saves time and helps you avoid typing mistakes.

• Shortcuts can help you finish your project up to 30% faster.

• They help you find and fix problems quickly and save time on repeated tasks.

Best Practices

Organize Scripts

• Keep your SQL scripts in one place. This makes them easy to find and update.

• Use version control tools to track changes and stop mistakes.

• Add comments to each script so others can understand your work.

• Centralized management tools help you set rules for all environments and reduce errors.

Security Tips

1. Update PostgreSQL often to get new security fixes.

2. Use strong passwords for every database user.

3. Only give users the permissions they need.

4. Use firewalls to block access to PostgreSQL ports.

5. Turn on encryption for data in transit and at rest.

6. Use safe authentication methods.

7. Turn on auditing and logging to track activity.

8. Back up your data often and test your backups.

9. Check PostgreSQL settings for security.

10. Keep your operating system and software updated.

11. Use monitoring tools to watch for problems.

12. Run security checks and audits often.

Regular backups keep your data safe and help you recover fast if something goes wrong. Always have a backup and a plan for disasters.

Real-World Use Cases

Maintenance Tasks

Backup and Restore

Backing up your PostgreSQL database keeps your data safe. You use the pg_dump tool to make a backup. You use psql to put your data back if needed. To make a backup, run:

1

pg_dump -U postgres mydatabase > /backup/mydatabase_backup.sql

To restore your database, use:

1

psql -U postgres -d mydatabase < /backup/mydatabase_backup.sql

It is smart to back up every day or week. This helps you fix things fast if something breaks.

Health Monitoring

You keep your database working well by running maintenance commands. These commands clean up space and help your database run faster. Here is a table with common tasks:

Tip: Run VACUUM and ANALYZE after big changes. This keeps your database fast and healthy.

Collaboration

Share Scripts

You work better with your team when you share SQL scripts. Save each query in a .sql file. Put them in a folder called sql/. Name files for what they do, like create_customers_table.sql. Group scripts by topic, such as sql/customers/ or sql/orders/. Add a README file to explain what each script does.

Version Control

You use tools like Git to track changes to your scripts. This lets you see every change and undo mistakes. Version control helps your team write better code and learn quickly. Sharing scripts and using approval steps keeps everyone on the same page.

• You keep your scripts neat.

• New team members learn faster.

• Your code stays high quality.

Note: Always write clear messages when you save changes. This helps your team know what you did and why.

Integration

Cron Jobs

You can set up jobs to run at certain times using tools like pg_cron, RunMyJobs, or pgAgent. These tools let you run SQL queries on a schedule. Here is a table with popular ways to do this:

You use these jobs to back up data, clean old records, or send reports.

Cloud Databases

You can connect psql to cloud databases for easy management. Many cloud companies support PostgreSQL and give you tools for backups and monitoring. You get easy access, strong security, and good uptime.

Tip: Use cloud schedulers to automate tasks and watch your database. This saves you time and helps you make fewer mistakes.

Learning important psql commands helps you handle PostgreSQL databases well in 2025. You get more done and protect your data by using these commands daily. Practice them often so you remember how to use them. If you want to learn more, check out these resources:

• A guide that shows you how to make PostgreSQL work better and stay stable.

• An article that teaches you how to use special access commands in Linux to speed up PostgreSQL.

Keep learning and practicing to get better at managing databases.

FAQ

How do you change your default database in psql?

Set the PGDATABASE environment variable before starting psql. Use this command in your terminal:

1

export PGDATABASE=your_database_name

Now, when you run psql, you connect to your chosen database automatically.

What should you do if you forget a psql command?

Type \? at the psql prompt. This command shows a list of all available psql commands and their descriptions. You can quickly find the command you need.

How can you export query results to a CSV file?

Use the COPY command in psql. For example:

1

COPY (SELECT * FROM your_table) TO '/tmp/output.csv' CSV HEADER;

This saves your query results as a CSV file on the server.

How do you view the structure of a table in psql?

Type \d table_name at the psql prompt. You see the columns, data types, and indexes for that table. This helps you understand the table’s design.

What is the fastest way to rerun a previous command in psql?

Press the up arrow key to scroll through your command history. Select the command you want, then press Enter. This saves time and reduces typing errors.

How do you enable timing for your queries in psql?

Type \timing at the psql prompt. After you run a query, psql shows how long it took. Use this to measure and improve your query performance.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL