MySql vs Postgresql view creation

Question

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,
               ^

Answer 1

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.