PostgreSQL Cheat Sheet
Tags: webdev, database, postgres, sql
Published: 2024-05-18 00:04:31

## General Commands

```sql
-- This is a comment

\? -- more help

\l -- list all databases

\c databaseName -- connect to databaseName

\dt -- Show all tables

\d tablename -- describe tablename

\i -- load query from .sql file (accepts only absolute paths)

\copy -- Copy outputs to file (accepts only absolute paths)
```

> Example of  `\copy` statement to `.csv` file

```sql
\copy (SELECT * FROM student) to 
'C:/path/to/destination/file.csv' DELIMITER ',' CSV HEADER;
```
---
## Creating Tables

```sql
CREATE TABLE TABLENAME(
columnname1 DATATYPE constraints DEFAULT defaultvalue, 
columnname2 DATATYPE....);
```

#### Common Datatypes and their default Values

| Data Type        | SQL Representation | Default Value     | Example                                  |
| ---------------- | ------------------ | ----------------- | ---------------------------------------- |
| uuid             | `UUID`             | `gen_random_uuid` | `'2c69d018-9484-4b69-b737-2c55778614e8'` |
| varchar          | `VARCHAR(n)`       |                   | `'Hello World'`                          |
| char             | `CHAR(n)`          |                   | `'H'`                                    |
| integer          | `INTEGER`          | 0                 | `1234`                                   |
| smallint         | `SMALLINT`         | 0                 | `123`                                    |
| bigint           | `BIGINT`           | 0                 | `1234567890123456789`                    |
| real             | `REAL`             | 0.0               | `123.45`                                 |
| double precision | `DOUBLE PRECISION` | 0.0               | `123.4567890123456789`                   |
| boolean          | `BOOLEAN`          | `false`           | `true`                                   |
| date             | `DATE`             | `NOW()::date`     | `'2023-03-10'`                           |
| time             | `TIME`             | `NOW()::time`     | `'12:34:56'`                             |
| timestamp        | `TIMESTAMP`        | `NOW()`           | `'2023-03-10 12:34:56'`                  |
#### Conditions

```sql
4 > 3 --greater than
2 < 3 --lesser than
2 >= 1 --greater than or equal to
5 <= 17 --lesser than or equal to
1 <> 2 --not equal to
```

#### Arithmetic Operators

```sql
2 + 3 --5 Addition
4 - 2 --2 Subtraction
6 * 8 --48 Multiplication
72 / 9 --8 Division
2 ^ 5 --32 Exponential
```
#### Logical Operators

```sql
studentGPA > 3.5 and studentName LIKE 'A%' --Both conditions must be true
gender = 'Male' or height > 5 --Either one of them must be true
```
### Certification Class

Here's a table of students in a certification class 

| studentID                              | studentName | studentAge | studentGPA |
| -------------------------------------- | ----------- | ---------- | ---------- |
| `e32fb977-a282-460f-82b8-ed4eb9a87dae` | Alice       | 18         | 4.5        |
Here's how  we define the table

```sql
CREATE TABLE student(
studentID uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), 
studentName VARCHAR(30) NOT NULL, 
studentAge INT, 
studentGPA DECIMAL
);
```

#### Inserting Foriegn Keys

A table named `Course` with `CourseID` and `CourseName`

| CourseID                               | CourseName   |
| -------------------------------------- | ------------ |
| `2fc1c282-6be6-495f-bca0-a3f56793cbda` | Biology(111) |

That is described like this

```sql
CREATE TABLE course(
courseID uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), 
courseName VARCHAR(70) NOT NULL
);
```


Lets add an additional column named `courseID` in the student relation as a `foriegn key`

```sql
CREATE TABLE student(
studentID uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
studentName VARCHAR(30) NOT NULL, 
studentAge INT, 
studentGPA DECIMAL, 

courseID uuid FOREIGN KEY REFERENCES course(courseID)); --<<-

--i.e. columnname datatype FORIEGN KEY REFERENCES relation(primary_key)
```
---
###  Inserting Constraints

#### Adding a check constraint

The  __Max GPA__ for any student is __5.0__. To enforce that in our student database

```sql
ALTER TABLE student 
ADD CONSTRAINT 
gpa_check 
CHECK(studentGPA >= 0.0 and studentGPA <= 5.0);
```

#### Adding a unique constraint

To insert the rule that a student name should be unique

```sql
ALTER TABLE student ADD UNIQUE(studentName);
```

#### Adding primary key constraint

Adding primary key constraint **After defining the table**

```sql
ALTER TABLE student ADD PRIMARY KEY(studentID);
```

#### Adding foriegn key constraint

```sql
ALTER TABLE student ADD FOREIGN KEY (courseID) REFERENCES course(courseID);
```


>This works only when they've not be defined
>Also using this method allows __PostgreSQL__ to name the constraints for us. If you dont like that;

```sql
ALTER TABLE tablename ADD CONSTRAINT constraintName constraintType(columnName) ;
```
---
# Selecting Stuff

From student table;

#### Selecting all

```sql
SELECT * FROM student;
```

#### Selecting a particular column

```sql
SELECT (studentName, studentGPA) FROM student;
```

#### Selecting by a condition

```sql
SELECT * FROM student WHERE(condition); -- Treat column names like variables e.g

SELECT * FROM student WHERE(studentGPA <> 5.0); -- list all students that DID NOT have a 5.0 GPA
```


>You can also use logical conditions

```sql
SELECT * FROM student WHERE(studentGPA = 5.0 and studentName LIKE "A%");
```

