PostgreSQL Tutorial

• PostgreSQL - Introduction
• Install PostgreSQL on Windows
• Install PostgreSQL on Mac
• PostgreSQL - Loading a Database
• PostgreSQL - Create Database
• PostgreSQL - ALTER DATABASE
• PostgreSQL - DROP DATABASE
• PostgreSQL - Rename Database
• PostgreSQL - Copy Database
• PostgreSQL - Size of a Database
• PostgreSQL - Show Databases

Data Types

• PostgreSQL - Data Types
• PostgreSQL - Boolean Data Type
• PostgreSQL - CHAR Data Type
• PostgreSQL - VARCHAR Data Type
• PostgreSQL - TEXT Data Type
• PostgreSQL - NUMERIC Data Type
• PostgreSQL - SMALLINT Integer Data Type
• PostgreSQL - INTEGER Data Type
• PostgreSQL - BIGINT Integer Data Type
• PostgreSQL - Date Data Type
• PostgreSQL - Timestamp Data Type
• PostgreSQL - UUID Data Type
• PostgreSQL - TIME Data Type
• PostgreSQL - Interval Data Type
• PostgreSQL - User-defined Data Type
• PostgreSQL - Array Data Type
• PostgreSQL - hstore Data Type
• PostgreSQL - JSON Data Type

Querying & Filtering Data

• PostgreSQL - SELECT
• PostgreSQL - SELECT DISTINCT clause
• PostgreSQL - ORDER BY clause
• PostgreSQL - WHERE clause
• PostgreSQL - LIMIT clause
• PostgreSQL - FETCH clause
• PostgreSQL - IN operator
• PostgreSQL - IS NULL operator
• PostgreSQL - LIKE operator
• PostgreSQL - NOT LIKE operator
• PostgreSQL - BETWEEN operator
• PostgreSQL - HAVING clause
• PostgreSQL - GROUP BY clause

Managing Tables

• PostgreSQL - CREATE TABLE
• PostgreSQL - SELECT INTO
• PostgreSQL - Create Auto-increment Column using SERIAL
• PostgreSQL - CREATE SEQUENCE
• PostgreSQL - Identity Column
• PostgreSQL - ALTER TABLE
• PostgreSQL - Rename Table
• PostgreSQL - ADD COLUMN
• PostgreSQL - DROP COLUMN
• PostgreSQL - Change Column Type
• PostgreSQL - RENAME COLUMN
• PostgreSQL - DROP TABLE
• PostgreSQL - TRUNCATE TABLE
• PostgreSQL - Temporary Table
• PostgreSQL - Temporary table name
• PostgreSQL - Removing Temporary Table
• PostgreSQL - Copy a Table
• PostgreSQL - Comparing Tables
• PostgreSQL - Describe Table
• PostgreSQL - Show Tables
• PostgreSQL - Import CSV File Into Table

Modifying Data

• PostgreSQL - INSERT
• PostgreSQL - Insert multiple rows
• PostgreSQL - UPDATE
• PostgreSQL - DELETE
• PostgreSQL - Upsert

Conditionals

• PostgreSQL - CASE
• PostgreSQL - COALESCE
• PostgreSQL - NULLIF() Function
• PostgreSQL - CAST

Control Flow

• PostgreSQL - IF Statement
• PostgreSQL - CASE Statement
• PostgreSQL - Loop Statement
• PostgreSQL - While Loops
• PostgreSQL - For Loops
• PostgreSQL - Exit
• PostgreSQL - Continue

Transactions & Constraints

• PostgreSQL - Transactions
• PostgreSQL - COMMIT
• PostgreSQL - BEGIN
• PostgreSQL - ROLLBACK
• PostgreSQL - Primary Key
• PostgreSQL - Foreign Key
• PostgreSQL - CHECK Constraint
• PostgreSQL - UNIQUE Constraint
• PostgreSQL - NOT NULL Constraint

Working with JOINS & Schemas

• PostgreSQL - Joins
• PostgreSQL - LEFT JOIN
• PostgreSQL - INNER JOIN
• PostgreSQL - FULL OUTER JOIN
• PostgreSQL - SELF JOIN
• PostgreSQL - Schema
• PostgreSQL - CREATE SCHEMA
• PostgreSQL - DROP SCHEMA
• PostgreSQL - ALTER SCHEMA

Roles & Permissions

• PostgreSQL - CREATE ROLE
• PostgreSQL - ALTER ROLE
• PostgreSQL - DROP ROLE
• PostgreSQL - GRANT
• PostgreSQL - REVOKE
• PostgreSQL - Role Membership

Working with Sets

• PostgreSQL - UNION operator
• PostgreSQL - INTERSECT Operator
• PostgreSQL - EXCEPT Operator
• PostgreSQL - GROUPING SETS
• PostgreSQL - CUBE
• PostgreSQL - ROLLUP

Subquery & CTEs

• PostgreSQL - ANY Operator
• PostgreSQL - ALL Operator
• PostgreSQL - EXISTS Operator
• PostgreSQL - CTE
• PostgreSQL - Deleting Duplicate Rows using Subquery

User-defined Functions

• PostgreSQL - CREATE FUNCTION Statement
• PostgreSQL - Function parameter modes
• PostgreSQL - Function Overloading
• PostgreSQL - Function that returns table
• PostgreSQL - Drop Function

Important In-Built Functions

