PostgreSQL Commands Cheat Sheet
Tags: webdev, programming, database, postgres
Published: 2024-01-18 19:32:52



### 1. Connect to PostgreSQL:

```bash
psql -U username -d database_name -h host -p port
```

**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:

```sql
\l
```

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

### 3. Create a Database:

```sql
CREATE DATABASE database_name;
```

**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:

```sql
DROP DATABASE database_name;
```

**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:

```sql
\c database_name;
```

**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:

```sql
\dt
```

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

### 7. Create a Table:

```sql
CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
);
```

**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:

```sql
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
```

**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:

```sql
SELECT * FROM table_name;
```

**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:

```sql
UPDATE table_name SET column1 = new_value WHERE condition;
```

**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:

```sql
DELETE FROM table_name WHERE condition;
```

**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:

```sql
CREATE INDEX index_name ON table_name (column);
```

**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:

  ```bash
  pg_dump -U username -d database_name > backup.sql
  ```

**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:

  ```bash
  psql -U username -d database_name < backup.sql
  ```

**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](https://www.postgresql.org/docs/) for more advanced features and detailed explanations.