Skip to main content

PostgreSQL

Introduction

This cheat sheet provides a quick reference for some common PostgreSQL commands and concepts. PostgreSQL is a powerful open-source relational database management system (RDBMS) used for storing and managing data.

Installation

To use PostgreSQL, you need to install it on your system. Installation methods vary depending on your operating system. Refer to the official PostgreSQL documentation for installation instructions.

PostgreSQL Concepts

Databases

  • Create a new database:

    CREATE DATABASE database_name;
  • List all databases:

    \l
  • Connect to a specific database:

    \c database_name
  • Delete a database (Be cautious, this will remove all data):

    DROP DATABASE database_name;

Tables

  • Create a new table:

    CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    );
  • List all tables in the current database:

    \dt
  • Describe the structure of a table:

    \d table_name
  • Delete a table:

    DROP TABLE table_name;

CRUD Operations

  • Insert a new record into a table:

    INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
  • Select data from a table:

    SELECT * FROM table_name WHERE condition;
  • Update data in a table:

    UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  • Delete data from a table:

    DELETE FROM table_name WHERE condition;

Indexes

  • Create an index on a table column:

    CREATE INDEX index_name ON table_name (column_name);
  • List all indexes on a table:

    \di

Joins

  • Perform an INNER JOIN between two tables:
    SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Functions

  • Use built-in PostgreSQL functions:
    SELECT COUNT(*) FROM table_name;

Users and Permissions

  • Create a new PostgreSQL user:

    CREATE USER username WITH PASSWORD 'password';
  • Grant privileges to a user:

    GRANT privilege_type ON TABLE table_name TO username;
  • Revoke privileges from a user:

    REVOKE privilege_type ON TABLE table_name FROM username;

PostgreSQL Command-Line Tool

  • Log in to PostgreSQL using the command-line tool:

    psql -U username -d database_name -h hostname
  • Execute SQL commands in batch mode:

    psql -U username -d database_name -a -f script.sql

Conclusion

This cheat sheet covers some common PostgreSQL commands and concepts. PostgreSQL offers a wide range of features and functionality; refer to the PostgreSQL documentation for more in-depth information and advanced usage.