Top psql commands and flags you need to know | PostgreSQL

PostgreSQL, or Postgres, is an object-relational database management system that uses the SQL language. It's free, open-source, reliable, robust, and performant. PostgreSQL is also one of the most popular & used relational databases.

psql is an interface you can access through the terminal to interact with Postgres databases. You can use it to connect to a database, add & read & modify data, check the available databases & fields, run commands from a file, and so on.

If you want to learn about Postgres commands or refresh your memory, you are in the right place! This article will teach you the top psql commands and flags you need to know when working with PostgreSQL.

The first step involves learning how to connect to a database. There are two ways to connect to a PostgreSQL database, depending on where the database resides.

Same host database

If the database is on the same host as your machine, you can use the following command:

psql -d <db-name> -U <username> -W

// example
psql -d tutorials_db -U admin -W

The above command includes three flags:

• -d - specifies the name of the database to connect to
• -U - specifies the name of the user to connect as
• -W - forces psql to ask for the user password before connecting to the database

In this example, the command connects you to the tutorials_db under the admin user.

Different host database

In the cases where your database is hosted somewhere else, you can connect as follows:

psql -h <db-address> -d <db-name> -U <username> -W

//example
psql -h my-psql-db.cloud.neon.tech -d tutorials_db -U admin -W

The -h flag specifies the host address of the database.

SSL mode

There might be cases where you want to use SSL for the connection.

psql "sslmode=require host=<db-address> dbname=<db-name> user=<username>"

//example
psql "sslmode=require host=my-psql-db.cloud.neon.tech dbname=tutorials_db user=admin"

The above command opens an SSL connection to the specified database.

In many cases, you will work with more than one database. You can list all the available databases with the following command:

\l

The above image illustrates what happens when you run the command. You get a table with all databases and their name, owner, access privileges, and other information.

You can also switch to another database with the following command:

\c <db-name>

// example
\c tutorials_db

The below image illustrates the result after running the command.

The command switches to the specified database under the user you logged in previously.

Let's consider you want to see all the tables from the database. You can list all database tables as follows:

\dt

The \dt psql command returns the tables alongside:

• the schema they belong to
• their type
• their owner

psql also has a command that lets you see the table's structure.

\d <table-name>

// example
\d tutorials

The \d command returns all the columns, their types, collection, whether they are nullable or not, and their configured default value.

If you want more information about a table, you can use the command:

\d+ <table-name>

Now, you get extra information such as storage, compression, stats target, and a description.

The \dn psql command lists all the database schemas.

It returns the name of the schemas and their owners.

Sometimes, you might need to change the user. Postgres has a command that lists all the users and their roles.

\du

As the image shows, the command returns all the users.

You can also retrieve information about a specific user with the following command:

\du <username>

//example
/du postgres

Now, you can see the roles of the specified user, and whether the user is a member of a group or not.

You can list all the functions from your database with the \df command.

The command returns all functions and the:

• schema they belong to
• names
• result data type
• argument data types
• type

The psql interface enables you to list all the database views with the \dv command.

There might be cases where you want to analyze the result of a query at a later time. Or two compare two query results. The psql interface allows you to do that.

You can save query results in a file as follows:

\o <file-name>

// example
\o query_results
...run the psql commands...
\o - stop the process and output the results to the terminal again

Let's save the following query results in a file:

• the available database tables
• the result of describing a database table
• the list of all users
• the details of the user "postgres"

Note: To stop saving results to the file, you need to run the \o command again without the file name.

The above image illustrates the file containing all the query results.

It's also possible to run commands from a file. For simple commands, it might not be the best solution. But when you want to run multiple commands and complex SQL statements, it helps a lot.

Create a txt file with the following content:

\l
\dt
\du

When you run the file, it should return a list of all:

• databases
• database tables
• users

You can run commands from a file with the following psql command:

\i <file-name>

// example
\i psql_commands.txt

The command returned all the databases, tables, and users as expected.

You quit the psql interface with the \q command.

All the above commands with psql are great to connect to PostgreSQL on the commandline for direct access and execution of operations. But in case you are looking to build APIs for data access on Postgres, you should check out Hasura. Hasura connects to your existing or new PostgreSQL database, introspects the schema(s) and lets you generate CRUD APIs in GraphQL and REST declaratively in minutes. Read more here to get started with Hasura and PostgreSQL.

Note: Any flavor of Postgres which is wire compatible will work with Hasura.

The psql interface is powerful and allows you to do many things, including running SQL statements. If you want to get started with PostgreSQL or deepen your knowledge, check out our PostgreSQL Tutorial. You can also check out the Postgre articles on our blog.

If you are looking for options to host your Postgres database, check these PostgreSQL hosting options.

• Hasura and Postgres
• PostgreSQL basics
• Postgres 15 release: Top things to know
• Learn Postgres for free
• Top PostgreSQL database free tier solutions
• Top serverless PostgreSQL providers