PostgreSQL psql is the command-line interactive tool that comes with the PostgreSQL installation package to interact with the PostgreSQL database cluster and run the SQL queries. We can pass an input file to psql or run the psql command on an interactive command-line interface. psql provides the features such as writing the scripts and creating automation for various tasks.
Connect To PostgreSQL Psql
There are multiple ways through which we can use the PostgreSQL psql command-line interface to connect to the PostgreSQL database cluster. In this section, we will see those options.
1. Connect to Exact Database Using Username and Password
We can connect to a specific database name using the username and the password. In the following example, we are using the postgres as the user name and the same for the DB name to make the connection.
Syntax:
$psql -d database -U user –W (Here -d =Database, -U = User, -W = Password)
Command:
cloudduggu@ubuntu:-$ sudo -i -u postgres
cloudduggu@ubuntu:-$ psql -d postgres -U postgres –W
Output:
2. Connect to Hostname Using Username and Password
We can connect to a specific hostname using the username and the password.
Syntax:
$psql -h host -d database -U user –W (Here -h =Hostname, -d =Database, -U = User, -W = Password)
3. Connect Using SSL Mode
We can make a connection with PostgreSQL Database Cluster using the SSL mode.
Syntax:
$psql -U user -h host "dbname=db sslmode=require"
PostgreSQL Psql Commands
We can connect to PostgreSQL Database Cluster using the psql utility as mentioned above. Once the connection is successful the prompt name is provided with the DB name. The format of the psql command is starting from a backslash that is followed by the command and then the argument.
Let us see the example of the PostgreSQL psql command in the following section.
1. PostgreSQL \l Command
\l Command is used to list all databases present in the PostgreSQL Database Cluster.
Command:
postgres=# \l
Output:
2. PostgreSQL \dt Command
\dt or \dt+ Command is used to show the list of available tables.
Command:
postgres=# \dt
postgres=# \dt+
Output:
3. PostgreSQL \d tablename
\d tablename Command is used to describe the table detail. In the below example the Films_Detail is the table name.
Command:
postgres=# \d Films_Detail
Output:
4. PostgreSQL \du Command
\du Command is used to show a list of the user's detail.
Command:
postgres=# \du
Output:
5. PostgreSQL \dn Command
\dn Command is used to list the number of schemas present in PostgreSQL.
Command:
postgres=# \dn
Output:
6. PostgreSQL \df Command
\df Command is used to list functions present in PostgreSQL.
Command:
postgres=# \df
Output:
7. PostgreSQL \dv Command
\dv Command will show the views present in PostgreSQL.
Command:
postgres=# \dv
Output:
8. PostgreSQL \s Command
\s Command will show the previous command's history.
Command:
postgres=# \s
Output:
9. PostgreSQL \s filename
\s filename is used to save the commands history in a file. In the below example we have saved the commands history in commandhistory.txt file.
Command:
postgres=# \s commandhistory.txt
Output:
10. PostgreSQL \g Command
\g Command execute the previously submitted command. In the below example we have executed SQL to check the database OID and we only issued \g and the previous command is executed successfully.
Command:
postgres=# \g
Output:
11. PostgreSQL \? Command
\? command shows the help option available for the psql.
Command:
postgres=# \?
Output:
12. PostgreSQL \timing Command
\timing command is used to show the query execution time. In the following example, we will use the \timing option to check the query run time.
Command:
postgres=# \timing
postgres=# Select oid,datname from pg_database;
postgres=# \timing
Output:
13. PostgreSQL \timing Command
\e command opens an editor to edit the commands. In the below example, we will use the \e command to open an editor, Ubuntu will ask us to select the editor type, we need to select one, we have selected nano editor and then we can modify or edit the SQL.
Command:
postgres=# \e
Output:
Press CTRL + O to save the file. Once the file is saved press CTRL+X to exit from the editor.
14. PostgreSQL \conninfo Command
\conninfo command shows the information about the connection details as mentioned on the below output.
Command:
postgres=# \conninfo
Output:
15. PostgreSQL \a Command
\a command is used to align the output format, if it is unaligned then after running this command it will be aligned. In the following example, we have shown this example.
Command:
postgres=# \a
postgres=# Select oid,datname from pg_database;
postgres=# \a
postgres=# Select oid,datname from pg_database;
Output:
16. PostgreSQL \H Command
\H command will format the output in the HTML format.
Command:
postgres=# \H
Output:
17. Run PostgreSQL commands from the file
We can run the SQL queries from a file using psql. We have created a file name command.sql under the /var/lib/postgresql and out of this SQL select current_Schema(); to see the current schema. Now we will run the script using psql as mentioned below.
Command:
postgres=# psql -U postgres -f command.sql
Note: In the above command the postgres is the username and the command.sql is the file name.
Output:
18. PostgreSQL \o Command
\o filename command sends the query output to a file. In the following example, we have saved the output of select current_Schema(); to a file named queryoutput.txt.
Command:
postgres=# \o queryoutput.txt
postgres=# select current_Schema();
Output:
19. PostgreSQL \set AUTOCOMMIT off/on Command
This command is used to set the auto-commit off/on at the session-level.
Command:
postgres=# \set AUTOCOMMIT off
postgres=# \set AUTOCOMMIT on
Output:
