Psql commands
In this section, we will see the list of most commonly used psql commands, which help us to query data from the PostgreSQL database server faster and more effectively.
In SQL shell(psql), we have n-numbers of commands, but here we will talk about some of the most commonly used commands.
Connect to PostgreSQL database If we want to connect the PostgreSQL database under a particular user, we will follow the below process:
Firstly, we will open the command prompt into our local system and copy the path where we install the PostgreSQL.
Then we will use the below command:
Once we write the above command and press Enter key, the PostgreSQL will request the user’s password.
For example
To connect to tutoraspire database under Postgres user, we use the below command:
Output
As we can see in the below screenshot:
If we want to connect to a database that exists in on another host, we add the -h option in the above command:
In case, we need to use SSL mode for the connection, define it as shown in the below command:
- Switch connection to a new database
Once we are connected to a database, we can switch the connection to a new database under a user-specified by the user.
The above statement will connect to tutoraspire database under postgres user:
- List available databases
To list all databases in the current PostgreSQL database server, we will use the below command:
For example
If we execute the above command, we will get the below output to see the existing database present in the PostgreSQL.
- List available tables
To list all tables in the current database, we will use the below command:
For example
If we execute the above command, we will get the below output where we can see the existing database present in the PostgreSQL.
Note: The above command specifies only those tables which are presently linked to the database.
- Describe a table
The below command is used to describe a table such as a type, column, modifiers of columns, etc.
For example, suppose we have one existing table called Station1, and we want to describe it with the help of the above command:
Output
After executing the above command, we will get the below output:
- List available functions
We can use the following command to see the available list of functions in the existing database.
Output
If we execute the above command, we will get the below output window:
- List available schema
The below command is used to see the available list of all schemas of the currently connected database.
Output
After executing the above command, we will get the below output window:
- List users and their roles
In psql, we will use the following command to list all users and their assigned roles.
Output
After executing the above command, we will get the below output window:
- List available views
We will use the following command to get a list of available views in the existing database.
Output
We will get the below output once we implement the above command:
- Perform the previous command
We will use the version() function in the following command to recover the existing version of the PostgreSQL server.
Output
Once we execute the above command, we will get the below output:
We can use the below command to implement the previous command because the following command will help us save our time while we are typing the previous command again.
Output
As we can see in the below output, we will get the same result compared to the previous command:
Note: In psql, we can execute the previous command again, which is the SELECT command.
- Execute psql commands from a file
In case, if we want to implement psql commands from a file, we can use the following command:
- Get help on psql commands
We will use the following command to know all available psql commands.
Output
After executing the above command, we will get the below output window:
And, we can use the below statement to get help on specific PostgreSQL command.
Output
After executing the above command, we will get the below output window:
For example, if we want to know the thorough information on ALTER TABLE command, we will type the below command:
Output
Once we execute the above command, we will get the below result:
Turn on query execution time
We will use the following command to turn on query execution time.
Output
Once we execute the above command, we will get the below result:
And if we want to see the count from the Station1 table, we will use the below command:
to turn it off, we can use the same command as above:
Output
As we can see in the below output that the timing is off.
- Edit command in editor
If we want to write the command in any editor, we will use the following command:
Output
After executing the above command, psql will open the text editor specified by our editor environment variable and where we can write any command as we can see in the below screenshot:
After entering the following command in the editor, first, we will save it and then close the editor.
Once we close the editor, then psql will execute the command and return the output as we can see in the below image:
- Quit psql
We will use the below command to quit the psql, and then press any key to exit psql.
Output
Once we execute the above command, we will get the below output:
