# PostgreSQL Cheat Sheet - Basic Commands

> This blog presented a cheat sheet that assists us in working with the Postgres databases, schemas, tables, and users/roles, efficiently.

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](<https://www.commandprompt.com/education/how-to-create-a-database-in-postgresql/>) 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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-cheat-sheet-databases-schemas-tables-users/)