Introduction to Postgres Command-Line
The following article provides an outline for Postgres Command-Line. The from-end from where you can interact with the database especially the PostgreSQL database are mainly categorized into two types Command- line interaction and graphical user interface interaction. Here we see about one such command-line terminal-based tool available in PostgreSQL for user-database interaction. Psql is the front-end tool that is based on the terminal. Hence, you can interact with your PostgreSQL database using the psql utility from your terminal itself easily and effectively.
Whenever you download PostgreSQL, psql comes built-in it. You don’t need to take any efforts if you have Postgres installed on your machine. Here we will see how we can use psql as a command-line utility for PostgreSQL database interaction and some of the most basic commands you should know while dealing with PostgreSQL while using the command line.
psql [options...] [databaseName [nameOfUser]]
You can open your terminal and login to your shell user and then for opening the psql command prompt, you can type the command in an above format where options, databaseName, and nameOfUser are optional arguments.
- options: There are many options available such as -a, -b, -c, -d, etc that can be used to mention whether you want to echo the results, echo queries or specify other related information while connecting to the psql database.
- databaseName: This is the name of the database that you wish to connect to while entering into psql command prompt. By default, it is not mentioned, you will be connected to the database named Postgres if available in your database which is the case most of the time or else template1 when Postgres is not present in your database system.
- nameOfUser: You can log in to the psql prompt with a particular user by specifying the name of that user over here.
Usage and Internals
Other than typing queries and issuing them to the Postgres, psql can also be used to fire the commands specified in an external file and use the metaCommands that certainly make the task easier and allows to write shell-like commands that enable us to write scripts using psql.
Whenever you type command on psql prompt, internally this command is issued to the Postgres server which is then interpreted and fired and results are sent back to psql and displayed on the psql terminal. We will take a look at some of the basic and crucial commands that a beginner should know when using the psql command-line interface to interact with the PostgreSQL database.
Examples of Postgres Command-Line
Given below are the examples mentioned:
Let us enter o the command shell with Postgres user using the following command.
sudo su – postgres
Which will ask you for root password if set in the following manner.
Let us now enter the psql prompt using the command.
If you want to enter the default database with the user already logged in as in the above case, Postgres is the username.
Here it will ask the password because we have already set it. After entering the password the command prompt seems to be somewhat like this.
So, now we are going to logged in to psql terminal prompt and our current database is Postgres.
View all databases.
The first command we are going to see is \l that lists up all the databases present in the current database server. \l command gives the following output.
As can be seen name, owner, encoding, collate, ctype and access privileges related to the databases are retrieved after firing \l command.
View all tables.
To ist out, all the tables in the current database, enter \dt command. The tables in RDBMS are referred to as the relations. \dt command gives following output.
If no tables are present in your database then a message saying “No relations found” will be displayed. The schema, name, type, and owner of the table are displayed using the \dt command.
Describe the table.
To retrieve information about the structure, constraints, and triggers on the table, we can use \d command. This is also known as describing the table. You can create the table using create table command if it doesn’t exist and then try to describe it if none is present in your database. For now, we will describe the table educational_platforms using the following command.
To switch your database and connect to some other database you can use \c or \connect command as follows.
Where educba is the name of the database you want to connect to that is passed as an argument.
So, you are now connected to educba database.
To know the syntax of particular command.
If you want to find out the syntax of a particular command you can use \h command.
For example, if you want to know the syntax of ALTER TABLE command, you can use the following command.
\h ALTER TABLE;
You can enter \q command to exit from the retrieved result.
List out all commands.
If you want to know about all the commands that are available in psql for your convenience, you can use \? command.
That gives the command syntax and its description.
If you want to know the information about the version of PostgreSQL you are using then you can fire the following command.
Firing last fired command again,
If you want to retrieve the last command that was fired by you and fire the same. You can easily retrieve and fire the same using the \g command.
We can use a psql command-line from-end terminal-based tool available to us easily and efficiently. Many metacommands help us to manipulate and interact with our database to perform operations easily. The psql command-line can also be used to write scripts and run commands present in external files.
This is a guide to Postgres Command-Line. Here we discuss the introduction, usage and internals and examples respectively. You may also have a look at the following articles to learn more –