DEV Community

Cover image for PostgreSQL Cheat Sheet
Ezekiel
Ezekiel

Posted on • Updated on

PostgreSQL Cheat Sheet

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)
Enter fullscreen mode Exit fullscreen mode

Example of \copy statement to .csv file

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

Creating Tables

CREATE TABLE TABLENAME(
columnname1 DATATYPE constraints DEFAULT defaultvalue, 
columnname2 DATATYPE....);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Arithmetic Operators

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

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
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Adding a unique constraint

To insert the rule that a student name should be unique

ALTER TABLE student ADD UNIQUE(studentName);
Enter fullscreen mode Exit fullscreen mode

Adding primary key constraint

Adding primary key constraint After defining the table

ALTER TABLE student ADD PRIMARY KEY(studentID);
Enter fullscreen mode Exit fullscreen mode

Adding foriegn key constraint

ALTER TABLE student ADD FOREIGN KEY (courseID) REFERENCES course(courseID);
Enter fullscreen mode Exit fullscreen mode

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) ;
Enter fullscreen mode Exit fullscreen mode

Selecting Stuff

From student table;

Selecting all

SELECT * FROM student;
Enter fullscreen mode Exit fullscreen mode

Selecting a particular column

SELECT (studentName, studentGPA) FROM student;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

You can also use logical conditions

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

Using between Keyword

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

The in Keyword

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

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
Enter fullscreen mode Exit fullscreen mode

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_
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The alias keyword

The alias keyword renames a column.

SELECT studentID, studentName AS firstClassStudents FROM student where studentGPA > 4.5;
Enter fullscreen mode Exit fullscreen mode

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%'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The FETCH keyword is also another way to limit

SELECT * FROM student FETCH FIRST 60 ROW ONLY; --pretty self explanatory
Enter fullscreen mode Exit fullscreen mode

Dropping Stuff

Dropping relations

Very risky in production ⚠

DROP TABLE student;
Enter fullscreen mode Exit fullscreen mode

Dropping Constraints

ALTER TABLE student DROP CONSTRAINT constraintName
Enter fullscreen mode Exit fullscreen mode

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!!)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Adding Columns

ALTER TABLE student ADD COLUMN email VARCHAR(40);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

What other PostgreSQL tips and tricks did I miss 😁

Top comments (2)

Collapse
 
takeshikriang profile image
takeshikriang

Very helpful, thanks.

Collapse
 
ezekiel_77 profile image
Ezekiel