Q:

postgresql order numeric string as integer

I have this query:
SELECT AVG(value), EXTRACT(YEAR FROM date_input)::text || EXTRACT(MONTH FROM date_input)::text AS tgl FROM app_inputdata WHERE dataid='95' AND date_input BETWEEN '2013-01-01' AND '2016-12-31' GROUP BY tgl ORDER BY tgl

and the result of this query is like:
+-----+--------+
| avg | tgl    |
+-----+--------+
|23.1 | 20141  |
|12.1 | 201412 | 
|1.7  | 20142  |
+-----+--------+

as you can see, tgl of 201412 appear at the second line where it should be appear on the last line. I am thinking it's because tgl is in type of string, so how to sort it as integer?

A:

Try using the date components:
SELECT AVG(value),
       EXTRACT(YEAR FROM date_input)::text || EXTRACT(MONTH FROM date_input)::text AS tgl
FROM app_inputdata
WHERE dataid='95' AND
      date_input BETWEEN '2013-01-01' AND '2016-12-31'
GROUP BY tgl
ORDER BY MAX(EXTRACT(YEAR FROM date_input)), MAX(EXTRACT(MONTH FROM date_input))

