Today I Learned

In PostgreSQL, the FLOOR() function is used to round a numeric value down to the nearest integer that is less than or equal to the original value. It effectively truncates the decimal part of the number, leaving only the integer part.

Example:

SELECT FLOOR(5.7); -- Returns 5 
SELECT FLOOR(-3.2); -- Returns -4 
SELECT FLOOR(10); -- Returns 10 (no change as it's already an integer)

Conditional expression

Postgres

In PostgreSQL, you can use the CASE expression with the WHEN, THEN, and END keywords within a SELECT statement to perform conditional logic and return different values based on specific conditions. The CASE expression allows you to create conditional branching within your SQL queries.

Example:

Suppose you have a table "employees" with a column salary, and you want to categorize employees into salary ranges

SELECT 
 employee_name, 
 salary, 
 CASE 
 WHEN salary < 30000 THEN 'Low Income' 
 WHEN salary >= 30000 AND salary < 60000 THEN 'Medium Income' 
 WHEN salary >= 60000 THEN 'High Income' 
 ELSE 'Unknown' 
 END AS income_category 
FROM 
 employees;

Using Regex matching

Postgres

You can use the "regexp_match" function to perform regular expression matching within SQL queries. This function allows you to extract matched substrings from a text column based on a regular expression pattern.

Example:

SELECT regexp_match(column_name, 'your_regex_pattern') AS matched_text 
FROM table_name;

Min/Max Postgres methods

Postgres

In PostgreSQL, the "MIN" and "MAX" functions are aggregate functions used to find the minimum and maximum values within a set of rows or a specific column in a table.

The "MIN" function is used to find the minimum value within a set of values. It can be applied to both numeric and non-numeric data types. When used in conjunction with a GROUP BY clause, it finds the minimum value for each group.

Example:
Find the minimum value in a column:

SELECT MIN(column_name) FROM table_name;

The "MAX" function is used to find the maximum value within a set of values, similar to MIN. It can be applied to both numeric and non-numeric data types. When used with GROUP BY, it finds the maximum value for each group.

Example:
Find the maximum value in a column:

SELECT MAX(column_name) FROM table_name;

string_agg method

Postgres

string_agg function: This function is used for string aggregation or concatenation within groups of rows. It takes an expression (usually a column) and concatenates the values of that expression across rows in a group, separated by a specified delimiter.

For example, suppose you have a table of orders and you want to concatenate the names of products for each order, separated by commas:

SELECT order_id, string_agg(product_name, ', ') AS products 
FROM order_details 
GROUP BY order_id;

In this query, string_agg(product_name, ', ') concatenates the product_name values for each order, separated by commas.

It's a useful function for creating comma-separated lists or combining text values within groups when working with SQL in PostgreSQL.

Use % as a wildcard on searching

Postgres

If you want to match text that ends with "-dev" using a WHERE sentence in PostgreSQL, you can do so using the LIKE operator and the percent sign % as a wildcard. Here's how:

-- Example: Match strings that end with '-dev' 
SELECT * 
FROM your_table 
WHERE your_column LIKE '%-dev';

In this example, the LIKE operator with the pattern "%-dev" will match any strings in your_column that end with "-dev". The percent sign % acts as a wildcard for zero or more characters. So, it matches any text that ends with "-dev".

Upper/Lower Method

Postgres

In PostgreSQL, the `UPPER` and `LOWER` functions are used to manipulate the case of text strings. Here's a quick explanation of each function with an example:

UPPER Function:
The UPPER function converts all characters in a text string to uppercase.

Syntax:

UPPER(string)

LOWER Function:
The LOWER function converts all characters in a text string to lowercase.

Syntax:

LOWER(string)

What are Views

Postgres

In PostgreSQL, a view is a virtual table created by a query. It behaves like a table but doesn't store data itself; instead, it's a saved query that can be referenced like a table. Views are useful for simplifying complex queries, providing an abstracted layer over tables, and controlling access to data.

Explanation:
1. A view is defined by a SQL query and has a name.
2. The view's data is dynamically generated based on the query whenever the view is queried.
3. Views can simplify complex joins or aggregations and abstract the underlying data structure for users.

Example:
Suppose you have a database with a table orders containing order information with columns like `order_id`, `customer_id`, and `order_date`. You want to create a view that shows only the orders placed by a specific customer. Here's how you can do it:

-- Create a view named "customer_orders" to show orders for a specific customer 
CREATE VIEW customer_orders AS 
SELECT order_id, order_date 
FROM orders 
WHERE customer_id = '12345';

Substring function

Postgres

In PostgreSQL, the SUBSTRING function is used to extract a portion of a string from within another string. It allows you to specify the starting position and the length of the substring you want to extract.

Example:

SUBSTRING(string FROM start_position [FOR length])

Where-in sentence on Postgres

Postgres

In PostgreSQL and SQL in general, the `WHERE` clause, combined with the `IN` operator, is used to filter rows from a table based on a specified list of values. It allows you to retrieve rows that match any of the values provided in the list. The `IN` operator simplifies querying for multiple values without the need for multiple `OR` conditions.

Example:

SELECT column1, column2, ... 
FROM table_name 
WHERE column_name IN (value1, value2, ...);