PostgreSQL is an advanced, open-source, highly stable relational database that extends the standard SQL language. It offers a variety of features, such as user-defined types, Point-in-time recovery, table inheritance, and many more. Due to its extensive features, PostgreSQL has become most developers' first choice.

PostgreSQL offers various commands/statements to work with databases, schemas, tables, roles, etc. This blog post will cover the basics of PostgreSQL concerning the below-listed concepts:

• PostgreSQL Databases - Cheat Sheet
• PostgreSQL Schemas - Cheat Sheet
• PostgreSQL Table - Cheat Sheet
• PostgreSQL Views - Cheat Sheet
• PostgreSQL Users - Cheat Sheet
• PostgreSQL Indexes - Cheat Sheet

So, let’s start with the database operations!

PostgreSQL Databases - Cheat Sheet

A database in Postgres is the topmost hierarchical level for managing database objects, like tables, views, indexes, etc. We can perform various operations on a database, such as create, alter, drop, etc. For this purpose, different commands are used.

Create Database

To create a new database in Postgres, use the CREATE DATABASE command followed by the database name to be created:

CREATE DATABASE db_name;

Access/Connect Database

To connect to a databases, execute the “\c” command(from psql) followed by the database name:

\c db_name;

Drop Database

To drop a database, use the DROP DATABASE command, as follows:

DROP DATABASE db_name;

Alter Database

To alter a database, use the ALTER DATABASE command. Using this command, you can change the database owner, rename the database, change database attributes, etc.

Change Database Owner

To change the database owner, users need to execute the ALTER DATABASE command as follows:

--To Change Database Owner
ALTER DATABASE db_name
OWNER TO new_owner_name | current_user |current_role | session_user;

--To Rename a Database
ALTER DATABASE old_db_name 
RENAME TO new_db_name;

--To Alter a Tablespace
ALTER DATABASE db_name
SET TABLESPACE new_tablespace_name;

-- To Alter Database Attributes
ALTER DATABASE db_name
WITH option;

-- To Alter Configuration Variables
ALTER DATABASE db_name
SET configuration_parameter = value;

-- To RESET Configuration Parameter
ALTER DATABASE db_name
RESET configuration_parameter; 

-- To List all Databases
\l

PostgreSQL Schemas - Cheat Sheet

Postgres provides different commands to work with schemas, such as CREATE SCHEMA, DROP SCHEMA, etc.

Create Schema

To create a schema, use the CREATE SCHEMA statement as follows:

CREATE SCHEMA [IF NOT EXISTS] new_schema;

Drop Schema

To drop a schema, run the DROP SCHEMA statement with one of the following options:

DROP SCHEMA schema_name 
[RESTRICT | CASCADE];

Alter Schema

The ALTER SCHEMA command is used in Postgres to modify the schema’s definition. This command/statement allows us to rename a schema, change the schema’s owner, etc.

--To Rename a Schema
ALTER SCHEMA schema_name 
RENAME TO new_schema_name;

--To Change Schema Owner
ALTER SCHEMA schema_name
OWNER TO{ new_owner_name | SESSION_USER | CURRENT_USER};

--TO Show All Available Schemas
\dn

PostgreSQL Tables - Cheat Sheet

Postgres offers several commands to perform different table operations, such as creating a table, dropping a table, selecting a table’s data, etc.

Create Table

To create a new table, run the CREATE TABLE statement followed by the table name to be created, as follows:

CREATE TABLE [IF NOT EXISTS] tab_name (
column_name DATATYPE column_contraint,
);

“IF NOT EXISTS” is an optional clause that creates a new table only if it doesn’t exist already. Specify the column names followed by their respective data types and constraints.

Drop Table

To drop a table in Postgres, run the DROP TABLE statement with the following syntax:

DROP TABLE [IF EXISTS] tab_name 
[RESTRICT | CASCADE];

“IF EXISTS”, “RESTRICT”, and “CASCADE” are optional clauses. The “IF EXISTS” option drops a table if it already exists, the RESTRICT option denies the table deletion in case some objects depend on it, while the CASCADE option deletes a table along with its dependent objects.

Alter Table

To modify the table’s definition in Postgres, run the ALTER TABLE command as follows:

--To Rename a Table
ALTER TABLE tab_name
RENAME TO new_tab_name;

--To Add new Columns
ALTER TABLE tab_name
ADD COLUMN new_col_name data_type constraint;

--To Drop a Table Column
ALTER TABLE tab_name
DROP COLUMN col_name;

--To Add a Constraint to a Table
ALTER TABLE tab_name
ALTER COLUMN col_name SET constraint_name;

--To Drop/Remove a Constraint
ALTER TABLE tab_name
ALTER COLUMN col_name DROP constraint_name;

--To Alter Column Data Type
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;