• PostgreSQL - AVG() Function
• PostgreSQL - COUNT() Function
• PostgreSQL - MAX() Function
• PostgreSQL - MIN() Function
• PostgreSQL - SUM() Function
• PostgreSQL - FIRST_VALUE Function
• PostgreSQL - LAST_VALUE Function
• PostgreSQL - NTH_VALUE Function
• PostgreSQL - ROW_NUMBER Function
• PostgreSQL - CURRENT_DATE Function
• PostgreSQL - CURRENT_TIME Function
• PostgreSQL - EXTRACT Function
• PostgreSQL- CONCAT Function
• PostgreSQL - FORMAT Function
• PostgreSQL - UPPER function
• PostgreSQL- LOWER function
• PostgreSQL - REGEXP_MATCHES Function
• PostgreSQL - REGEXP_REPLACE Function
• PostgreSQL - REPLACE Function

PostgreSQL PL/pgSQL

• PostgreSQL - Dollar-Quoted String Constants
• PostgreSQL - Block Structure

Variables & Constants

• PostgreSQL - Variables
• PostgreSQL - Select Into
• PostgreSQL - Row type variables
• PostgreSQL - Record type variable
• PostgreSQL - Constants

Stored Procedures

• PostgreSQL - Introduction to Stored Procedures
• PostgreSQL - CREATE PROCEDURE
• PostgreSQL - Drop Procedure

Working with Triggers

• PostgreSQL - Trigger
• PostgreSQL - CREATE TRIGGER
• PostgreSQL - DROP TRIGGER
• PostgreSQL - ALTER TRIGGER
• PostgreSQL - Disabling a Trigger
• PostgreSQL - Enabling a Trigger

Working with Views & Indexes

• PostgreSQL - CREATE INDEX
• PostgreSQL - DROP INDEX
• PostgreSQL - List Indexes
• PostgreSQL - UNIQUE Index
• PostgreSQL - Partial Index
• PostgreSQL - Index On Expression
• PostgreSQL - REINDEX
• PostgreSQL - Multicolumn Indexes

Errors & Exception Handling

• PostgreSQL - Errors and Messages
• PostgreSQL - Exception Handling
• PostgreSQL - Assert

PostgreSQL - UNION operator

The UNION operator in PostgreSQL is used to combine the result sets of two or more SELECT statements. The combined result set will include all rows from both queries, but it will eliminate duplicate rows unless otherwise specified.

Here are some essential points and considerations about the UNION operator in PostgreSQL:

1. Basic Usage:

   SELECT column1, column2 FROM table1
   UNION
   SELECT column1, column2 FROM table2;
   

2. Requirements:

   • The number and order of columns in the SELECT statements must be the same.
   • The data types of the corresponding columns must be compatible, meaning they should either be the same or convertible to one another.

3. Eliminating Duplicates:

   By default, the UNION operator removes duplicates from the combined result set. If you want to keep all duplicates, use the UNION ALL operator:

   SELECT column1, column2 FROM table1
   UNION ALL
   SELECT column1, column2 FROM table2;
   

   Using UNION ALL can be faster since the database doesn't have to search for and eliminate duplicates.

4. Ordering:

   If you want to order the combined result set, you can add an ORDER BY clause at the end:

   SELECT column1, column2 FROM table1
   UNION
   SELECT column1, column2 FROM table2
   ORDER BY column1;
   

   Note: You cannot have an ORDER BY clause for individual SELECT statements within a UNION, but you can have one for the entire combined result set.

5. Limiting Results:

   You can use the LIMIT and OFFSET clauses after the UNION to limit the combined result set:

   SELECT column1, column2 FROM table1
   UNION
   SELECT column1, column2 FROM table2
   ORDER BY column1
   LIMIT 10 OFFSET 5;
   

6. Combining with Other Operators:

   In addition to UNION, PostgreSQL also supports other set operators like INTERSECT (returns only the rows that appear in both result sets) and EXCEPT (returns rows from the first query that aren't present in the second query).

7. Use Cases:

   The UNION operator is useful in scenarios where you want to retrieve related data from multiple tables and present it as a single result set. For instance, you might have separate tables for storing user details from different sources (e.g., users_from_source1, users_from_source2) but want a single list of all users.

8. Performance Consideration:

   • Since UNION eliminates duplicates, it might involve additional processing. When you're sure that the result sets don't overlap or when duplicates are acceptable, UNION ALL can offer better performance.

In summary, the UNION operator in PostgreSQL allows for combining result sets from multiple SELECT queries into a single set, eliminating duplicates by default. Proper knowledge of its usage and nuances can help in creating efficient and effective queries.

1. PostgreSQL UNION operator example: Use the UNION operator to combine the results of two or more SELECT statements:

   SELECT column1 FROM table1
   UNION
   SELECT column1 FROM table2;
   

2. How to use UNION operator in PostgreSQL: The UNION operator is used to combine the results of two or more SELECT statements into a single result set.

3. Combine results of two queries in PostgreSQL: Combine results using the UNION operator:

   SELECT column1 FROM table1
   UNION
   SELECT column1 FROM table2;
   

4. PostgreSQL multiple SELECT statements with UNION: Combine multiple SELECT statements using UNION:

   SELECT column1 FROM table1
   UNION
   SELECT column1 FROM table2
   UNION
   SELECT column1 FROM table3;
   

5. Ordering results with UNION in PostgreSQL: Order the combined result set using the ORDER BY clause:

   SELECT column1 FROM table1
   UNION
   SELECT column1 FROM table2
   ORDER BY column1;
   

6. Filtering data in UNION operator in PostgreSQL: Use a WHERE clause to filter data in each SELECT statement:

   SELECT column1 FROM table1 WHERE condition1
   UNION
   SELECT column1 FROM table2 WHERE condition2;
   

7. Limiting rows in UNION in PostgreSQL: Use LIMIT to restrict the number of rows in the combined result set:

   SELECT column1 FROM table1
   UNION
   SELECT column1 FROM table2
   LIMIT 10;
   

8. UNION operator and data types in PostgreSQL: Ensure that the data types in corresponding columns of the SELECT statements match for a successful UNION.