Postgresql Cheat Sheet
Tags: beginners, database, postgressql, programming
Published: 2023-07-21 19:00:06

## General Commands:

1. **Connection**
```sh
psql -h localhost -d johto -U silver
```
Prompt: `Password for user silver:` _(Enter your password and press enter)_

2. **List Databases**
```sh
\l
```
Prompt: 
```sh
                              List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 johto     | myuser   | UTF8     | C.UTF-8 | C.UTF-8 |
 hoenn     | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)
```

3. **List Tables**
```sh
\dt
```
Prompt: 
```sh
          List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | user | table | silver
(1 row)
```

---
**User and Role Management:**
---
1. **Create User**
```sql
CREATE USER silver WITH PASSWORD 'totodileRulez';
```
Prompt: `CREATE ROLE`

2. **Grant Privileges**
```sql
GRANT ALL PRIVILEGES ON DATABASE johto TO silver;
```
Prompt: `GRANT`

3. **Create Role**
```sql
CREATE ROLE trainer WITH LOGIN PASSWORD 'totodileRulez';
```
Prompt: `CREATE ROLE`

---
**Database Actions:**
---
1. **Create Database**
```sql
CREATE DATABASE johto;
```
Prompt: `CREATE DATABASE`

2. **Drop Database**
```sql
DROP DATABASE johto;
```
Prompt: `DROP DATABASE`

---
**Query Methods:**
---
1. **Select All**
```sql
SELECT * FROM pokemon;
```
Prompt: 
```sh
 id |   name   
----+----------
  1 | Teddiursa
  2 | Sunkern
(2 rows)
```

2. **Where Clause**
```sql
SELECT * FROM pokemon WHERE name = 'Espeon';
```
Prompt: 
```sh
 id |   name   
----+----------
  1 | Espeon
(1 row)
```

---
**Complex Commands:**
---
1. **Join Tables**
```sql
SELECT * FROM pokemon INNER JOIN moves ON pokemon.id = moves.pokemon_id;
```
Prompt: 
```sh
 pokemon_id | name    | move_id | move  
------------+---------+---------+----------
       1 | Teddiursa|        1 | Tackle
       2 | Sunkern |        2 | Vine Whip
(2 rows)
```

---
**More Query Methods:**
---
3. **Max**
```sql
SELECT MAX(level) FROM pokemon;
```
Prompt:
```sh
 max
-----
  100
(1 row)
```

4. **Min**
```sql
SELECT MIN(level) FROM pokemon;
```
Prompt:
```sh
 min
-----
  1
(1 row)
```

5. **Avg**
```sql
SELECT AVG(level) FROM pokemon;
```
Prompt:
```sh
        avg
---------------------
 50.5000000000000000
(1 row)
```

6. **Sum**
```sql
SELECT SUM(level) FROM pokemon;
```
Prompt:
```sh
 sum
-----
 101
(1 row)
```

7. **Group By**
```sql
SELECT COUNT(*), type FROM pokemon GROUP BY type;
```
Prompt:
```sh
 count |   type   
-------+-------------
    2 | Dark
    3 | Steel
(2 rows)
```

8. **Order By**
```sql
SELECT * FROM pokemon ORDER BY level DESC;
```
Prompt:
```sh
 id |   name    | level | type 
----+-----------+-----+---------
  1 | Unown|  100 | Psychic
  3 | Snubbul|  33 | Fairy
  2 | Ursaring|  1 | Normal
(3 rows)
```

---
**More Complex Commands:**
---
2. **Subquery**
```sql
SELECT * FROM (SELECT * FROM pokemon ORDER BY level DESC LIMIT 2) AS subquery;
```
Prompt:
```sh
 id |   name    | level | type 
----+-----------+-----+---------
  1 | Chikorita |  100 | Grass
  3 | Cyndaquil |  33 | Fire
(2 rows)
```

