8 Key PostgreSQL psql Commands for Database Administrators

Here are 8 important PostgreSQL psql commands that every database administrator should know:

1. \l

2. \c

3. \dt

4. \d

5. \du

6. \?

7. \timing

8. \i

Learning these commands helps you work faster and better. Surveys say skills like query analysis and scripting make jobs easier and improve how things run. You will see syntax, examples, and ways to use each command below. Use this guide to make your work easier and get better at using the command line.

1. \l List Databases

Image Source: unsplash

Syntax

You can use the \l command in PostgreSQL psql to see all the databases on your server. This command is simple and quick. Just type:

1

\l

You do not need to add anything else. This command works right after you log in to psql.

Example

Let’s say you want to check which databases are available. Open your terminal, connect to PostgreSQL psql, and enter:

1

\l

You will see a table like this:

This table shows all the databases you can access. You will also see details like the database name, owner, encoding, and access privileges.

Use Case

You might wonder when to use the \l command. Here are some common situations:

• You want to check if a database exists before connecting.

• You need to see who owns each database.

• You want to review access privileges for security reasons.

Tip: If you manage many databases, running \l helps you stay organized and avoid mistakes.

When you use the \l command, you should know about permissions. Not every user can see every database. Your role and privileges matter. Here are some common privileges in PostgreSQL:

• SELECT: Lets you read data from tables.

• INSERT: Lets you add new rows.

• UPDATE: Lets you change data.

• DELETE: Lets you remove rows.

• CONNECT: Lets you connect to a database.

Sometimes, row security policies limit what you can see or change. If row security is on, you might not see all rows unless your role allows it. By default, you can see all rows if you have the right privileges.

Using the \l command is a basic but powerful way to manage your PostgreSQL psql environment. It helps you keep track of your databases and understand who can access them.

2. \c Connect to Database

Syntax

You can use the \c command to change databases in PostgreSQL psql. This command lets you connect to another database without logging out. The basic way to use it is:

1

\c [database_name] [user_name] [host] [port]

You only need the database name to switch. You can also add your username, host, or port if you want to connect somewhere special.

Example

If you want to use the school_db database as the admin user, type:

1

\c school_db admin

To connect to a database on a different server, add the host and port:

1

\c school_db admin 192.168.1.100 5432

After you run the command, PostgreSQL psql might ask for your password. You will see a message that says you are now using the new database.

Use Case

You may need to change databases when you work on many projects. The \c command makes switching easy. You do not need to log out or start over. Just type the command and go to the next database.

Before you connect, check that you have what you need. Here is a simple checklist:

It is important to be safe when you connect. PostgreSQL psql has different ways to check who you are. Here are some common ways:

Tip: If you want to change databases quickly and safely, use the \c command. It helps you save time and work better.

You can use this command every day. It helps you move between databases, test things, and manage users. You stay in control and get things done faster.

3. \dt List Tables

Image Source: unsplash

Syntax

You want to see all the tables in your current database. The \dt command in PostgreSQL psql makes this easy. Just type:

1

\dt

You can also use patterns to filter tables. For example, \dt emp* shows tables that start with “emp”. If you want more details, add a plus sign like this:

1

\dt+

This gives you extra info, such as table size and access method.

Example

Let’s say you just connected to your database and want to check what tables you have. You type:

1

\dt

You will see a table like this:

This table shows you the schema, table name, type, and owner for each table. If you use \dt+, you get even more details, like how much space each table uses.

Use Case

You use the \dt command when you want a quick look at all tables in your database. This helps you stay organized and know what data you have. If you work with many schemas or need to check who owns a table, this command gives you that info right away.

Tip: Use \dt before running queries. It helps you avoid mistakes, like using the wrong table name or schema.

The \dt command also helps you see how your tables are grouped by schema. You can spot which tables belong to which owner. This is useful when you manage users or set permissions. If you want to see more about a table, you can use other commands, but \dt is your starting point for exploring your database in PostgreSQL psql.

4. \d Describe Table

Syntax

You use the \d command in PostgreSQL psql to see what a table looks like. Type:

1

\d [table_name]

Change [table_name] to the table you want to check. You do not need extra options for a simple look. If you want more details, type \d+ [table_name].

Example

Let’s say you want to look at the employees table. After you connect to your database, type:

1

\d employees

You will see a summary like this:

This table helps you see everything about your table quickly.

Use Case

You use the \d command when you need to know about a table before you do anything. Here are some reasons:

• You want to check column names and data types before you write a SELECT statement.

• You need to see if a column has NOT NULL or a default value.

• You want to know if the table has indexes or constraints.

• You need to look at triggers or rules that might change your data.

Tip: Use \d before you change a table. This helps you not make mistakes and keeps your database safe.

The \d command is very helpful in PostgreSQL psql for looking at and managing your tables. It gives you a clear view of your table’s structure, so you can work better and faster.

5. \du List Roles

Syntax

You want to see who can access your database and what they can do. The \du command in PostgreSQL psql helps you list all roles. Just type:

1

\du

You do not need extra options. This command works right after you log in.

Example

Let’s say you want to check which users and roles exist. After you connect, type:

1

\du

You will see a table like this:

