I have a problem with PostgreSQL 9.5 when I select columns and I use json_build_object, I have an error with GROUP BY clause.
I have three tables:
Table contact
Colonne | Type
-----------+--------------------------
id | integer
lastname | character varying(255)
firstname | character varying(255)
This table is a table of contacts
Table companie
Colonne | Type
-----------+--------------------------
id | integer
name | character varying(255)
This table is a table of companies
Table companie_contact
Colonne | Type
-----------+--------------------------
id | integer
id_c | integer
id_cm | integer
This table is a many-to-many relation to link contacts with companies and vice versa.
When I execute this query all works
SELECT co.id,
json_build_object(
'lastname', co.lastname,
'firstname', co.firstname) AS contact,
array_agg(c.name) AS companies
FROM companie AS c
INNER JOIN companie_contact AS vs
ON c.id = vs.id_cm
INNER JOIN contact AS co
ON co.id = vs.id_c
GROUP BY co.id;
I get
id | contact | companies
----+----------------------------------------------+-------------
1 | {"lastname" : "some", "firstname" : "one"} | {A,B,D,E,F}
4 | {"lastname" : "some", "firstname" : "two"} | {A}
2 | {"lastname" : "some", "firstname" : "three"} | {B}
But when I replace contact and companie by View or by a subquery Like below
-- Imagine that the views are more complex than that
CREATE VIEW view_contact AS SELECT * FROM contact
CREATE VIEW view_companie AS SELECT * FROM companie
SELECT co.id,
json_build_object(
'lastname', co.lastname,
'firstname', co.firstname) AS contact,
array_agg(c.name) AS companies
FROM view_companie AS c
INNER JOIN companie_contact AS vs
ON c.id = vs.id_cm
INNER JOIN view_contact AS co
ON co.id = vs.id_c
GROUP BY co.id;
I got this error
ERROR: column "co.lastname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT co.id,json_build_object('lastname', co.lastname, 'fi...
I don't know why I have this error, I didn't find solution to my specific problem on web
Thanks you for your help
PS: Sorry for my english I'm french
Aucun commentaire:
Enregistrer un commentaire