General Commands
? — 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
‘C:/path/to/destination/file.csv’ DELIMITER ‘,’ CSV HEADER;
Creating Tables
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
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
4 – 2 –2 Subtraction
6 * 8 –48 Multiplication
72 / 9 –8 Division
2 ^ 5 –32 Exponential
Logical Operators
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
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
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
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
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
Adding primary key constraint
Adding primary key constraint After defining the table
Adding foriegn key constraint
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;
Selecting Stuff
From student table;
Selecting all
Selecting a particular column
Selecting by a condition
SELECT * FROM student WHERE(studentGPA <> 5.0); — list all students that DID NOT have a 5.0 GPA
You can also use logical conditions
Using between Keyword
The in Keyword
–Select all students that are name is one of the above ^
The like Keyword
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
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
Aggegrate Functions
Aggegrate functions compute single data from a set of values especially a column e.g
FN(columnName) –Use function on a particular column
Count
counts the no of occurences
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
Sum
Mostly used for column arguments. sums the values in a column
Min
Finds the minimum value in a column
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 * 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
Round
Round takes an extra argument and that is the number to round it to
SELECT ROUND(3923.9212); –3924
The having keyword
Its like a WHERE clause for the GROUP BY keyword
The alias keyword
The alias keyword renames a column.
Ordering
We can order in Ascending or Descending form.
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 OFFSET 8 LIMIT 15; –jump the first 8 rows and display only 15 rows
The FETCH keyword is also another way to limit
Dropping Stuff
Dropping relations
Very risky in production ⚠
Dropping Constraints
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 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 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
Date and Timestamps
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 😁