Error while converting mysql query to postgres

Question

The following query works in MySQL:

SELECT
  f.created_date as time_sec
  ,sum(f.value) as value
  , date_format( f.created_date , '%a') as metric
FROM ck_view_fills as f
GROUP BY date_format(f.created_date, '%a' )

I have migrated my database to PostgreSQL and I am now converting my queries to match. My naive conversion looks like this:

SELECT
  f.created_date as time_sec
  ,sum(f.value) as value
  , to_char( f.created_date , "D") as metric
FROM ck_view_fills as f
GROUP BY to_char( f.created_date , "D")

This query is not accepted and the error message produced by PostgreSQL is the following:

Error in query (7): ERROR: column "f.created_date" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: f.created_date as time_sec

As far as I can tell f.created_date is indeed used in the group by clause. I have also seen examples using this very syntax. So what is the cause of this error and how do I get around it?

Mr. Developerdude · Accepted Answer · 2019-08-23 11:52:33Z

2

Postgres is correct. In fact, your query would fail with the most recent versions of MySQL as well -- using the default settings.

Just use an aggregation function:

SELECT MIN(f.created_date) as time_sec,
       SUM(f.value) as value
       TO_CHAR(f.created_date, 'D') as metric
FROM ck_view_fills f
GROUP BY to_char(f.created_date , 'D');

You should have used a similar construct in MySQL (regarding MIN() -- or MAX()).

edited Aug 23, 2019 at 11:52

Mr. Developerdude

9,78811 gold badges66 silver badges111 bronze badges

answered Aug 23, 2019 at 11:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error while converting mysql query to postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related