DEV Community

Cover image for PostgreSQL Commands Cheat Sheet
Francisco Inoque
Francisco Inoque

Posted on

PostgreSQL Commands Cheat Sheet

1. Connect to PostgreSQL:

psql -U username -d database_name -h host -p port
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • psql: Command-line tool for interacting with PostgreSQL.
  • -U username: Specifies the PostgreSQL username for authentication.
  • -d database_name: Specifies the name of the database to connect to.
  • -h host: Specifies the host where the PostgreSQL server is running.
  • -p port: Specifies the port number on which the PostgreSQL server is listening.

2. List Databases:

\l
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • \l: Meta-command in the psql interactive terminal to list all available databases.

3. Create a Database:

CREATE DATABASE database_name;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • CREATE DATABASE: SQL command to create a new PostgreSQL database.
  • database_name: Replace with the desired name for the new database.

4. Drop a Database:

DROP DATABASE database_name;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • DROP DATABASE: SQL command to delete an existing PostgreSQL database.
  • database_name: Replace with the name of the database to be dropped.

5. Connect to a Database:

\c database_name;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • \c: Meta-command in the psql interactive terminal to connect to a specific database.
  • database_name: Replace with the name of the database to connect to.

6. List Tables:

\dt
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • \dt: Meta-command to list all tables in the currently connected database.

7. Create a Table:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • CREATE TABLE: SQL command to create a new table in the database.
  • table_name: Name of the table to be created.
  • (column1 data_type, column2 data_type, ...): Define the columns and their data types.

8. Insert Data:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • INSERT INTO: SQL command to insert data into a table.
  • table_name: Name of the table to insert data into.
  • (column1, column2, ...): Specify the columns to insert data.
  • VALUES (value1, value2, ...): Provide the corresponding values for the columns.

9. Select Data:

SELECT * FROM table_name;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • SELECT *: SQL command to retrieve all columns from a table.
  • FROM table_name: Specify the table from which to retrieve data.

10. Update Data:

UPDATE table_name SET column1 = new_value WHERE condition;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • UPDATE: SQL command to modify existing data in a table.
  • table_name: Name of the table to be updated.
  • SET column1 = new_value: Specify the column to be updated and its new value.
  • WHERE condition: Define the condition to identify the rows to be updated.

11. Delete Data:

DELETE FROM table_name WHERE condition;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • DELETE FROM: SQL command to remove data from a table.
  • table_name: Name of the table from which to delete data.
  • WHERE condition: Define the condition to identify the rows to be deleted.

12. Create an Index:

CREATE INDEX index_name ON table_name (column);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • CREATE INDEX: SQL command to create an index on a table.
  • index_name: Name of the index to be created.
  • ON table_name: Specify the table on which to create the index.
  • (column): Define the column on which the index is created.

13. Backup and Restore:

  • To perform a backup:
  pg_dump -U username -d database_name > backup.sql
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • pg_dump: Command-line tool to create a backup of a PostgreSQL database.
  • -U username: Specifies the PostgreSQL username for authentication.
  • -d database_name: Specifies the name of the database to be backed up.
  • > backup.sql: Redirects the backup output to a file named "backup.sql".

  • To restore from a backup:

  psql -U username -d database_name < backup.sql
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • psql: Command-line tool to interact with PostgreSQL.
  • -U username: Specifies the PostgreSQL username for authentication.
  • -d database_name: Specifies the name of the database to be restored.
  • < backup.sql: Reads the backup file and restores the database.

These commands are fundamental for working with PostgreSQL databases. Explore the PostgreSQL Documentation for more advanced features and detailed explanations.

Top comments (1)

Collapse
 
jordan_gamba profile image
Jordan Gamba

Good article