了解从MySQL迁移到Postgres后“SEPARATOR”处或附近的语法错误

我最近不得不对一个最初使用MySQL的Django项目进行全面检查,但为了部署到Heroku(按照他们在 docs regarding Django deployment )我通过以下方法将MySQL数据库迁移到Postgres:

1. 已安装的依赖项: pyscopg2 和 py-mysql2pgsql

2. 设置Postgres数据库

3. 迁移的数据库: $ py-mysql2pgsql 这将创建 mysql2pgsql.yml 文件,如下所示:

   mysql: hostname: localhost port: 3306 socket: /tmp/mysql.sock username: foo password: bar database: your_database_name compress: false destination: postgres: hostname: localhost port: 5432 username: foo password: bar database: your_database_name

4. 通过以下方式传输数据: $ py-mysql2pgsql -v -f mysql2pgsql.yml

喝了很多杯咖啡后,Heroku的部署成功了(大部分情况下,直到我今天发现这个问题),应用程序在Heroku和我运行时都能正常运行 $ heroku local 。但今天,当访问前端的某个函数时,浏览器中出现了数据库错误:

DatabaseError at /projects/

syntax error at or near "SEPARATOR"
LINE 1: ...s, sc.name as sport_category, string_agg(au.email SEPARATOR ...
                                                             ^

Request Method:     GET
Request URL:    http://0.0.0.0:8989/projects/
Django Version:     1.5.1
Exception Type:     DatabaseError
Exception Value:    

syntax error at or near "SEPARATOR"
LINE 1: ...s, sc.name as sport_category, string_agg(au.email SEPARATOR ... 

我相信相关代码位于名为business.py的文件中:

def getAllProjects(self):
        '''
        @note: Retrieve all projects
        '''

        from django.db import connection
        cursor = connection.cursor()

        cursor.execute("select p.id, p.title, p.description, p.deadline, pt.name as project_type, s.name as status, sc.name as sport_category, group_concat(au.email SEPARATOR '\n') as project_managers from project p left join project_manager_in_project pmip on p.id = pmip.project_id left join auth_user au on pmip.project_manager_id = au.id inner join project_type pt on p.projectType_id = pt.id inner join status s on p.status_id = s.id inner join sport_category sc on p.sportCategory_id = sc.id where p.deleted = 0 group by p.id")
        projects = cursor.fetchall()
        return projects

我知道有几个帖子讨论了类似的问题:

Postgresql - concat_ws like function?

Postgresql GROUP_CONCAT equivalent?

这让我尝试改变 group_concat(au.email SEPARATOR '\n') 到 string_agg(au.email SEPARATOR '\n') 然后 array_agg(au.email SEPARATOR '\n') 但我还是得到了同样的错误。

有没有其他方法可以解决这个问题并调整此函数以实现变通方法?

更新 :对于任何感兴趣的人来说,下面由“考虑我”提供的选定答案(和评论)让我找到了以下经过修改的查询语句,到目前为止没有任何问题:

cursor.execute("select \"project\".\"id\", project.title, project.description, \"project\".\"deadline\", \"pt\".\"name\" as \"project_type\", \"sc\".\"name\" as status, \"sc\".\"name\" as sport_category, string_agg(au.email, E'\n') as project_managers from project left join project_manager_in_project pmip on project.id = pmip.project_id left join auth_user au on pmip.project_manager_id = au.id inner join project_type pt on project.\"projectType_id\" = pt.id inner join status s on project.\"status_id\" = \"s\".id inner join sport_category sc on \"project\".\"sportCategory_id\" = \"sc\".\"id\" where project.deleted = 0 group by 1,2,3,4,5,6,7")

由于此应用程序的原始开发人员创建了所有带双引号的表,这导致 column p.projecttype_id does not exist 错误和其他类似 does not exist 当我所做的一切都被删除时,我不断收到错误 SEPARATOR 并已更改 group_concat (两者均不存在于Postgres中)至 string_agg 换言之,根据“考虑我”的建议,我必须用双引号封装几乎所有的表和列名称(当然,还要转义引号)。