Top PostgreSQL Commands You Must Know

PostgreSQL is a powerful, open-source object-relational database system that has a solid reputation for stability, feature robustness, and performance after more than 30 years of active development. PostgreSQL is used to store, manipulate and display the data in an application.PostgreSQL is used as a primary database for many web and mobile applications. PostgreSQL is very popular in tech giants like Apple which uses it for storing data for their users.

There are several applications of PostgreSQL :

Financial Industry: PostgreSQL is an excellent database management system for the financial sector. Furthermore, it is fully ACID compliant, making it an excellent alternative for OLTP (Online Transaction Processing).
Government GIS Data: PostgreSQL comes with a robust GIS named “PostGIS.” This addon contains hundreds of functions to process geometric data in multiple formats. PostGIS adheres to several industry standards.
Manufacturing: PostgreSQL is now being used by industrial businesses to improve their overall business processes. It enables businesses to cut down on their operational costs.
Web technology and NoSQL: Scalability is a major concern if your website must handle hundreds or even thousands of requests per second at any given time. Postgre proves to be the best option in this case.

In this blog, we will be learning about PostgreSQL commands. We will learn to build a connection to the database, using INSERT, SELECT, CREATE commands for insertion, reading and creation of values and tables in the database.

We will be executing these commands in a PostgresSQL shell, you should have the PostgreSQL installed on your device before moving forward. You can download PostgreSQL based on your Operating System on this site: https://www.postgresql.org/download/

Now you have the PostgreSQL available to you. The next step is to start the PostgreSQL server using

Start PostgreSQL Server/Initialize the Database

Command (To initialize): sudo service postgresql-9.3 initdb(linux)

Command (To start): sudo service postgresql-9.3 start(linux)

Note: Make sure to have the environment variables for postgres set before executing the command to prevent undefined errors

Stop PostgreSQL Server/Initialize the Database

Command: sudo service postgresql-9.3 stop(linux)

The PostgreSQL is up and running on your machine, let us create a new connection in the database server and set up a database along with other useful commands in PostgreSQL. Below is a list of the most common and useful commands in PostgreSQL.

PostgreSQL Commands List

1. Connect to PostgreSQL Database

The following command establishes a connection to a database under the control of a specified user. After pressing Enter, PostgreSQL will prompt for the user’s password.

psql -d database -U  user -W

To connect to the user_db database as a Postgres user, for example, execute the following command:

C:\Program Files\PostgreSQL\9.5\bin>psql -d user_dbl -U postgres -W
Password for user postgres:
user_db=#

If you want to connect to a database that is hosted on a different server, use the -h option:

psql -h host -d database -U user -W

If you want to use SSL mode for the connection, simply specify it as seen in the command below:

psql -U user -h host "dbname=db sslmode=require"

2. Change the database connection to a new one

You can switch the connection to a new database while logged in with an existing user. The previously established connection will be terminated. If the user parameter is omitted, the current user is assumed.

\c dbname username

Under the Postgres user, run the following command to connect to the user_db database:

postgres=# \c user_db
You are now connected to database "user_db" as user "postgres".
user_ab=#

3. List available database

The command is used to list all databases in the current PostgreSQL database server:

\l

4. List available table

The \dt command is used to list all tables in the current database:

\dt

This command only displays the table in the currently connected database.

5. Describe table

The following command is used to describe a table, such as a column, data type, column modifiers, and so on:

\d table_name

6. List available schema

A database schema represents the logical configuration of all or part of a relational database.

The \dn command is used to list all schemas in the currently connected database.

\dn

7. List available function

The \df command is used to display a list of available functions in the current database. In pg catalog there are several functions. We can list them using \df.

\df

8. List all views

The \dv command is used to list all accessible views in the current database.

\dv

9. List users and roles

You can define users as superuser(admin) who can do anything and everything in the database. Or a user with limited powers like adding/reading data only. You can use the \du command to get a list of all users and their assigned roles:

\du

10. Execute the previous command

To retrieve the current version of PostgreSQL server, you use the version() function as follows:

SELECT version();

Output :

postgres (PostgreSQL) 9.3.10

If you wish to save time by not having to type the preceding command twice, you can use the \g command to do so:

\g

The previous command, the SELECT statement, is repeated by psql.

11. Command History

The \s command is used to display command history. If you want to view the commands executed till now. Eg: database creation, table creation, table manipulation. You can use /s to view all of them in one go.

\s

If you want to store the command history to a file, use the following syntax to give the file name after the s command:

\s filename

12. Execute psql commands

If you want to run any psql command from a file, use the \i command, which is as follows:

\i filename

You can run any command written in a text file using /i. For eg:

This command can be executed in the command line using /i command.

13. Get help on psql commands

The \? command displays a list of all accessible psql commands.

\?

The \h command is used to seek help on certain PostgreSQL statements.

For instance, if you wish to learn more about the ALTER TABLE statement, you can execute the following command:

\h ALTER TABLE

14. Turn on query execution time

The \timing command is used to enable query execution time. This command helps you know the execution time of the query. For eg : 1.495ms for the below example.

user_ab=# \timing
Timing is on.
user_ab=# select count(*) from film;
 count
-------
  1000
(1 row)

Time: 1.495 ms
user_ab=#

You use the same command \timing to turn it off.

user_db=# \timing
Timing is off.
user_db=#

15. Edit command in your editor

It’s quite convenient if you can type the command in your preferred editor. You use the \e command in psql to accomplish this. Following the command, psql will launch the text editor specified by your EDITOR environment variable and paste the most recent command you typed into it.

psql will execute the command and return the result once you type it in the editor, save it, and shut it.

\ef [function name]

16. Switch output options

psql will execute the command and return the result once you type it in the editor, save it, and shut it.

\a command fixes the alignment of the output.
\H command formats the output in HTML form.

17. Quit psql

To quit psql, you use \q command and press enter to exit psql.

\q

In this tutorial, we learnt about PostgreSQL. We discussed the major features of PostgreSQL which led to its wide usage among several industries. We try to understand multiple commands for different functions in PostgreSQL like establishing the connection and showing the databases and tables created in it. Now you know how to get started with PostgreSQL on your machine.

FAQs

Q.1: What is PostgreSQL used for?

Ans: Many web, mobile, geospatial, and analytics applications use PostgreSQL as their primary data storage or data warehouse. PostgreSQL 12 is the most recent major version.

Q.2: What are the Features of PostgreSQL?

Ans: PostgreSQL is a powerful, open-source object-relational database system that has a solid reputation in stability, feature robustness, and performance.

Q.3: How do I access the PostgreSQL database?

Ans: You can connect to the database using

psql -d database -U  user

-u signifies user
-d signifies database