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, Postgres has become most developers' first choice.

Postgres 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 Database Operations - Cheat Sheet
• PostgreSQL Schemas Operations - Cheat Sheet
• PostgreSQL Table Operations - Cheat Sheet
• PostgreSQL Users Operations - Cheat Sheet

So, let’s start with the database operations!

PostgreSQL Database Operations - Cheat Sheet

In PostgreSQL, you can perform various database operations, such as create, alter, drop, etc. To do that, different commands are used in Postgres.

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;

Specify the name of a database to be created in place of “db_name”.

Access/Connect Database

Execute the “\c” command(from psql) followed by the database name to which you want to establish a connection:

\c db_name;

Drop Database

The DROP DATABASE command allows us to delete/drop a particular database:

DROP DATABASE db_name;

Alter Database

Use the ALTER DATABASE command to modify a particular database in Postgres. This command allows us to 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:

ALTER DATABASE db_name
OWNER TO new_owner_name | current_user |current_role | session_user;

Rename Database

Use the following syntax to rename a database in Postgres:

ALTER DATABASE old_db_name 
RENAME TO new_db_name;

Alter Tablespace

The ALTER DATABASE statement allows us to change the default tablespace of a database:

ALTER DATABASE db_name
SET TABLESPACE new_tablespace_name;

Alter Database Attributes

Using ALTER DATABASE command, you can modify the attributes of the database, such as connection limit, connections allowed, etc. For this purpose, you must use the below syntax:

ALTER DATABASE db_name
WITH option;

In place of “option”, you can specify CONNECTION LIMIT, ALLOW_CONNECTIONS, or IS_TEMPLATE.

Alter Configuration Variables

Use the ALTER DATABASE statement with a SET clause to override the settings of a particular database:

ALTER DATABASE db_name
SET configuration_parameter = value;

RESET Configuration Parameter

Execute the ALTER DATABASE command with RESET option to rest the configuration parameter:

ALTER DATABASE db_name
RESET configuration_parameter;

List Databases

Execute the “\l” or “\l+” command from psql to show the list of databases:

\l;

PostgreSQL Schema Operations - Cheat Sheet

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

Create Schema

Use the CREATE SCHEMA statement to define/create a new schema in a database:

CREATE SCHEMA [IF NOT EXISTS] new_schema;

Specify the schema’s name in place of “new_schema”.

Drop Schema

A schema can be dropped using the DROP SCHEMA statement, as shown in the following snippet:

DROP SCHEMA schema_name 
[RESTRICT | CASCADE];

Alter Schema

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

Rename Schema

The ALTER SCHEMA command enables us to rename a schema; to do that, the ALTER SCHEMA command must be executed with the RENAME TO clause:

ALTER SCHEMA schema_name 
RENAME TO new_schema_name;

Change Owner

Run the ALTER SCHEMA command along with the OWNER TO clause to change the schema’s owner:

ALTER SCHEMA schema_name
OWNER TO{ new_owner_name | SESSION_USER | CURRENT_USER};

Show Schemas

Execute the “\dn” command from “psql” to get the list of available schemas:

\dn;

PostgreSQL Table Operations - Cheat Sheet

Postgres offers several commands to perform different table operations, such as create table, drop table, select table’s data, etc.

Create Table

Specify the CREATE TABLE statement followed by the table name to be created:

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

Postgres offers a DROP TABLE statement to delete/drop a particular table:

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

In Postgres, the ALTER TABLE command is used to modify an already existing table. Using ALTER TABLE command, you can add or drop columns and constraints, rename a table or column, and modify the column type.

Rename Table

Exercise the ALTER TABLE command with the RENAME TO clause to rename a table:

ALTER TABLE tab_name
RENAME TO new_tab_name;

Add Column

The ALTER TABLE command must be used with the ADD COLUMN clause to add a new column to a table:

ALTER TABLE tab_name
ADD COLUMN new_col_name data_type constraint;

In place of constraint, you can specify any constraint of your choice, such as a PRIMARY KEY, UNIQUE, NOT NULL, etc.

Drop Column

Use the ALTER TABLE command with the DROP COLUMN clause to drop a column from the targeted table:

ALTER TABLE tab_name
DROP COLUMN col_name;

Use the comma-separated syntax to drop more than one column of a table.

Add Constraint

Use the following syntax to add a particular constraint using the ALTER TABLE command:

ALTER TABLE tab_name
ALTER COLUMN col_name SET constraint_name;

Drop/Remove Constraint

Run the ALTER TABLE command with the DROP clause to drop/remove a specific constraint from a table:

ALTER TABLE tab_name
ALTER COLUMN col_name DROP constraint_name;

Alter Column Type

Postgres allows us to modify the data type of an existing column via the ALTER TABLE statement:

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;

Rename Table Columns

Use the “ALTER TABLE” command with the “RENAME COLUMN” clause to modify the column’s name:

ALTER TABLE tab_name
RENAME COLUMN old_col_name TO new_col_name;

Fetch Table’s Data

Use the SELECT command to get/fetch the table’s data:

SELECT * FROM tab_name;

List Tables

Run the “\dt” command from SQL Shell to get the list of available tables in a particular database:

\dt;

PostgreSQL Users Operations - Cheat Sheet

Here is the list of some significant statements regarding Postgres users:

Create User

To create a user in Postgres, 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

Specify the CREATE USER statement along with the SUPERUSER attribute to create a user with superuser privileges:

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 Postgres, fetch the “usename” column of the “pg_user” table using the SELECT query:

SELECT usename, usesysid
FROM pg_user;

Find Logged in Users

A system view named “pg_stat_activity” is used to find the currently logged-in users Postgres:

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;

Rename Users

Use the ALTER USER statement along with the RENAME TO clause to rename a user in Postgres:

ALTER USER user_name
RENAME TO new_user_name;

Change User Password

Execute the ALTER USER statement along with the PASSWORD attribute to modify the user’s password:

ALTER USER user_name
WITH PASSWORD 'updated_password';

Change Password Validity Date

To change the password expiry date, you must use the ALTER TABLE command with the “PASSWORD” attribute and “VALID UNTIL” option:

ALTER USER user_name
WITH PASSWORD 'updated_password'
VALID UNTIL 'expiry_date_time';

Change User to Superuser

Changing an ordinary user to a superuser requires the ALTER USER command, user name, and the SUPERUSER attribute:

ALTER USER existing_user_name 
WITH SUPERUSER;

Alter User Permissions

To grant any privileges to a user, you must execute the ALTER USER command along with the privileges to be assigned, such as “CREATEDB”, “CREATEROLE”, etc.

ALTER USER user_name 
WITH user_privileges;

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.