Blog

  • Basic and Useful PostgreSQL Commands

    Posted by Sanjay Saini | Last Updated: 15-Jan-19

    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

Tags: postgresql