I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it?
The following query:
SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Project GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;Produces the following error:
ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in an aggregate function
That field is a CHAR, so I'm not sure what kind of aggregate to use, or (more important to my understanding) why one is necessary.
As I said, I'm not sure I understand why this occurs. I'm assuming that I don't understand "group by" as well as I thought I did ;)
This isn't my query, I'm translating a system prototyped in MSSQL to Postgres. This query _does_ work in MSSQL. Does that constitute a bug in MSSQL, or a shortcomming of Postgres, or just a difference of interpretation?
-- Bill Moran Potential Technologies http://www.potentialtech.com
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
