Basic and Useful PostgreSQL Commands

Hi Guys,

In this Blog, I'm going to share some basic commands that are useful for a developer in daily use. I give you a list of simple psql commands that supports you query data from PostgreSQL database.

1. Connect to the PostgreSQL database

This command helps you to connect with the specified database.

psql -U user -d database

For example, to connect to demo database under postgres user, you use the following command:

psql -U postgres -d demo

2.Change connection to a new database

Once you are connected to a database, you can change the connection to a new database under a user specified by user. The previous connection will be closed. If you omit the user parameter, the current user is expected.

\c dbname username

The following command connects to macdi database under postgres user:

demo=# \c macdi postgres
You are now logged In to database "macdi" as user "postgres".
macdi=#

3.Get list of available databases

To get the list of databases in the current PostgreSQL database server, you use \l command:

postgres=# \l

4.Get List of available Tables

To get the list of tables in the current database, you use \dt command:

postgres=# \dt

Note that this command shows the only table in the currently connected database.

5.know about the details of a table

This command is used to describe a table details such as a column, type, modifiers of columns, etc., you use the following command:

BA=# \d

6.Get list of available schema

To get the list of all schema of the currently logged In a database, you use the \dn command.

BA=# \dn

7.Get list of available functions

To get the list of present procedures in the selected database, you use the \df command.

BA=# \df

8.Get list of available views

To get the list of present views in the selected database, you use the \dv command.

BA=# \dv

9.Get list of users and their roles

To get the list of all users and their related authority, you use the \du command :

BA=# \du

10.Run psql commands from a file

In case you like to execute PostgreSQL commands through a file, you use the \i command as follows:

\i filename

For example :

BA=# \i test.sql 
(test.sql file contains (select * from company command)

These are some basic commands that are every used by each developer in daily working life. 

Thanks

Sanjay Saini