What is the equivalent of the following sql statement in postgresql?-CodePudding

I have the following SQL statement that is executed in a MySql database and it works successfully

cursor.execute("""SELECT COUNT(*), `deleted` FROM `tweets` WHERE `id` = %s""", (tweet['id'],))

What is the equivalent in postgres database? Trying the following

cursor.execute("""SELECT COUNT(*), deleted FROM tweets WHERE id = %s""", (tweet['id'],))

Fails with error

 ERROR: ERROR:  column "tweets.deleted" must appear in the GROUP BY clause or be used in an aggregate function

I'm not sure where I should add the GROUP BY Clause. Any help will be appreciated.

CodePudding user response：

This statement:

SELECT COUNT(*), `deleted`
FROM `tweets`
WHERE `id` = %s

is malformed in any database, including MySQL, because the SELECT columns mix an aggregation function and a non-aggregation function and there is no GROUP BY.

Older versions of MySQL accept this syntax (an oversight that has happily been fixed!). The value of deleted comes from an arbitrary row. So, you can use any value of deleted to match the functionality. So, use an aggregation function:

SELECT COUNT(*), MAX(deleted) as deleted
FROM `tweets`
WHERE `id` = %s