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 thelike
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)
Very helpful, thanks.
⭐