How to select and order an extracted column in PostgreSQL

Question

I have a query builder to select all users created in the last 12 months

$range = \Carbon\Carbon::now()->subYears(1);
            $countUser = DB::table('users')
                        ->select(DB::raw('month(created_at) as getMonth'),
                                 DB::raw('year(created_at) as getYear'), 
                                 DB::raw('COUNT(*) as value'))
                        ->where('created_at', '>=', $range)
                        ->groupBy('getMonth','getYear')
                        ->orderBy('getMonth', 'ASC')
                        ->get();

This query works fine with MySQL, However, when I deploy it on Heroku which runs with PostgreSQL database it returns an error

column "getMonth" does not exist LINE 1: ...end date)", "extract(year from created_at)" order by "getMonth"... ^ (SQL: select extract(month from created_at) as getMonth, extract(year from created_at) as getYear, COUNT(*) as value from "users" where "created_at" >= 2019-10-01 03:16:43 group by "extract(month from created_at)", "extract(year from created_at)" order by "getMonth" asc)

I tried to search and figured out that postgest has a different statement to get month and year in created_at column and I change my code to this and it also returns the error not found column getMonth for the GroupBy()

$range = \Carbon\Carbon::now()->subYears(1);
            $countUser = DB::table('users')
                        ->select(DB::raw('extract(month from created_at) as getMonth'),
                            DB::raw('extract(year from created_at) as getYear'),
                            DB::raw('COUNT(*) as value'))
                        ->where('created_at', '>=', $range)
                        ->groupBy('getMonth','getYear')
                        ->orderBy('getMonth', 'ASC')
                        ->get();

Does PostgreSQL have any naming rules to set a name for a selected column or does it has any way to solve my problem? Thanks for reading!

@WaytoDeveloper it is? do you have a link to information about getMonth? — ysth

ysth ysth · Accepted Answer · 2020-10-01T04:00:59

If you use mixed-case aliases in postgresql, they have to be quoted. Use get_month instead of getMonth, or use AS "getMonth" and groupBy('"getMonth"',...

How to select and order an extracted column in PostgreSQL

1 Answers