Mastering PostgreSQL: A Comprehensive Guide to Postgres Commands

PostgreSQL is one of the most popular open-source relational database management systems. It is widely used by developers and organizations to store and manage data. In this blog post, we will discuss some of the essential PostgreSQL commands that you should know.

Connect to the Postgres server

psql

The psql command is used to access the PostgreSQL interactive terminal. You can use it to connect to a database and execute SQL commands. To use psql, you need to provide the database name, user, and password.

psql -U <sampleuser> -h <localhost>
psql -U <username> -d <database_name> -h <hostname> -p <port_number>
# Example
psql -U postgres -h localhost

Create User

CREATE USER command is used to create a new user

CREATE USER <name>;
# Example
CREATE USER newuser;

Create Database

The CREATE DATABASE command is used to create a new database.

CREATE DATABASE <db_name>;
# Example
CREATE DATABASE sales;

Alter User

Alter user used to set a password to the user

CREATE USER <name> WITH PASSWORD '<password>';
# Example
ALTER USER newuser WITH PASSWORD '12345';

Grant & Revoke

The grant statement grants permissions on a database to a user or a group of users.

GRANT <permission> ON <db_name> TO <user or group>;
# Example
GRANT ALL PRIVILEGES ON DATABASE "sales" to "postgres"

The Revoke statement revokes permissions on a database to a user or a group of users

REVOKE <permission(s)> ON <object> FROM <user or group>;
# Examples
revoke ALL PRIVILEGES ON DATABASE "sales" from "postgres"

Create Table

The CREATE TABLE command is used to create a new table.

CREATE TABLE table_name(
   column datatype,
   column datatype,
   PRIMARY KEY( one or more columns )
);
# Example
CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name TEXT
);

Drop Database

The DROP DATABASE command is used to delete a database.

DROP DATABASE <db_name>;
# Example
DROP DATABASE mydatabase;

Drop Table

The DROP TABLE command is used to delete a table.

DROP TABLE <table_name>;
# Example
DROP TABLE mytable;

Drop User

THE DROP USER command is used to delete a user

DROP USER <user_name>;
# Example
DROP USER newuser;

Sure, here's a blog post on the top PostgreSQL commands:

Other Commands

# connect to database
\c <db_name>
# list all Database
\d
# list all table from database
\dt
# list all users
\du
# schema of table
\d <table_name>
# psql commands
\?
# help 
\h
# exit from psql
\q

These are some of the most used PostgreSQL commands. You may manage your PostgreSQL database more successfully and efficiently by becoming familiar with these commands.