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.