This table shows you each role’s name, what they can do, and which groups they belong to. You can spot superusers, regular users, and roles that cannot log in.

Use Case

You use the \du command when you want to manage security and permissions. Here are some reasons to use it:

• You want to see who has superuser rights.

• You need to check which roles can create databases or new roles.

• You want to find users who cannot log in.

• You need to review which roles belong to certain groups.

Tip: Run \du before you add or remove users. This helps you avoid mistakes and keeps your database safe.

If you manage a team, you can use this command to make sure everyone has the right access. You might see a role called admin with many permissions, while readonly cannot log in. This helps you spot problems and fix them fast. In PostgreSQL psql, keeping track of roles is key for strong security.

6. ? PostgreSQL psql Help

Syntax

You want to find help fast when you use PostgreSQL psql. The \? command is your shortcut. Just type:

1

\?

This command works right after you log in. You do not need extra options. It shows you a list of all meta-commands you can use in psql.

Example

Imagine you forget how to list tables or connect to another database. You do not need to search online or leave your terminal. Type:

1

\?

You will see a screen with many commands and short descriptions. Here are some you might spot:

• \c [database name] – Connect to a database

• \l – List all databases

• \d – Show tables, views, and sequences

• \dt – List tables only

• \dv – List views

• \di – List indexes

• \dn – List schemas

• \dT – List data types

• \timing – Toggle query timing

• \i [filename] – Run commands from a file

• \q – Quit psql

You also see commands for changing directories, running shell commands, and printing messages. The help screen makes it easy to find what you need.

Use Case

You use the \? command when you want a quick reference. Maybe you are new to PostgreSQL psql or you just forgot a command. This feature helps you learn faster and work smarter. You do not need to leave the command line or open another window.

Here is what you get from using \?:

• You see all available meta-commands and their usage.

• You get help and documentation right in your terminal.

• You learn new commands as you explore the list.

• You save time and avoid mistakes.

Tip: If you want help with SQL commands, type \h. For help with psql meta-commands, use \?.

The \? command is perfect for beginners and experts. It helps you remember commands and discover new features. You stay focused and get more done.

7. \timing PostgreSQL psql Timing

Syntax

You can turn on query timing in PostgreSQL psql with a simple command. Just type:

1

\timing

This command works right after you log in. You do not need extra options. When you enable timing, PostgreSQL psql will show you how long each query takes to run.

Example

Let’s say you want to see how fast your queries run. First, type:

1

\timing

Now, run a query like this:

1

SELECT * FROM employees;

After the query finishes, you will see something like:

1

Time: 12.345 ms

This message tells you how many milliseconds the query took. If you run another query, you will see a new timing result each time.

Tip: You can turn timing off by typing \timing again. This toggles the feature on and off.

Use Case

You might wonder why timing matters. Here are some reasons you should use it:

• You want to measure how long your queries take.

• You need to find slow queries and make them faster.

• You want to compare different queries to see which one works best.

When you enable timing, you get instant feedback. You see the time for each query right in your terminal. This helps you spot problems and improve performance. Timing also includes network latency, so results may change if your connection is slow.

Here’s a quick list of what you gain:

• Fast way to check query speed

• Easy tool for performance tuning

• Simple method to track changes after updates

If you care about making your database run better, timing is a must-have tool. You can use it every day to keep your queries fast and your users happy.

8. \i Execute SQL File

Syntax

You can run a whole SQL file in PostgreSQL psql with the \i command. This command lets you execute many SQL statements at once. You just need to type:

1

\i [filename.sql]

Replace [filename.sql] with the path to your SQL file. You can use a relative or absolute path. If your file sits in the same folder as your terminal, you only need the file name.

Tip: Make sure your SQL file has the right permissions. If you cannot read the file, psql will show an error.

Example

Let’s say you have a file called setup_tables.sql. This file creates tables and inserts data. You want to run all the commands inside it. Here’s what you do:

1

\i setup_tables.sql

After you run this command, psql reads the file and executes each SQL statement. You see the results in your terminal. If your file has errors, psql will show messages to help you fix them.

Here’s a quick look at what your file might contain:

1
2
3
4
5
6
7
8

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  grade INT
);

INSERT INTO students (name, grade) VALUES ('Alice', 90);
INSERT INTO students (name, grade) VALUES ('Bob', 85);

You run everything with one command. You save time and avoid typing each line.

Use Case

You use the \i command when you want to automate tasks. Here are some common reasons:

• You need to set up a new database with tables and data.

• You want to apply changes from a migration script.

• You need to restore a backup or run a batch of updates.

Let’s look at how this helps you:

Note: If your script is long, you can break it into smaller files. You can run each file with \i to keep things organized.

The \i command makes your work easier. You can run scripts, fix problems, and set up databases without typing every command. This tool helps you work smarter and faster.

If you learn these 8 PostgreSQL psql commands, your skills get better and your work gets easier. You will have:

• More speed in your daily database jobs

• More trust from others at work

• More power to solve problems in new ways

Practice these commands often. You will:

• Get quicker at using the command line

• Work with hard queries without worry

• Keep your SQL knowledge strong

If you want more help or tips, look at these links:

• PostgreSQL Documentation

• Release Notes

• Tutorials & Other Resources

• FAQ

• Wiki

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