--To Rename Table Columns
ALTER TABLE tab_name
RENAME COLUMN old_col_name TO new_col_name;

--To Add a Primary Key
ALTER TABLE tab_name 
ADD PRIMARY KEY col_name;

--To Remove a Primary Key
ALTER TABLE tab_name 
DROP CONSTRAINT primary_key_constraint_name;

Fetch the Table’s Data

To fetch the table’s data, use the SELECT command, as follows:

--To Fetch All Columns
SELECT * FROM tab_name;

--To Fetch Specific Columns
SELECT col_1, col_2, ...
FROM tab_name;

Insert Data Into a Table

To insert data into a table, use the INSERT INTO query as follows:

INSERT INTO tab_name(col_list)
VALUES (value_list);

Delete Table Records

To delete records from a table, run the following query:

--To Delete all Records
DELETE FROM tab_name;

--To Delete Specific Records
DELETE FROM tab_name
WHERE condition;

List Tables

To list all available tables, run the “\dt” command from SQL Shell, like this:

\dt

PostgreSQL Views - Cheat Sheet

In PostgreSQL views are the virtual tables that are used to simplify complex queries. Postgres supports different types of views, such as materialized views, recursive views, etc. We can create, alter, or remove a view using different commands.

Create a View

To create a new view in Postgres, run one of the following CREATE VIEW statements:

--To Create a Standard View
CREATE OR REPLACE viewName AS
query;

--To Create a Recursive View
CREATE RECURSIVE VIEW viewName(col_list) AS
SELECT col_list;

--To Create a Materialized View
CREATE MATERIALIZED VIEW viewName 
AS
query
WITH <NO> DATA;

--To refresh a Postgres MATERIALIZED view
REFRESH MATERIALIZED VIEW CONCURRENTLY viewName;

Drop a View

To drop a view run the below-given commands:

DROP VIEW <IF EXISTS> viewName;
--To drop a materialized view
DROP MATERIALIZED VIEW viewName;

Rename a View

To rename a Postgres view, execute the following command:

ALTER VIEW viewName RENAME TO new_viewName;

PostgreSQL Users - Cheat Sheet

PostgreSQL users manage the database access and privileges. We can create new users, alter them when needed, or even drop them (when no longer needed).

Create User

To create a user in PostgreSQL, use the CREATE ROLE or CREATE USER command followed by the role/user name to be created:

CREATE USER use_name 
WITH option;

Any privilege of your choice, such as SUPERUSER, LOG-IN, CREATEDB, CREATEROLE, CONNECTION LIMIT, PASSWORD, VALID UNTIL, etc., can replace the option.

Create Superuser

To create a superuser, run the CREATE USER statement along with the SUPERUSER attribute, as follows:

CREATE USER user_name 
WITH SUPERUSER;

Drop User

To drop any particular user in Postgres, specify the DROP USER statement followed by the user name to be dropped:

DROP USER [IF EXISTS] user_name;

Find Users

To find the users in PostgreSQL, fetch the “usename” column of the “pg_user” table using the SELECT query, as follows:

SELECT usename, usesysid
FROM pg_user;

Find Logged in Users

To find the currently logged-in Postgres users, use the SELECT query with a system view named “pg_stat_activity”, as shown below:

SELECT DISTINCT usename, usesysid
FROM pg_stat_activity;

List Users

Execute the “\du” or “\du+” commands from the SQL Shell to find the list of users:

\du;

Alter Users

To change the user’s definition, use the ALTER USER command with the suitable clause, as seen in the following snippet:

--To Rename a User
ALTER USER user_name
RENAME TO new_user_name;

--To Change User Password
ALTER USER user_name
WITH PASSWORD 'updated_password';

--To Change Password Validity Date
ALTER USER user_name
WITH PASSWORD 'updated_password'
VALID UNTIL 'expiry_date_time';

--To Change a Normal User to a Superuser
ALTER USER existing_user_name
WITH SUPERUSER;

--To Alter User Permissions
ALTER USER user_name
WITH user_privileges;

PostgreSQL Indexes - Cheat Sheet

Indexes improve the efficiency of the PostgreSQL database by finding and retrieving a table record quickly. To create an index in Postgres, simply employ the following syntax:

CREATE [UNIQUE] INDEX indexName
ON tableName (column_list);

Similarly, you can drop/remove an index using its name, as shown below:

DROP INDEX indexName;

That’s all from this Postgres blog.

Conclusion

PostgreSQL offers a wide range of commands to work with databases, schemas, tables, and users/roles. For instance, CREATE command creates a database, schema, table, or a role/user; the ALTER command modifies a database, schema, table, or a user/role, etc. This blog presented a cheat sheet that assists us in working with the Postgres databases, schemas, tables, and users/roles, efficiently.