PostgreSQL - FORMAT Function

The FORMAT function in PostgreSQL is used to format strings based on a format string and an optional list of arguments. It provides a more readable and convenient way to concatenate and format strings compared to using the || operator or other string functions.

Syntax:

FORMAT(format_string, argument1, argument2, ...)

format_string: The string that contains format specifiers, which will be replaced by the values from the arguments.
argument1, argument2, ...: The list of arguments that will be inserted into the format_string.

Format Specifiers:

%s: For string values.
%I: For SQL identifiers. This will quote the argument if necessary.
%L: For SQL literals. This will escape and quote the argument as necessary.
%%: For a literal %.

Examples:

Basic string formatting:
```
SELECT FORMAT('Hello, %s!', 'World');
```
Result: 'Hello, World!'
Using multiple specifiers:
```
SELECT FORMAT('%s %s', 'Hello', 'World');
```
Result: 'Hello World'
Using %I for SQL identifiers:
```
SELECT FORMAT('CREATE TABLE %I (%I text)', 'table_name', 'column_name');
```
Result: 'CREATE TABLE "table_name" ("column_name" text)'
Using %L for SQL literals:
```
SELECT FORMAT('INSERT INTO tbl (col) VALUES (%L)', 'O''Reilly');
```
Result: 'INSERT INTO tbl (col) VALUES ('O''Reilly')'

Benefits:

Readability: The FORMAT function offers a cleaner way to concatenate and format strings, especially when dealing with multiple string manipulations.
SQL Injection Protection: Using %I for identifiers and %L for literals can help safeguard against SQL injection as the arguments are correctly quoted and escaped.
Consistency: The function brings a C-style string formatting to SQL, which might be familiar to those coming from programming backgrounds.

In conclusion, the FORMAT function is a valuable tool in PostgreSQL for producing formatted strings, ensuring code readability, and enhancing security.

PostgreSQL TO_CHAR function example:
- Description: The TO_CHAR function converts a value to a specified format as a string.
- Code:
```
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS formatted_date;
```
Formatting dates in PostgreSQL:
- Description: Format date values using the TO_CHAR function.
- Code:
```
SELECT TO_CHAR(CURRENT_DATE, 'DD Mon YYYY') AS formatted_date;
```
Number formatting in PostgreSQL:
- Description: Apply number formatting using the TO_CHAR function.
- Code:
```
SELECT TO_CHAR(1234567.89, '9,999,999.99') AS formatted_number;
```
Custom date formatting in PostgreSQL:
- Description: Customize date formatting using format patterns.
- Code:
```
SELECT TO_CHAR(NOW(), 'DDth Month YYYY') AS custom_formatted_date;
```
PostgreSQL string formatting functions:
- Description: Explore various string formatting functions in PostgreSQL, including TO_CHAR.
- Code:
```
SELECT CONCAT('Formatted Date: ', TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS')) AS result;
```

TO_CHAR vs. TO_DATE in PostgreSQL:

Description: Understand the difference between TO_CHAR and TO_DATE.

Code:

-- TO_CHAR: Convert date to string
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') AS char_result;

-- TO_DATE: Convert string to date
SELECT TO_DATE('2022-01-01', 'YYYY-MM-DD') AS date_result;

Formatting timestamps in PostgreSQL:
- Description: Format timestamp values using TO_CHAR.
- Code:
```
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS.US') AS formatted_timestamp;
```
Currency formatting in PostgreSQL:
- Description: Apply currency formatting using the TO_CHAR function.
- Code:
```
SELECT TO_CHAR(1234567.89, '$999,999,999.99') AS formatted_currency;
```
Scientific notation formatting in PostgreSQL:
- Description: Format numbers using scientific notation.
- Code:
```
SELECT TO_CHAR(0.000012345, '9.999E999') AS scientific_notation;
```
Formatting intervals in PostgreSQL:
- Description: Use TO_CHAR to format interval values.
- Code:
```
SELECT TO_CHAR(INTERVAL '2 days 03:04:05', 'DD HH24:MI:SS') AS formatted_interval;
```
PostgreSQL date format codes:
- Description: Understand and use date format codes with TO_CHAR.
- Code:
```
SELECT TO_CHAR(NOW(), 'FMDDth Month YYYY') AS formatted_date;
```

TO_CHAR vs. EXTRACT in PostgreSQL:

Description: Compare TO_CHAR for formatting and EXTRACT for extracting components.

Code:

-- Using TO_CHAR for formatting
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS formatted_date;

-- Using EXTRACT for extracting components
SELECT EXTRACT(YEAR FROM NOW()) AS extracted_year;

Locale-specific formatting in PostgreSQL:
- Description: Adjust formatting based on the locale.
- Code:
```
SELECT TO_CHAR(NOW(), 'FMDay, FMMonth FMDD, YYYY', 'en_US') AS formatted_date;
```

Conditional formatting in PostgreSQL:

Description: Conditionally format values based on a condition.

Code:

SELECT 
    CASE WHEN column1 > 0 
         THEN TO_CHAR(column1, '$999,999,999.99') 
         ELSE 'N/A' 
    END AS formatted_value
FROM your_table;

Formatting options for numeric data types in PostgreSQL:
- Description: Explore various formatting options for numeric data types.
- Code:
```
SELECT 
    TO_CHAR(123456.789, '999G999G999D99S') AS custom_format1,
    TO_CHAR(0.123, '9.999E999S') AS custom_format2;
```