Ezekiel

Posted on May 18 • Edited on Jun 3

PostgreSQL Cheat Sheet

#webdev #database #postgres #sql

General Commands

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

\copy (SELECT * FROM student) to 
'C:/path/to/destination/file.csv' DELIMITER ',' CSV HEADER;

Creating Tables

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

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

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

Logical Operators

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

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

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

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

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

ALTER TABLE student ADD UNIQUE(studentName);

Adding primary key constraint

Adding primary key constraint After defining the table

ALTER TABLE student ADD PRIMARY KEY(studentID);

Adding foriegn key constraint

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;

ALTER TABLE tablename ADD CONSTRAINT constraintName constraintType(columnName) ;

Selecting Stuff

From student table;

Selecting all

SELECT * FROM student;

Selecting a particular column

SELECT (studentName, studentGPA) FROM student;

Selecting by a condition

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

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

Using `between` Keyword

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

The `in` Keyword

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

The `like` Keyword

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

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

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

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

Count

counts the no of occurences

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

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

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

Min

Finds the minimum value in a column

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

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

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

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

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.

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

Ordering

We can order in Ascending or Descending form.

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

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

SELECT * FROM student FETCH FIRST 60 ROW ONLY; --pretty self explanatory

Dropping Stuff

Dropping relations

Very risky in production ⚠

DROP TABLE student;

Dropping Constraints

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

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

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

ALTER TABLE student ADD COLUMN email VARCHAR(40);

Date and Timestamps

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 😁

Top comments (2)

takeshikriang • May 21

Very helpful, thanks.

Ezekiel • May 24

⭐