Sort month that is in full in Postgres

Asked

Viewed 1,150 times

6

I need to make a ORDER BY in a table mes which has the months in full and in Portuguese (Janeiro, Fevereiro...). It has no relation to any other column in the table. It is possible to do this?

postgresql order-by classification

  • I doubt you can do it just by string..

2 answers

4


I’ve been looking into it, and I think I found a solution for you string of the month for the respective number (ex.: Janeiro = 1):

SELECT 
       CASE 
            WHEN mes = "Janeiro" THEN 1
            WHEN mes = "Fevereiro" THEN 2
            WHEN mes = "Março" THEN 3
            WHEN mes = "Abril" THEN 4
            ...
       END as meses
       , mes //apresentar também o mês em string ao lado do número
FROM tbl_meses;
ORDER BY meses ASC
  • 1

    It worked with the second code, I just had to change a few things, I put LIKE instead of = and exchange the double quotes for single. Thanks.

  • I just detected a little problem... the result of the month is coming as integer, instead of coming as it was originally.

  • Yes, it was necessary for the order by. But you can still present the month, I’ll edit

1

If this table is just to translate the months a simple solution is to add a column with month number the sort must be done by year and by that new column.

If you have a date or similar field and want to display the month in full in Portuguese you can set the locale in the session and sort by month.

set lc_time = 'portuguese';
select to_char(data,'tmmonth') from tabela order by extract(year from data), extract(month from data)

  • Then I will edit the response to add the necessary settings for the locale to work correctly.

Browser other questions tagged postgresql order-by classification

You are not signed in. Login or sign up in order to post.