Essential PostgreSQL Commands Every Developer Should Know

Master key PostgreSQL commands, from user creation to database management, to enhance your database administration skills.

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 

Creating Database

$ sudo -u postgres createdb 

Giving the user a password

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

Granting privileges on database

psql=# grant all privileges on database  to  ;

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

JSON Primitive Types and Corresponding PostgreSQL Types

Dynamic PostgreSQL Credentials Management with HashiCorp Vault: PHP Symfony & Go Examples

PostgreSQL 16 Installation on Ubuntu 22.04

PostgreSQL Full Text Search Rank by Position