Today I Learned

This tool can help you learn things like:

1. How many rows were affected by a command?
2. What's the unique ID of the last row that was added to a table?
3. If there was an error, what's the reason for it?

You can use this tool in a program or script to check these things.

For example, if you update some data in a table, you can use "GET DIAGNOSTICS" to see how many rows got changed and if there was any problem. This can be really handy for keeping track of what's going on when you're working with a database.

Example:

-- Let's say we update some data in a table. 
UPDATE my_table SET column1 = 'new_value' WHERE id = 42;-- Now, we want to know how many rows got changed and if there's a unique ID for the last row. 
GET DIAGNOSTICS rows_affected = ROW_COUNT, last_oid = RESULT_OID;-- We can print these values to see what happened. 
RAISE NOTICE 'Rows affected: %, Last OID: %', rows_affected, last_oid;

RAISE NOTICE

Postgres

We use RAISE NOTICE to generate a notice message like we use the console.logs() on Javascript. The message can include placeholders (e.g., %) where you can insert values or variables.

Example:

CREATE OR REPLACE FUNCTION example_function() RETURNS void AS $$ 
BEGIN 
 -- Some code here 
 
 -- Raise a notice message 
 RAISE NOTICE 'This is a notice message: %', 'Additional data'; 
 
 -- More code here 
END; 
$$ LANGUAGE plpgsql;

.empty? Method

Ruby

In Ruby, the .empty? method is used to check whether a data structure, such as a string, array, or hash, is empty. It returns true if the object it is called on contains no elements or characters, and false otherwise.

Example:

# Strings 
empty_string = "" 
non_empty_string = "Hello, world!"empty_string.empty? # => true 
non_empty_string.empty? # => false# Arrays 
empty_array = [] 
non_empty_array = [1, 2, 3]empty_array.empty? # => true 
non_empty_array.empty? # => false# Hashes 
empty_hash = {} 
non_empty_hash = { name: "John", age: 30 }empty_hash.empty? # => true 
non_empty_hash.empty? # => false

AVG method

Postgres

In PostgreSQL, the AVG function is used to calculate the average (arithmetic mean) of a set of numeric values within a specified column of a table. It's a convenient way to find the average value of a dataset in a SQL query.

Syntax:

AVG(column_name)

ROUND Method

Postgres

In PostgreSQL, the ROUND function is used to round a numeric value to a specified number of decimal places. It is a very useful function when you need to control the precision of numeric values in your database queries or calculations.

Syntax:

ROUND(numeric_expression, number_of_decimal_places)

Console.dir()

Javascript

Is a JavaScript method used for displaying an interactive list of the properties of a specified JavaScript object. It is particularly useful for examining the structure and contents of complex objects, such as DOM elements or custom JavaScript objects.

Here's how `console.dir()` works with an example:

const person = { 
 firstName: "John", 
 lastName: "Doe", 
 age: 30, 
 address: { 
 street: "123 Main St", 
 city: "Anytown", 
 state: "CA", 
 zip: "12345", 
 }, 
};console.dir(person);/* output: 
Object 
 age: 30 
 address: Object 
 city: "Anytown" 
 state: "CA" 
 street: "123 Main St" 
 zip: "12345" 
 firstName: "John" 
 lastName: "Doe" 
*/

Any operator

Postgres

The PostgreSQL ANY operator compares a value to a set of values returned by a subquery.

The following illustrates the syntax of the ANY operator.

expresion operator ANY(subquery)

Examples of use:

SELECT 3 = ANY (ARRAY[1, 2, 3, 4]); -- Returns TRUE 
SELECT 42 = ANY (SELECT column FROM some_table); -- Returns TRUE if 42 exists in the column 
SELECT 'apple' = ANY (VALUES ('banana'), ('apple'), ('cherry')); -- Returns TRUE

Coalesce method

Postgres

In PostgreSQL, the COALESCE function is used to return the first non-null expression in a list of expressions. It is a very useful function for handling cases where you want to obtain the first non-null value from a set of possible values.

Example:

SELECT COALESCE(null, 42, 'Hello'); -- Returns 42

Replace method

Postgres

In PostgreSQL, the REPLACE() function is used to replace all occurrences of a specified substring within a given string with another substring. This function is useful for modifying or cleaning up string data in SQL queries.

Example:

REPLACE(original_string, search_string, replacement_string) 
or 
SELECT REPLACE('Hello, World!', 'World', 'Universe'); -- Returns 'Hello, Universe!'

Ceil method

Postgres

In PostgreSQL, the CEIL() function, or CEILING() function, is used to round a numeric value up to the nearest integer that is greater than or equal to the original value. It effectively rounds up the number to the next higher integer.

Example:

SELECT CEIL(5.2); -- Returns 6 
SELECT CEIL(-3.8); -- Returns -3 
SELECT CEIL(10); -- Returns 10 (no change as it's already an integer)

Floor method

Postgres

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;

Generators

Javascript

In JavaScript, a generator is a special type of function that allows you to pause and resume its execution. Generators are defined using the function* syntax, and they are capable of producing a sequence of values lazily, one at a time. They are particularly useful for working with asynchronous code and creating iterable objects.

Let's assume you have a web application that fetches a large dataset of users from a remote API. Loading all users at once would be inefficient and slow. Instead, you can use a generator to paginate through the data, fetching a limited number of users at a time as needed.

Example:

async function* fetchUsers() { 
 let page = 1; 
 const perPage = 10; // Number of users per page

 while (true) { 
  const response = await fetch(`https://api.example.com/users?page=${page}&per_page=${perPage}`); 
  const users = await response.json();

  if (users.length === 0) { 
    // No more users to fetch 
    break; 
  }

  for (const user of users) { 
    yield user; 
  }

  page++; 
 } 
}

// Usage: 
const userGenerator = fetchUsers();(async () => { 
 for await (const user of userGenerator) { 
 console.log(`User: ${user.name}, Email: ${user.email}`); 
 } 
})();

Using range filter for Elasticsearch

Elasticsearch

A range filter in an Elasticsearch query is used to filter documents based on a specific numeric or date field falling within a specified range of values. This filter allows you to retrieve documents that meet certain criteria within a specified range.

When dealing with numeric fields (e.g., prices, quantities), you can use a range filter to find documents where the field value falls within a specified numeric range.

Example:

{ 
 "range": { 
   "price": { 
     "gte": 20, 
     "lte": 50 
   } 
 } 
}

For date fields (e.g., timestamps, dates of events), you can use a range filter to filter documents within a specified date range.

Example:

{ 
 "range": { 
   "event_date": { 
     "gte": "2023-01-01", 
     "lte": "2023-12-31" 
   } 
 } 
}

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;