Learn PostgreSQL

Learn PostgreSQL

PostgreSQL is a powerful, advanced, fast, and open source object-relational database system based on POSTGRES, Version 4.2.

PostgreSQL supports a large part of the SQL standard with many modern features such as complex queries, triggers, updatable views, transactional integrity, multiversion concurrency control, etc. Additionally, PostgreSQL can be extended by the user by adding new data types, functions operators, aggregate functions, index methods, and procedural languages.

Basics

  1. Install PostgreSQL
  2. Connect to PostgreSQL DB
  3. Create Database in PostgreSQL
  4. Create Table in PostgreSQL Database
  5. Copy Table
  6. Drop Table in PostgreSQL Database
  7. Drop Database in PostgreSQL
  8. Truncate Table in PostgreSQL
  9. SELECT Statement in PostgreSQL
  10. WHERE Clause
  11. GROUP BY Clause
  12. HAVING Clause
  13. ORDER BY Clause
  14. DISTINCT Clause
  15. Inner Join
  16. Left Outer Join
  17. Right Outer Join
  18. Full Outer Join
  19. Self Join
  20. Natural Join
  21. Cross Join
  22. LIMIT OFFSET Clause
  23. GROUPING SETS
  24. GROUPING() Function
  25. GROUP BY CUBE
  26. GROUP BY ROLLUP
  27. Sub Query
  28. CASE Expressions
  29. ALL Operator
  30. ANY Operator
  31. UNION Operator
  32. INTERSECT Operator
  33. EXCEPT Operator
  34. IS NULL, IS NOT NULL Operator
  35. BETWEEN Operator
  36. LIKE Operator
  37. CAST Operator
  38. Sequence in PostgreSQL
  39. Serial Type
  40. Create Identity Columns
  41. Create Computed Columns

Constraints

  1. Constraints in PostgreSQL
  2. NOT NULL Constraint
  3. Unique Constraint
  4. Check Constraint
  5. Primary Key
  6. Foreign Key

DML Statements

  1. ALTER Table in PostgreSQL
  2. Rename Table in PostgreSQL
  3. Rename Columns of a Table in PostgreSQL
  4. Add Columns in a Table in PostgreSQL
  5. Modify Column Type in PostgreSQL
  6. Set Default Value of Column
  7. Remove Columns from a Table in PostgreSQL
  8. Add Constraints to Table in PostgreSQL
  9. Insert Data into Table in PostgreSQL
  10. Upsert (Insert or Update) Data in PostgreSQL
  11. Update Data in PostgreSQL
  12. Delete Data in PostgreSQL

Functions

  1. NULLIF()
  2. COALESCE()
  3. GREATEST(), LEAST()
  4. WITH Queries (CTE)

Data Types

  1. PostgreSQL Data Types
  2. Boolean Type
  3. Character Type
  4. Integer Type
  5. Numeric Type
  6. Date Type
  7. Time Type
  8. TimeStamp Type
  9. Interval Type
  10. Array Type
  11. Json Type