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;