'column "survey_data.start_date" must appear in the GROUP BY clause or be used in an-CodePudding

I'm trying to run the following PostgreSQL query:

sql = """SELECT json_agg(survey_data)
            FROM survey_data.survey_data 
            WHERE codigo_do_projeto LIKE '%%%s%%'
            ORDER BY data_de_inicio_da_coleta desc 
            LIMIT %s
            OFFSET %s*%s""" % (survey_name,items_per_page, items_per_page,page_number)

However it throws this error:

'column "survey_data.data_de_inicio_da_coleta" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 4:
ORDER BY data_de_inicio_da_coleta desc \n
^\n'

I really want it the query result to come out as a dict because it is being a lot of trouble to convert it to one (and it looks sloopy).

CodePudding user response：

As the error says data_de_inicio_da_coleta desc needs to be used in a GROUP BY or used in the aggregate function.

So your choices are, throw a GROUP BY in the query:

SELECT json_agg(survey_data)
FROM survey_data.survey_data 
WHERE codigo_do_projeto LIKE '%%%s%%'
GROUP BY data_de_inicio_da_coleta desc
ORDER BY data_de_inicio_da_coleta desc 
LIMIT %s
OFFSET %s*%s

Or use in the aggregate function:

SELECT json_agg(survey_data ORDER BY data_de_inicio_da_coleta desc)
FROM survey_data.survey_data 
WHERE codigo_do_projeto LIKE '%%%s%%'
LIMIT %s
OFFSET %s*%s

As to the convert to dict part. Using RealDictCursor from psycopg2.extras:

import psycopg2
from psycopg2.extras import RealDictCursor

con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432, cursor_factory=RealDictCursor)

cur = con.cursor()

cur.execute("select * from animals order by animal")

cur.fetchall()

[RealDictRow([('pk_animals', 200), ('cond', 'good'), ('animal', 'crow')]),
 RealDictRow([('pk_animals', 45), ('cond', 'poor'), ('animal', "'dog'")]),
 RealDictRow([('pk_animals', 2), ('cond', 'good'), ('animal', 'eagle')]),
 RealDictRow([('pk_animals', 35), ('cond', 'good'), ('animal', 'emu')]),
 RealDictRow([('pk_animals', 101), ('cond', 'good'), ('animal', 'gopher')]),
 RealDictRow([('pk_animals', 16), ('cond', 'fair'), ('animal', 'heron')]),
 RealDictRow([('pk_animals', 33), ('cond', 'fair'), ('animal', 'mole')]),
 RealDictRow([('pk_animals', 3), ('cond', 'good'), ('animal', 'mole')])]