0

When i run this query in window system behave correctly UNNSET but when i run this query Linux behave different.unnset duplicate record list on different row

SELECT DISTINCT 
         "billing_billmanagement"."creation_date",
         "billing_billmanagement"."bill_number",
         unnest(array_agg(DISTINCT "inventory_product"."product_name")) AS "product",
         unnest(array_agg(DISTINCT "services_service"."name")) AS "service"
FROM "billing_billmanagement"
  INNER JOIN "users_staffuser" ON ("billing_billmanagement"."staff_id" = "users_staffuser"."id")
  INNER JOIN "auth_user" ON ("users_staffuser"."user_id" = "auth_user"."id")
  LEFT OUTER JOIN "billing_customerproductbill" ON ("billing_billmanagement"."id" = "billing_customerproductbill"."bill_id")
  LEFT OUTER JOIN "inventory_product" ON ("billing_customerproductbill"."product_id" = "inventory_product"."id")
  LEFT OUTER JOIN "billing_customerservicebill" ON ("billing_billmanagement"."id" = "billing_customerservicebill"."bill_id")
  LEFT OUTER JOIN "services_service" ON ("billing_customerservicebill"."service_id" = "services_service"."id")

WHERE "billing_billmanagement"."creation_date" BETWEEN '2017-12-04' AND '2017-12-06'
GROUP BY billing_billmanagement.creation_date,
         billing_billmanagement.bill_number
ORDER BY "billing_billmanagement"."creation_date" ASC
  • Show the output you get and the output you expect. And make sure in both cases you have **exactly** the same data in **all* tables. It is very unlikely that this depends on the operating system. There are differences between the Windows version and Linux version but `unnest()` is not one of them (mainly the outcome of string comparisons might be different). Also: using multiple set returning functions in the SELECT list is discouraged (see the manual for details: https://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET) –  Dec 08 '17 at 06:57
  • You probably get better answers if you can supply a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) that shows the behaviour **[edit]** your question for that, do **not** post code in comments. –  Dec 08 '17 at 06:58

1 Answers1

0

If getting duplicate rows is the problem, try this 

 SELECT billing_billmanagement.creation_date,
           billing_billmanagement.bill_number,
           inventory_product.product_name AS product,
           services_service.name AS service
    FROM billing_billmanagement
      INNER JOIN users_staffuser ON (billing_billmanagement.staff_id = users_staffuser.id)
      INNER JOIN auth_user ON (users_staffuser.user_id = auth_user.id)
      LEFT OUTER JOIN billing_customerproductbill ON (billing_billmanagement.id = billing_customerproductbill.bill_id)
      LEFT OUTER JOIN inventory_product ON (billing_customerproductbill.product_id = inventory_product.id)
      LEFT OUTER JOIN billing_customerservicebill ON (billing_billmanagement.id = billing_customerservicebill.bill_id)
      LEFT OUTER JOIN services_service ON (billing_customerservicebill.service_id = services_service.id)
    WHERE billing_billmanagement.creation_date BETWEEN '2017-12-04' AND '2017-12-06'
    GROUP BY 1,
             2,
             3,
             4
    ORDER BY 1 ASC;
penguin
  • 1,267
  • 14
  • 27