0

I have this query in MySql and I was wondering, will it be ok in postgresql? I am wondering whether it should be something i need to change :)

I am mostly concerned with the group by function that shouldn't be possible to perform in post.

I am willing to consider the possibility to migrate to postgres and I would like to know if the current query i use will do the work!

Thanks for you help in advance, vinc

    SELECT
     #GROUP BY
   dc.region as Region
 ,(DATEDIFF(CURRENT_TIMESTAMP,cc.activated_at)) AS 'Listing Age'
 ,cc.sku AS SKU
 ,CONCAT(dc.venture_url,cc.urlkey_details) as URL
 , cc.status 'Listing Status'
# REGION
, region.name_en Region
 ,(CASE WHEN is_agent=1 THEN supplier.name ELSE concat(supplier.name, ' (private)') END) AS Agent
 # Car type
 , ct.label_en as 'Vehicle Type'
,(Case 
    WHEN cc.fk_catalog_attribute_option_global_condition=1 THEN 'New'
    ELSE 'Other' END) AS 'Condition'
, ca.name_en as Brand

FROM 
 catalog_product_visible cpv

join catalog_config cc on cc.country_id = cpv.country_id and cc.id_catalog_config = cpv.fk_catalog_config

    left join
    dwh_country dc on dc.country_id = cc.country_id

  JOIN supplier AS supplier ON supplier.country_id = cc.country_id AND supplier.id_supplier = cc.product_owner

 LEFT JOIN
 (SELECT 
 profile_isocode,
 date_format(report_date,'%x-%v') as date,
 sum(coalesce((case when event_action IN ('Email Lead Success', 'Contact Submit Sucess') OR event_category = 'Email Lead Success' then total_events end),0)) as EmailLead,
 sum(coalesce((case when event_action IN ('Phone Lead Success', 'Show number Top', 'Show number Bottom', 'Show number Agency') OR event_category IN ('Phone Lead Success') then total_events end),0)) as PhoneLead,
 sum(coalesce((case when event_category IN ('SMS Lead Success') then total_events end),0)) as SMSLead
 FROM dwh_eventlistings c
 WHERE (event_action in ('Contact Submit Sucess')
 GROUP BY 1,2,3,4,5,6)  AS leads ON leads.profile_isocode = pv.profile_isocode AND leads.listing_id = pv.listing_id AND leads.date = date_format(pv.report_date,'%x-%v')

 LEFT JOIN catalog_simple AS cs ON cs.fk_catalog_config = cc.id_catalog_config AND cs.country_id = cc.country_id

WHERE supplier.name not like '%test%' and URL not like '%test%'

GROUP BY 
country, sku, report_week, source, medium, campaign
thequietus
  • 129
  • 1
  • 1
  • 6

1 Answers1

2

Yes, you are using some features of MySQL that don't work on PostgreSQL.

  • # is a non-standard comment character supported only by MySQL. In PostgreSQL, use either -- or /* */.

  • You have two different columns in your select-list given the alias Region and then you try to GROUP BY that column, which I assume will either throw an error or else result in undefined behavior.

  • In your subquery, you use GROUP BY 1,2,3,4,5,6 even though there are only five columns.

  • In your subquery, it makes no sense to GROUP BY columns that are aggregate expressions like SUM().

  • In your subquery, you don't close the parenthesis in the WHERE clause, so this query can't work in MySQL either.

  • Your query violates the single-value rule. You use several columns in the select-list that you aren't listing in the GROUP BY clause, nor inside aggregate functions. In fact, it's incomprehensible why you have listed the columns you have in the GROUP BY clause.

    MySQL is more permissive about GROUP BY semantics. See my answer to MySQL GROUP BY behavior for an explanation.

I think you have some basic misunderstand of what GROUP BY does. To learn more I recommend you read this excellent blog post about Debunking GROUP BY myths by SQL expert and author Roland Bouman.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    PostgreSQL does support GROUP BY a column's ordinal number. – klin Jun 15 '14 at 18:02
  • @klin, thank you for that, I don't have an PG instance handy to test that. The docs mention column ordinals for `ORDER BY` but not for `GROUP BY` as far as I could see. I'll edit that statement out. But still, it makes no sense to group by `SUM()` as in the OP's query. – Bill Karwin Jun 15 '14 at 18:34
  • 2
    The simple rule is that all the columns in the `GROUP BY` clause constitute the "logical" primary key of the result set. All other fields in the result set are functionally dependent on it. – wildplasser Jun 15 '14 at 18:49
  • It's worth noting for the benefit of the original poster that none of these are likely to be showstoppers that prevent use of PostgreSQL. The broken query just has to be fixed first. – Craig Ringer Jun 16 '14 at 00:22
  • @wildplasser, thanks, that's a good way of thinking about it. – Bill Karwin Jun 16 '14 at 00:27