#### Using `between` Keyword

```sql
SELECT * FROM student WHERE studentGPA BETWEEN 3.0 and 4.5; -- Capturing all second class students
```

#### The `in` Keyword

```sql
SELECT * FROM student WHERE studentName IN ('Alice', 'Barnabas','Wolfgang','Putin');
--Select all students that are name is one of the above ^
```

#### The `like` Keyword

```sql
SELECT * FROM student WHERE studentName LIKE 'A%' --select all students with names starting with 'A'
SELECT * FROM student WHERE studentName LIKE '%el'--select all students with names ending 'el' e.g. Daniel, Samuel, Joel
```

Imagine there is an email column where students have email like `john@hotmail.com` ,`alicegres@gmail.com`,`patrick@gmail.edu`

```sql
SELECT * FROM student WHERE studentEmail LIKE '%mail%' --select all users from _mail_
```


>The `ilike` keyword is the same as the `like` keyword but is case __Insensitive__

#### The `group by` keyword

The `group by` keyword groups data according to a column

```sql
SELECT columnName FROM table GROUP BY columnName; --Mostly works for aggegrate functions
```
---
#### Aggegrate Functions

Aggegrate functions compute single data from a set of values especially a column e.g

```sql
FN(*) --Use function on entire relation
FN(columnName) --Use function on a particular column
```

##### Count

counts the no of occurences 

```sql
SELECT COUNT(*) FROM student; --Counts the number of rows in the relation
SELECT COUNT(studentGPA) FROM student WHERE studentGPA > 4.0; --Counts the NO of GPA's that are greater than 4.0
```

`COUNT` can also be used with `group by` 

```sql
SELECT studentGPA,COUNT(*) FROM student GROUP BY studentGPA; --displays GPA and the number of students that have them
```

##### Sum

Mostly used for column arguments. sums the values in a column

```sql
SELECT SUM(studentGPA) FROM student; --displays the sum of all the gpa's
```

##### Min

Finds the minimum value in a column

```sql
SELECT MIN(studentGPA) FROM student; --displays the lowest GPA in the student table
SELECT * FROM student WHERE studentGPA = (SELECT MIN(studentGPA) from  student); --displays the students with the lowest GPA
```

##### Max

Finds the maximum value in a column

```sql
SELECT MAX(studentGPA) FROM student; --displays the highest GPA in the student table
SELECT * FROM student WHERE studentGPA = (SELECT MAX(studentGPA) from student); --displays the students with the highest GPA
```

##### Avg

Finds the average of values in a column

```SQL
SELECT AVG(studentGPA) FROM student; --displays the average af all gpa's in the student table
```

##### Round

Round takes an extra argument and that is the number to round it to

```sql
SELECT ROUND(2.344343,2); --2.34
SELECT ROUND(3923.9212); --3924
```

### The `having` keyword

Its like a `WHERE` clause for the `GROUP BY` keyword

```sql
SELECT COUNT(studentGPA) FROM student GROUP BY studentGPA HAVING studentGPA > 2.0; --displays the studentGPA that are greater than 2.0 and the number of students that have it
```

#### The `alias` keyword

The alias keyword renames a column.

```sql
SELECT studentID, studentName AS firstClassStudents FROM student where studentGPA > 4.5;
```

#### Ordering

We can order in __Ascending__ or __Descending__ form.

```sql
SELECT * FROM student ORDER BY studentGPA ASC; --displays all rows with ascending GPA
SELECT * FROM student ORDER BY studentName DESC; --displays all rows starting from 'Z%' --> 'A%'
```

#### Limit and Offset

We can limit the output number of rows to a particular number

```sql
SELECT * FROM student LIMIT 10; --display only 10 rows;
SELECT * FROM student OFFSET 8 LIMIT 15; --jump the first 8 rows and display only 15 rows
```

The `FETCH` keyword is also another way to limit

```sql
SELECT * FROM student FETCH FIRST 60 ROW ONLY; --pretty self explanatory
```
---
## Dropping Stuff

### Dropping relations

Very risky in production ⚠

```sql
DROP TABLE student;
```

### Dropping Constraints

```sql
ALTER TABLE student DROP CONSTRAINT constraintName
```

> First describe the table `\d student` to see the name of the constraint

### Dropping rows

It is advisable to drop rows based on the primary keys because it uniquely identifies the row

```sql
DELETE FROM student WHERE studentID = '0d5b8325-698c-43a2-b497-b0564d23c49d';
DELETE FROM student WHERE studentGPA < 1.0 --Delete all students with less than 1.0 GPA
DELETE FROM student; --delete all rows (BE CAREFUL!!)
```
---
## Updating Stuff

### Updating Records

```sql
UPDATE student SET studentName = 'Daniel' WHERE studentID = '03fdf13c-d11d-4155-b54c-762596eac70a'; --Changes the name to Daniel
UPDATE student SET studentGPA = 5.0; --Sets all student GPA to 5.0(BE CAREFULL!!)

UPDATE student SET studentName = 'Julia', studentGPA = 4.3 WHERE studentID = 'b6c562a2-6342-44ee-881c-5a04533bffab'; --Updates multiple columns
```
---
#### Adding Columns

```sql
ALTER TABLE student ADD COLUMN email VARCHAR(40);
```

## Date and Timestamps

```sql
select NOW(); --2024-03-10 17:16:33.263697+01
select NOW()::date --2024-03-10 <<- Date only
select NOW()::time -- 17:17:39.453662 <<- Time only
```

What other PostgreSQL  tips and tricks did I miss  😁