This page contains the full list of template patterns and template pattern modifiers that can be used when formatting numbers in PostgreSQL.
Object-Relational
Get the Day Name from a Date in PostgreSQL
In PostgreSQL, you can get the day name from a date by using the to_char() function. This function returns a string based on the timestamp and the template pattern you provide as arguments..
Get the Short Month Name in PostgreSQL
In PostgreSQL, you can use the to_char() function to get the short month name from a date.
By “short month name” I mean the abbreviated month name, for example Jan, Feb, Mar, etc.
The to_char() function accepts two parameters; the date and the format string with which to format that date.
Extract the Year from a Date in PostgreSQL
In PostgreSQL you can use the extract() function to get the year from a date.
You can also use the date_part() function to do the same thing.
Extract the Month from a Date in PostgreSQL
In PostgreSQL you can use the EXTRACT() function to get the month from a date.
You can also use the DATE_PART() function to do the same thing.
Convert Month Number to Month Name in PostgreSQL
You can use the following code examples in PostgreSQL if you have a month number but you want the month name instead.
Convert Month Name to Month Number in PostgreSQL
In PostgreSQL, if you already have a month name, but you want to convert that name to the month number, you can do this with the EXTRACT() function.
Get the Month Name from a Date in PostgreSQL
If you’re familiar with PostgreSQL, you might know that you can use the EXTRACT() and the DATE_PART() functions to extract the month from a date. But those functions only allow you to extract the month number.
What if you need the month name?
You can get the month name from a date by using the TO_CHAR() function. This function returns a string based on the timestamp and the template pattern you provide as arguments.
How to Check your PostgreSQL Version
In this article I present several ways to check your PostgreSQL version.
How to Return a List of Available Collations in PostgreSQL
In PostgreSQL, we can use the pg_collation catalog to get a list of the available collations. Another way to do it is to use the \dOS command.
Using the first method, we can run the following statement to return a list of available collations in PostgreSQL:
SELECT * FROM pg_collation;
These collations are mappings from an SQL name to operating system locale categories.