I am having this view query in MySql which works

SELECT s.id AS id,
       s.customer_id AS customer_id,
       s.shipment_status_id AS shipment_status_id,
       ss.name AS shipment_status_name,
       s.ship_from_date AS ship_from_date,
       s.ship_to_company AS ship_to_company,
       s.ship_from_company AS ship_from_company,
       s.ship_to_address1 AS ship_to_address1,
       s.ship_to_address2 AS ship_to_address2,
       s.ship_to_postal_code AS ship_to_postal_code,
       s.ship_to_city AS ship_to_city,
       c.country AS country,
       p.tracking_numbers AS tracking_numbers,
       s.quote_service_name AS quote_service_name,
       s.quote_total_charge AS quote_total_charge,
       p.weight AS weight,
       p.insurance_amount AS insurance_amount,
       p.cod_amount AS cod_amount,
       z.fedex AS ZONE,
       s_p.code AS state_province_code,
       s_p.zone AS state_province_zone,
       ss.name AS status_name,
       i.total AS total,
       cust.first_name AS customer_first_name,
       cust.last_name AS customer_last_name,
       (CASE
            WHEN (s.additional_services_saturday_delivery = 1) THEN 'Yes'
        END) AS saturday_delivery,
       (CASE
            WHEN (s.additional_services_hold_for_pickap = 1) THEN 'Yes'
        END) AS hold_for_pickap,
       (CASE
            WHEN (s.ship_to_residential = 1) THEN 'Yes'
        END) AS ship_to_residential,
       (CASE
            WHEN (s.additional_services_signature_required = 1) THEN 'Yes'
        END) AS signature_required
FROM (((((((shipments s
            LEFT JOIN shipment_package_group_data p on((s.id = p.shipment_id)))
           LEFT JOIN country c on((s.ship_to_country_id = c.id)))
          LEFT JOIN invoice i on((s.invoice_id = i.id)))
         LEFT JOIN state_province s_p on((s.ship_to_state = s_p.id)))
        LEFT JOIN shipment_statuses ss on((s.shipment_status_id = ss.id)))
       LEFT JOIN zones z on((s.zone_id = z.id)))
      JOIN customer cust on((s.customer_id = cust.id)))
GROUP BY s.id

But when I try converting it to PostgreSQL view like this:

    SELECT s.id AS id,
       s.customer_id AS customer_id,
       s.shipment_status_id AS shipment_status_id,
       ss.name AS shipment_status_name,
       s.ship_from_date AS ship_from_date,
       s.ship_to_company AS ship_to_company,
       s.ship_from_company AS ship_from_company,
       s.ship_to_address1 AS ship_to_address1,
       s.ship_to_address2 AS ship_to_address2,
       s.ship_to_postal_code AS ship_to_postal_code,
       s.ship_to_city AS ship_to_city,
       c.country AS country,
       p.tracking_numbers AS tracking_numbers,
       s.quote_service_name AS quote_service_name,
       s.quote_total_charge AS quote_total_charge,
       p.weight AS weight,
       p.insurance_amount AS insurance_amount,
       p.cod_amount AS cod_amount,
       z.fedex AS ZONE,
       s_p.code AS state_province_code,
       s_p.zone AS state_province_zone,
       ss.name AS status_name,
       i.total AS total,
       cust.first_name AS customer_first_name,
       cust.last_name AS customer_last_name,
       (CASE
            WHEN (s.additional_services_saturday_delivery = 1) THEN 'Yes'
        END) AS saturday_delivery,
       (CASE
            WHEN (s.additional_services_hold_for_pickap = 1) THEN 'Yes'
        END) AS hold_for_pickap,
       (CASE
            WHEN (s.ship_to_residential = 1) THEN 'Yes'
        END) AS ship_to_residential,
       (CASE
            WHEN (s.additional_services_signature_required = 1) THEN 'Yes'
        END) AS signature_required
FROM (shipments s
            LEFT JOIN shipment_package_group_data p on(s.id::INT = p.shipment_id)
           LEFT JOIN country c on((s.ship_to_country_id = c.id))
          LEFT JOIN invoice i on((s.invoice_id = i.id))
         LEFT JOIN state_province s_p on((s.ship_to_state::INT = s_p.id))
        LEFT JOIN shipment_statuses ss on((s.shipment_status_id = ss.id))
       LEFT JOIN zones z on((s.zone_id = z.id))
      JOIN customer cust on((s.customer_id = cust.id)))
GROUP BY s.id

PostgreSQL complains the following:

    [Err] ERROR:  column "ss.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4:        ss.name AS shipment_status_name,
               ^
  • 2
    It's not very clear why you are grouping at all. Furthermore, this is one of MySQL's big mistakes (not to say sins), to allow incomplete GROUP BY clauses, which will behave somehow. In PostgreSQL, you have to at least list all primary keys of the tables involved. – dezso Sep 28 '16 at 13:37
up vote 7 down vote accepted

TL;DR MySQL is ignoring the SQL Standard and allowing your original query to return indeterminate results. Postgres doesn't let you do that; you will need to properly define what results you expect.

You can see an explanation of the behavior that MySQL is relying on e.g. in this SO post. When you convert your query over to Postgres, you need to fill in what the author of the original query neglected: there is a GROUP BY s.id, so for each s.id that is grouped-by, which ss.name must be returned? There may be several different ss.name values, how should the database decide which one to give you? Note that ss.name is not the only offending column in this query, all of your non-aggregate columns you are SELECTing potentially need to be handled properly in this query.

Ultimately, you will need to use some combination of:

  • get rid of the GROUP BY entirely and simply return all results
  • GROUP BY all non-aggregated columns you are SELECTing, e.g. GROUP BY s.id, s.customer_id, s.shipment_status_id, ...
  • Use SELECT ... DISTINCT ON ... ORDER BY to tell Postgres that you only care to receive a single s.customer_id, s.shipment_status_id, ss.name, etc. for each s.id that you are GROUPing by. The ORDER BY will instruct Postgres how to decide which one of each s.customer_id etc. to return.

... and probably there are some more workarounds I'm neglecting, but that should get you started in the right direction.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.