PostgreSQL Cheat Sheet – Pedagogy Zone

By
On:

A PostgreSQL cheat sheet is a quick reference guide that provides a summary of commonly used commands and operations in PostgreSQL, a powerful open-source relational database management system. It typically includes essential SQL commands, psql (PostgreSQL command-line tool) commands, and other useful information for database administrators and developers working with PostgreSQL.

SELECT Query

SELECT col1, col2
FROM table
JOIN table2 ON table1.col = table2.col
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY col1 ASC|DESC;

SELECT Keywords

DISTINCT: Removes
duplicate results
SELECT DISTINCT product_name
FROM product;
BETWEEN: Matches a
value between two
other values (inclusive)
SELECT product_name
FROM product
WHERE price BETWEEN 50 AND 100;
IN: Matches to any of
the values in a list
SELECT product_name
FROM product
WHERE category IN
('Electronics', 'Furniture');
LIKE: Performs
wildcard matches using
_ or %
SELECT product_name
FROM product
WHERE product_name
LIKE '%Desk%";

Joins

SELECT t1.*, t2.*
FROM t1
join_type t2 ON t1.col = t2.col;

Join SQL

INNER JOIN: show all matching
records in both tables.
SQL table 1
LEFT JOIN: show all records from left table, and any matching records from right table.
RIGHT JOIN: show all records from
right table, and any matching records from left table.
SQL Table 3
FULL JOIN: show all records from
both tables, whether there is a match or not.
SQL Table 4

CASE Statement

Simple Case

CASE name
WHEN 'John' THEN 'Name John'
WHEN 'Steve' THEN 'Name Steve'
ELSE 'Unknown'
END

Searched Case

CASE
WHEN name='John' THEN 'Name John'
WHEN name='Steve' THEN 'Name Steve'
ELSE 'Unknown'
END

Common Table Expression

WITH queryname AS (
SELECT col1, col2
FROM firsttable)
SELECT col1, col2..
FROM queryname...;

Modifying Data

Insert INSERT INTO tablename
(col1, col2...)
VALUES (val1, val2);
Insert from a
Table
INSERT INTO tablename
(col1, col2...)
SELECT col1, col2...
Insert Multiple
Rows
INSERT INTO tablename
(col1, col2...) VALUES
(valA1, valB1),
(valA2, valB2),
(valA3, valB3);
Update UPDATE tablename
SET col1 = val1
WHERE condition;
Update with
a Join
UPDATE t
SET col1 = val1
FROM tablename t
INNER JOIN table x
ON t.id = x.tid
WHERE condition;
Delete DELETE FROM tablename
WHERE condition;

Indexes

Create Index CREATE INDEX indexname
ON tablename (cols);
Drop Index DROP INDEX indexname;

Set Operators

UNION: Shows unique
rows from two result sets.
set operator
UNION ALL: Shows all
rows from two result sets.
set operator
INTERSECT: Shows rows that
exist in both result sets.
set operators
EXCEPT: Shows rows that exist
in the first result set but not the second.
set operaor in sql

Aggregate Functions

SUM: Finds a total of the numbers provided
COUNT: Finds the number of records
AVG: Finds the average of the numbers provided
MIN: Finds the lowest of the numbers provided
MAX: Finds the highest of the numbers provided

Common Functions

  • LENGTH(string): Returns the length of the provided string
  • POSITION(string IN substring): Returns the position of the
    substring within the specified string.
  • CAST(expression AS datatype): Converts an expression into the
    specified data type.
  • NOW: Returns the current date, including time.
  • CEIL(input_val): Returns the smallest integer greater than the
    provided number.
  • FLOOR(input_val): Returns the largest integer less than the
    provided number.
  • ROUND(input_val, [round_to]): Rounds a number to a specified
    number of decimal places.
  • TRUNC(input_value, num_decimals): Truncates a number to a
    number of decimals.
  • REPLACE(whole_string, string_to_replace, replacement_string):
    Replaces one string inside the whole string with another string.
  • SUBSTRING(string, [start_pos], [length]): Returns part of a value,
    based on a position and length.

Create Table

Create Table CREATE TABLE tablename (
column_name data_type
);

Create Table with Constraints

CREATE TABLE tablename (
column_name data_type NOT NULL,
CONSTRAINT pkname PRIMARY KEY (col),
CONSTRAINT fkname FOREIGN KEY (col)
REFERENCES other_table(col_in_other_table),
CONSTRAINT ucname UNIQUE (col),
CONSTRAINT ckname CHECK (conditions)
);

Create Temporary
Table
CREATE TEMP TABLE tablename (
colname datatype
);
Drop Table DROP TABLE tablename;

Alter Table

Add Column ALTER TABLE tablename ADD COLUMN
columnname datatype;
Drop Column ALTER TABLE tablename DROP COLUMN
columnname;
Modify Column ALTER TABLE tablename ALTER COLUMN
columnname TYPE newdatatype;
Rename Column ALTER TABLE tablename RENAME COLUMN
currentname TO newname;
Add Constraint ALTER TABLE tablename ADD CONSTRAINT
constraintname constrainttype
(columns);
Drop Constraint ALTER TABLE tablename DROP
constraint_type constraintname;
Rename Table ALTER TABLE tablename
RENAME TO newtablename;

Window/Analytic Functions

function_name ( arguments ) OVER (
[query_partition_clause] [ORDER BY order_by_clause
[windowing_clause] ] )

Example using RANK, showing the student details and their rank according to the fees_paid, grouped by gender:

SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (
PARTITION BY gender ORDER BY fees_paid
) AS rank_val
FROM student;

Subqueries

Single Row SELECT id, last_name, salary
FROM employee
WHERE salary = (
SELECT MAX(salary)
FROM employee
);
Multi Row SELECT id, last_name, salary
FROM employee
WHERE salary IN (
SELECT salary
FROM employee
WHERE last_name LIKE 'C%'
);
Read More Topics
Oracle SQL cheat sheet
MySQL cheat sheet
SQL server cheat sheet

Santhakumar Raja

Hello The goal of this blog is to keep students informed about developments in the field of education. encourages pupils to improve as writers and readers.

For Feedback - techactive6@gmail.com

Leave a Comment