General commands

1. Connect to particular database:

\c database_name; or psql -d database_name -U username -W

2. View all database:

\ list; or \l

3. View tables in the current database:

\ dt;

4. View Users:

\ du;

5. Check Current Database:

SELECT current_database();

6.See Current User:

SELECT current_user;

7. Current Connection Info:

\conninfo

Database Management

1. Create new database in Postgresql:

CREATE DATABASE database_name;

2. Drop the database in Postgresql:

DROP DATABASE database_name;

3. Rename the database in Postgresql:

ALTER DATABASE old_name RENAME TO new_name;

Table Management

1. Create new table in Postgresql:

CREATE TABLE table_name ( column1 datatype, column2 datatype, ...);

2. Drop a table in Postgresql:

DROP TABLE table_name;

3. Rename a table in Postgresql:

ALTER TABLE old_table_name RENAME TO new_table_name;

4. Add a column to a table in Postgresql:

ALTER TABLE table_name ADD COLUMN column_name datatype;

5. Drop a column to a table in Postgresql:

ALTER TABLE table_name DROP COLUMN column_name;

5. Show all columns of a table in Postgresql:

\d table_name

User Management

1. Create a new user in Postgresql:

CREATE USER username WITH PASSWORD 'password';

2. Drop a new user in Postgresql:

DROP USER username;

3. Drop a new user in Postgresql:

DROP USER username;

4. Alter a new user in Postgresql:

ALTER USER username WITH PASSWORD 'new_password';

5. Grant Privileges to a User in Postgresql:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

6. Revoke Privileges to a User in Postgresql:

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;

Schema Management

1. Create a New Schema in Postgresql:

CREATE SCHEMA schema_name;

2. Drop a Schema in Postgresql:

DROP SCHEMA schema_name CASCADE;

3. Set a Schema Search Path Postgresql:

SET search_path TO schema_name;

View Management

1. Create a View in Postgresql:

CREATE VIEW view_name AS SELECT column1, column2, ...FROM table_name WHERE condition;

2. Drop a View in Postgresql:

DROP VIEW view_name;

3. List All Views in Postgresql:

\dv

Backup and Restore

1. Backup a Database in Postgresql:

pg_dump database_name > backup_file.sql

1. Restore a Database in Postgresql:

psql database_name < backup_file.sql

Permissions and Roles

1. Create a Role in Postgresql:

CREATE ROLE role_name;

2. Grant Role to a User in Postgresql:

GRANT role_name TO username;

3. Revoke Role to a User in Postgresql:

REVOKE role_name FROM username;

Data Manipulation Language (DML)

Inserting Records into a Table
1. Insert a Single Record in Postgresql:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

example:


INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Amit', 'Kumar', 'amittest@gmial.com', 500000);

2. Insert multiple Records in Postgresql:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
(value1_1, value1_2, value1_3, ...),
(value2_1, value2_2, value2_3, ...),
...;

example:


INSERT INTO employees (first_name, last_name, email, salary)
VALUES 
('Jane', 'Smith', 'jane.smith@example.com', 60000),
('Mark', 'Johnson', 'mark.johnson@example.com', 55000);

3. Insert default Record in Postgresql:

INSERT INTO table_name DEFAULT VALUES;

Updating Records in a Table
1. Update a Single Record in Postgresql:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

1. Update Multiple Records in Postgresql:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

ex:


UPDATE employees
SET salary = salary + 5000
WHERE department_id = 3;


Comments(0)