Day 5 PSQL- Union ,CAST

PostgreSQL UNION and CAST Operators in Detail

UNION Operator

The UNION operator in PostgreSQL is used to combine the result sets of two or more SELECT statements into a single result set.

Basic Syntax

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

Key Characteristics

1. Duplicate Elimination: UNION automatically removes duplicate rows from the result set.
2. Column Matching: The number and order of columns must be the same in all SELECT statements.
3. Data Type Compatibility: Corresponding columns must have compatible data types.

UNION ALL Variant

If you want to keep duplicates, use UNION ALL (which is faster as it doesn't check for duplicates):

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

Example

SELECT product_name FROM current_products
UNION
SELECT product_name FROM discontinued_products;

CAST Operator

The CAST operator converts a value from one data type to another.

Basic Syntax

CAST(expression AS target_type)

Or using the alternative syntax:

expression::target_type

Common Use Cases

1. String to Number Conversion:

   SELECT CAST('123' AS INTEGER);
   SELECT '123'::INTEGER;

1. Number to String Conversion:

   SELECT CAST(123 AS VARCHAR);
   SELECT 123::VARCHAR;

1. Date/Time Conversions:

   SELECT CAST('2023-01-15' AS DATE);
   SELECT NOW()::DATE;

CAST with UNION

When using UNION, you may need CAST to ensure type compatibility:

SELECT product_id, product_name FROM products
UNION
SELECT CAST(customer_id AS VARCHAR), customer_name FROM customers;

In this example, we cast customer_id (likely an integer) to VARCHAR to match the product_id column type.

Handling Type Mismatches

Without proper casting, you might get errors like:

ERROR:  UNION types text and integer cannot be matched

Solutions:

1. Explicitly cast columns to matching types
2. Use a common supertype (like TEXT for strings and numbers)

Advanced Example

SELECT 
    'Product' AS item_type,
    product_id::TEXT AS id,
    product_name AS name
FROM products
UNION
SELECT 
    'Customer' AS item_type,
    customer_id::TEXT AS id,
    customer_name AS name
FROM customers
ORDER BY item_type, name;

Performance Considerations

1. UNION vs UNION ALL: Use UNION ALL when you know there are no duplicates or when duplicates don't matter.
2. CAST Overhead: Type conversions add processing overhead - minimize unnecessary casts.
3. Index Usage: Casting in WHERE clauses can prevent index usage (e.g., WHERE CAST(column AS TEXT) = 'value').

Practical Tips

1. Always specify column names explicitly in UNION queries for clarity.
2. Use CTEs (WITH clauses) to make complex UNION queries more readable.
3. Test type compatibility before running large UNION operations.
4. Consider using COALESCE or NULL for missing columns when UNIONing tables with different structures.

Remember that proper type handling is crucial when combining data from different sources with UNION in PostgreSQL.