Today I Learned

The unnest() function in PostgreSQL is used to expand an array into a set of rows. It takes an array as input and returns a new table where each element of the array occupies a separate row. This function is particularly useful for normalizing denormalized data stored in array formats and for performing operations that require each array element to be processed individually.

Uses of the PostgreSQL UNNEST() Function

Normalize Data: Transform array data into individual rows for easier processing.
Facilitate Joins: Enable joins with other tables by expanding arrays into rows.
Aggregate Data: Perform aggregate functions on individual array elements.
Filter Array Elements: Apply filters to specific elements within an array.
Convert Arrays to Tables: Turn arrays into tabular format for better data manipulation.
Combine with Other Functions: Use in conjunction with other PostgreSQL functions for advanced data operations.

Example of use

-- Selecting and unnesting an array of integers
SELECT 
    unnest(ARRAY[1, 2]); -- Expands the array [1, 2] into individual rows

Source: https://www.w3resource.com/PostgreSQL/postgresql_unnest-function.php

Get operation result with decimals using numeric

Postgres

You can perform multiplication using the method SUM and get decimals complementing with numeric in the following way:

Example:

select 
  us.product_id,
  ROUND(SUM(us.units * p.price)::numeric / SUM(us.units), 2) AS average_price 
from Prices p
left join UnitsSold us on us.product_id = p.product_id and us.purchase_date between p.start_date and p.end_date
group by us.product_id
order by us.product_id

Command to distinguish a table from a view

Postgres

The following will show a table of information about all tables available, including which are views and which are tables:

SELECT * FROM information_schema.tables

See Information Schema for more details.

Setup your production database on local environment

Postgres

Through the Heroku PostgreSQL add-on create a new backup capture you can do that by the UI or the CLI running:

heroku pg:backups:capture -a your-production-name-app

After that download the backup on your local machine, and set it in your local environment running on the terminal:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -d <data-base-name> /Users/juanequex/backups/<your-local-backup>

and it is what it is, enjoy your local debugging.

Select into statement

Postgres

The "SELECT INTO" statement is used to retrieve data from a database table and store it into variables or a new table.

Example:

-- Assigning Values to Variables 
DECLARE @variable_name data_type; 
SELECT column_name INTO @variable_name FROM table_name WHERE condition;-- Creating a New Table 
SELECT column1, column2 INTO new_table FROM old_table WHERE condition;-- Inserting Data into an Existing Table 
INSERT INTO existing_table (column1, column2) 
SELECT column1, column2 FROM source_table WHERE condition;

FOUND variable

Postgres

In PostgreSQL, the FOUND variable is a simple flag that helps you check if a query has found any results. It's like a "yes" or "no" indicator.

1. When a query successfully finds one or more results, FOUND is set to true (or "yes").
2. When a query doesn't find any results, FOUND is set to false (or "no").

You can use FOUND in control structures like IF statements to make decisions based on whether your query found anything or not. It's a handy way to handle the outcome of a database search in your SQL scripts.

Global variables

Postgres

In PostgreSQL scripts, you can use global variables by defining custom variables and using them within your SQL code.

Example:

-- set a global variable 
SET my_var = 42;-- use the global variable 
SELECT * FROM my_table WHERE column = my_var;-- unset the global variable 
RESET my_var;

GET DIAGNOSTICS

Postgres

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;

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)

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)