Often in PostgreSQL you may want to order the rows of a table based on a column that contains month names.
You can use the following syntax to do so:
SELECT *
FROM products
ORDER BY to_date(month_sold, 'Month');
This particular example will order the rows of the products table based on the month names in the month_sold column.
Note that by using the to_date() function, we are able to extract the month as a date from the month_sold column.
We then are able to sort the rows of the table by month in chronological order using the ORDER BY statement.
The following example shows how to use this syntax in practice.
Example: How to Order by Month Name in PostgreSQL
Suppose that we create the following table named products that contains information about when various products were sold at some retail store.
-- create table named 'products'
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
month_sold TEXT
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A0045BH', 'February');
INSERT INTO products VALUES (0002, 'A00933R', 'April');
INSERT INTO products VALUES (0003, 'BRRHHYL', 'May');
INSERT INTO products VALUES (0004, 'BR33503', 'December');
INSERT INTO products VALUES (0005, 'CHYYGG4', 'October');
INSERT INTO products VALUES (0006, 'C743559', 'March');
INSERT INTO products VALUES (0007, 'HTT56HH', 'February');
-- view all rows in table
SELECT * FROM products;
id | name | month_sold
----+---------+------------
1 | A0045BH | February
2 | A00933R | April
3 | BRRHHYL | May
4 | BR33503 | December
5 | CHYYGG4 | October
6 | C743559 | March
7 | HTT56HH | February
Suppose that we use the following syntax to attempt to order the rows based on the month names in the month_sold column:
SELECT *
FROM products
ORDER BY month_sold;
id | name | month_sold
----+---------+------------
2 | A00933R | April
4 | BR33503 | December
1 | A0045BH | February
7 | HTT56HH | February
6 | C743559 | March
3 | BRRHHYL | May
5 | CHYYGG4 | October
Since the month_sold column is a text column, the rows are sorted in alphabetical order instead of chronological order.
To sort by months in chronological order, we can use the to_date() function with the following syntax:
SELECT *
FROM products
ORDER BY to_date(month_sold, 'Month');
id | name | month_sold
----+---------+------------
7 | HTT56HH | February
1 | A0045BH | February
6 | C743559 | March
2 | A00933R | April
3 | BRRHHYL | May
5 | CHYYGG4 | October
4 | BR33503 | December
Notice that the rows are sorted in correct chronological order based on the months in the month_sold column.
You can use similar syntax to sort the rows of your own table based on month names in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Get Month Name from Number
PostgreSQL: How to Get the Number of Days in a Month
PostgreSQL: How to Select All Rows from Previous Month
PostgreSQL: How to Generate a Series of Months
