vendredi 17 juin 2016

Fail of query when use subquery

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