PostgreSQL Common Commands

#postgres #programming

PostgreSQL Common Command You Need to know

Creating user,Creating Database,Giving the user a password & Granting privileges on database

sudo -u postgres psql
postgres=# create database mydb;
postgres=# create user myuser with encrypted password 'mypass';
postgres=# grant all privileges on database mydb to myuser;

Creating user

$ sudo -u postgres createuser <username>

Creating Database

$ sudo -u postgres createdb <dbname>

Giving the user a password

$ sudo -u postgres psql
psql=# alter user <username> with encrypted password '<password>';

Granting privileges on database

psql=# grant all privileges on database <dbname> to <username> ;

Psql commands

psql=# \l List available databases

psql=# \c dbname Switch connection to a new database

psql=#\dt List available tables

psql=#\d table_name Describe a table such as a column, type, modifiers of columns, etc.

psql=#\dn List all schemes of the currently connected database

psql=#\df List available functions in the current database

psql=#\dv List available views in the current database

psql=#\du List all users and their assign roles

psql=#\h Get help

psql=#\? Know all available psql commands

psql=#\q Exit psql shell

psql=#SELECT version(); *Retrieve the current version of PostgreSQL server`