PostgreSQL Operators

Operators are essential symbols or keywords that perform operations on one or more expressions in PostgreSQL. Understanding operators is fundamental to writing effective queries and manipulating data efficiently.

Introduction

In PostgreSQL, operators allow you to perform various operations on data, from simple arithmetic calculations to complex pattern matching. They form the building blocks of expressions used in your SQL queries and help transform, filter, and manipulate data to get the results you need.

This guide will walk you through the main categories of PostgreSQL operators and provide practical examples of how to use them effectively.

Arithmetic Operators

Arithmetic operators perform mathematical calculations on numeric data types.

Operator	Description	Example	Result
+	Addition	SELECT 5 + 3;	8
-	Subtraction	SELECT 5 - 3;	2
*	Multiplication	SELECT 5 * 3;	15
/	Division	SELECT 5 / 2;	2.5
%	Modulo (remainder)	SELECT 5 % 2;	1
^	Exponentiation	SELECT 2 ^ 3;	8
`	/`	Square root	`SELECT
`		/`	Cube root
!	Factorial	SELECT 5!;	120

Practical Example

Let's say we have a table called products with columns for price and quantity. We can use arithmetic operators to calculate the total value:

-- Calculate total value of each product
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total_value
FROM 
    products;

Result:

 product_name | price | quantity | total_value 
--------------+-------+----------+-------------
 Keyboard     | 29.99 |       10 |      299.90
 Mouse        | 15.50 |       20 |      310.00
 Monitor      | 149.99|        5 |      749.95

Comparison Operators

Comparison operators compare values and return boolean results (true/false).

Operator	Description	Example	Result
=	Equal	SELECT 5 = 5;	t
<> or !=	Not equal	SELECT 5 <> 3;	t
<	Less than	SELECT 3 < 5;	t
>	Greater than	SELECT 5 > 3;	t
<=	Less than or equal	SELECT 3 <= 3;	t
>=	Greater than or equal	SELECT 3 >= 3;	t

Using Comparison Operators in WHERE Clauses

-- Find all expensive products (price > 100)
SELECT 
    product_name,
    price
FROM 
    products
WHERE 
    price > 100;

Result:

 product_name | price  
--------------+--------
 Monitor      | 149.99
 Laptop       | 899.99
 Printer      | 199.50

Logical Operators

Logical operators combine boolean values.

Operator	Description	Example	Result
AND	Logical AND	SELECT (true AND false);	f
OR	Logical OR	SELECT (true OR false);	t
NOT	Logical NOT	SELECT NOT false;	t

Complex Filtering with Logical Operators

-- Find products with price between $50 and $200
SELECT 
    product_name,
    price
FROM 
    products
WHERE 
    price >= 50
    AND price <= 200;

Result:

 product_name | price  
--------------+--------
 Monitor      | 149.99
 Printer      | 199.50
 Headphones   |  89.99

String Operators

PostgreSQL offers several operators for working with text data.

Operator	Description	Example	Result
||	String concatenation	SELECT 'Hello' || ' ' || 'World';	Hello World
LIKE	Pattern matching with wildcards	SELECT 'apple' LIKE 'app%';	t
ILIKE	Case-insensitive LIKE	SELECT 'Apple' ILIKE 'app%';	t
~	Matches regex, case-sensitive	SELECT 'apple' ~ '^a';	t
~*	Matches regex, case-insensitive	SELECT 'Apple' ~* '^a';	t
!~	Does not match regex, case-sensitive	SELECT 'apple' !~ '^b';	t
!~*	Does not match regex, case-insensitive	SELECT 'Apple' !~* '^b';	t

Finding Products by Name Pattern

-- Find all products containing "phone" in their name
SELECT 
    product_name,
    price
FROM 
    products
WHERE 
    product_name ILIKE '%phone%';

Result:

 product_name | price 
--------------+-------
 Smartphone   | 499.99
 Headphones   |  89.99
 Microphone   |  59.99

JSON Operators

PostgreSQL has powerful JSON operators for working with JSON data.

Operator	Description	Example	Result
->	Get JSON array element or object field as JSON	SELECT '{"name":"John"}'::json->'name';	"John"
->>	Get JSON array element or object field as text	SELECT '{"name":"John"}'::json->>'name';	John
#>	Get JSON object at the specified path	SELECT '{"user":{"name":"John"}}'::json#>'{user,name}';	"John"
#>>	Get JSON object at the specified path as text	SELECT '{"user":{"name":"John"}}'::json#>>'{user,name}';	John

Working with JSON Data

-- Assuming we have a table with JSON data
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO user_profiles (data) VALUES 
    ('{"name": "Alice", "contact": {"email": "[email protected]", "phone": "123-456-7890"}}'),
    ('{"name": "Bob", "contact": {"email": "[email protected]", "phone": "098-765-4321"}}');

-- Query to extract email addresses
SELECT 
    data->>'name' AS name,
    data#>>'{contact,email}' AS email
FROM 
    user_profiles;

Result:

  name  |       email        
--------+--------------------
 Alice  | [email protected]
 Bob    | [email protected]

Array Operators

PostgreSQL supports operations on array types.

Operator	Description	Example	Result
=	Equal	SELECT ARRAY[1,2,3] = ARRAY[1,2,3];	t
@>	Contains	SELECT ARRAY[1,2,3] @> ARRAY[2,3];	t
<@	Is contained by	SELECT ARRAY[2,3] <@ ARRAY[1,2,3,4];	t
&&	Overlaps (have elements in common)	SELECT ARRAY[1,2] && ARRAY[2,3];	t
`		`	Array concatenation

Working with Arrays

-- Create a table with array data
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    item_name TEXT,
    categories TEXT[]
);

INSERT INTO tags (item_name, categories) VALUES
    ('Laptop', ARRAY['Electronics', 'Computers', 'Office']),
    ('Coffee Mug', ARRAY['Kitchen', 'Office', 'Gifts']);

-- Find items in the 'Office' category
SELECT 
    item_name,
    categories
FROM 
    tags
WHERE 
    categories @> ARRAY['Office'];

Result:

  item_name  |              categories              
-------------+--------------------------------------
 Laptop      | {Electronics,Computers,Office}
 Coffee Mug  | {Kitchen,Office,Gifts}

Date/Time Operators

PostgreSQL has operators for working with date and time data.

Operator	Description	Example	Result
+	Addition	SELECT DATE '2023-01-01' + INTEGER '7';	2023-01-08
-	Subtraction	SELECT DATE '2023-01-10' - INTEGER '5';	2023-01-05
-	Difference	SELECT DATE '2023-01-10' - DATE '2023-01-01';	9
@	Absolute value (for intervals)	SELECT @ INTERVAL '-5 days';	5 days

Date Calculations

-- Assuming we have an orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    delivery_date DATE
);

INSERT INTO orders (order_date, delivery_date) VALUES
    ('2023-01-01', '2023-01-05'),
    ('2023-01-10', '2023-01-18'),
    ('2023-01-15', '2023-01-25');

-- Calculate delivery duration
SELECT 
    id,
    order_date,
    delivery_date,
    delivery_date - order_date AS delivery_days
FROM 
    orders;

Result:

 id | order_date | delivery_date | delivery_days 
----+------------+---------------+--------------
  1 | 2023-01-01 | 2023-01-05    |            4
  2 | 2023-01-10 | 2023-01-18    |            8
  3 | 2023-01-15 | 2023-01-25    |           10

Network Address Operators

PostgreSQL includes operators for IP address manipulation.

Operator	Description	Example	Result
<<	Is contained within	SELECT inet '192.168.1.5' << inet '192.168.1.0/24';	t
>>	Contains	SELECT inet '192.168.1.0/24' >> inet '192.168.1.5';	t
&&	Overlaps	SELECT inet '192.168.1.0/24' && inet '192.168.1.128/25';	t
~	Bitwise NOT	SELECT ~ inet '192.168.1.5';	63.87.254.250

IP Address Filtering

-- Create a table for access logs
CREATE TABLE access_logs (
    id SERIAL PRIMARY KEY,
    ip_address INET,
    access_time TIMESTAMP,
    resource TEXT
);

INSERT INTO access_logs (ip_address, access_time, resource) VALUES
    ('192.168.1.5', '2023-01-01 10:00:00', '/home'),
    ('192.168.2.10', '2023-01-01 10:05:00', '/products'),
    ('10.0.0.15', '2023-01-01 10:10:00', '/cart');

-- Find all accesses from a specific subnet
SELECT 
    id,
    ip_address,
    resource
FROM 
    access_logs
WHERE 
    ip_address << inet '192.168.0.0/16';

Result:

 id | ip_address  |  resource  
----+-------------+------------
  1 | 192.168.1.5 | /home
  2 | 192.168.2.10| /products

Operator Precedence

Like in mathematics, PostgreSQL operators follow a specific order of precedence:

1. :: (type cast)
2. [ ] (array element selection)
3. + - (unary plus, minus)
4. ^ (exponentiation)
5. * / % (multiplication, division, modulo)
6. + - (addition, subtraction)
7. All other operators
8. NOT
9. AND
10. OR

To override this precedence, use parentheses to group operations.

-- Precedence example
SELECT 2 + 3 * 4;        -- Returns 14 (multiplication before addition)
SELECT (2 + 3) * 4;      -- Returns 20 (parentheses override precedence)

Custom Operators

PostgreSQL allows you to create custom operators to fit your specific needs. This is an advanced topic but understanding that this capability exists can be valuable.

-- Example of creating a custom operator
CREATE OPERATOR ## (
    LEFTARG = text,
    RIGHTARG = text,
    FUNCTION = text_concat,
    COMMUTATOR = ##
);

Real-World Example: E-commerce Order Analysis

Let's tie everything together with a comprehensive example analyzing e-commerce orders:

-- Create sample tables
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    signup_date DATE
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price DECIMAL(10, 2),
    categories TEXT[]
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date TIMESTAMP,
    status TEXT
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    price_at_purchase DECIMAL(10, 2)
);

-- Analyze order data using various operators
SELECT 
    c.name AS customer_name,
    o.id AS order_id,
    o.order_date,
    SUM(oi.quantity * oi.price_at_purchase) AS order_total,
    CASE 
        WHEN o.order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Recent'
        WHEN o.order_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'Medium'
        ELSE 'Old'
    END AS order_age,
    EXISTS (
        SELECT 1 
        FROM order_items oi2 
        JOIN products p ON p.id = oi2.product_id
        WHERE oi2.order_id = o.id AND p.categories @> ARRAY['Electronics']
    ) AS contains_electronics
FROM 
    customers c
JOIN 
    orders o ON c.id = o.customer_id
JOIN 
    order_items oi ON o.id = oi.order_id
WHERE 
    o.status = 'Completed'
    AND c.signup_date > CURRENT_DATE - INTERVAL '1 year'
GROUP BY 
    c.name, o.id, o.order_date
HAVING 
    SUM(oi.quantity * oi.price_at_purchase) > 100
ORDER BY 
    o.order_date DESC;

This query demonstrates:

• Arithmetic operators (* for multiplication)
• Comparison operators (=, >)
• Logical operators (AND)
• Date/time operators (- for interval subtraction)
• Array operators (@> for contains)
• Existence operator (EXISTS)
• Functions together with operators

Summary

PostgreSQL operators are powerful tools that allow you to manipulate, compare, and transform data in various ways:

• Arithmetic operators perform mathematical calculations
• Comparison operators filter data based on specific conditions
• Logical operators combine multiple conditions
• String operators work with text data and pattern matching
• JSON operators extract and manipulate JSON data
• Array operators handle array types
• Date/time operators work with dates and intervals
• Network address operators filter and manipulate IP addresses

Understanding these operators is crucial for writing efficient and effective PostgreSQL queries. They are the building blocks of more complex expressions and allow you to transform raw data into meaningful insights.

Practice Exercises

1. Write a query to find all products where the price is between $50 and$100.
2. Create a query that concatenates customer first and last names with a space in between.
3. Find all orders placed in the last 30 days.
4. Extract all emails from a JSON column that contains user contact information.
5. Find all products that belong to both the 'Electronics' and 'Office' categories using array operators.

Additional Resources

• PostgreSQL Official Documentation on Operators
• PostgreSQL Mathematical Functions and Operators
• PostgreSQL String Functions and Operators
• PostgreSQL JSON Functions and Operators
• PostgreSQL Array Functions and Operators