3. **Create Index**
```sql
CREATE INDEX idx_pokemon_level ON pokemon(level);
```
Prompt: `CREATE INDEX`

4. **Create View**
```sql
CREATE VIEW high_level_pokemon AS SELECT * FROM pokemon WHERE level > 50;
```
Prompt: `CREATE VIEW`

5. **Select from View**
```sql
SELECT * FROM high_level_pokemon;
```
Prompt:
```sh
 id |   name    | level | type 
----+-----------+-----+---------
  1 | Totodile  |  100 | Water
(1 row)
```

---
**More Query Methods:**
---
9. **Select Distinct**
```sql
SELECT DISTINCT type FROM pokemon;
```
Prompt:
```sh
  type  
-----------
 Grass
 Fire
(2 rows)
```

10. **Like Operator**
```sql
SELECT * FROM pokemon WHERE name LIKE '%quil%';
```
Prompt:
```sh
 id |   name    | level | type 
----+-----------+-----+---------
  1 | Cyndaquil |  100 | Fire
  2 | Quilava |  1 | Fire
(2 rows)
```

---
**Advanced Complex Commands:**
---
6. **Right Join**
```sql
SELECT * FROM moves RIGHT JOIN pokemon ON pokemon.id = moves.pokemon_id;
```
Prompt:
```sh
 pokemon_id | name  | move_id | move  
------------+-------+---------+----------
       1 | Chikorita|        1 | Tackle
       2 | Bayleef |        2 | Vine Whip
       3 | Cyndaquil |     NULL | NULL
(3 rows)
```

7. **Full Outer Join**
```sql
SELECT * FROM moves FULL OUTER JOIN pokemon ON pokemon.id = moves.pokemon_id;
```
Prompt:
```sh
 pokemon_id | name  | move_id | move  
------------+-------+---------+----------
       1 | Chikorita|        1 | Tackle
       2 | Bayleef |        2 | Vine Whip
       3 | Cyndaquil |     NULL | NULL
(3 rows)
```

8. **Self Join**
```sql
SELECT A.name, B.name FROM pokemon A, pokemon B WHERE A.type = B.type AND A.name != B.name;
```
```sh
   name    |   name   
-----------+----------
 Chikorita| Bayleef 
 Bayleef | Chikorita
(2 rows)
```

---
**Function and Procedures:**
---
1. **Creating Functions**
```sql
CREATE FUNCTION increase_level(integer, integer) RETURNS integer AS $$
BEGIN
   RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;
```
Prompt: `CREATE FUNCTION`

2. **Calling Functions**
```sql
SELECT increase_level(1, 2);
```
Prompt:
```sh
 increase_level 
----------------
              3
(1 row)
```

3. **Creating Procedures**
```sql
CREATE PROCEDURE archive_old_pokemon() AS $$
BEGIN
   DELETE FROM pokemon WHERE level < 10;
END;
$$ LANGUAGE plpgsql;
```
Prompt: `CREATE PROCEDURE`

4. **Calling Procedures**
```sql
CALL archive_old_pokemon();
```
Prompt: `CALL`

5. **Creating Triggers**
```sql
CREATE TRIGGER check_level_before_insert 
BEFORE INSERT ON pokemon 
FOR EACH ROW 
WHEN (NEW.level > 100) 
DO 
$$ 
BEGIN 
   RAISE EXCEPTION 'Level cannot be more than 100'; 
END; 
$$ LANGUAGE plpgsql;
```
Prompt: `CREATE TRIGGER`

---
**Error Handling:**
---
PostgreSQL returns errors with specific SQLSTATE values. When an error occurs, an exception is thrown with an associated SQLSTATE value.

For example, if you attempt to create a table that already exists:

```sql
CREATE TABLE pokemon(id SERIAL PRIMARY KEY, name VARCHAR(100));
```

You might get an error like:

```sh
ERROR:  relation "pokemon" already exists
```

In this case, you would need to either drop the existing table or change the name of the table you are trying to create.