0

I recently had to overhaul a Django project that originally used MySQL, but in order to deploy to Heroku (as per their recommendations in their docs regarding Django deployment) I migrated the MySQL database to Postgres via the following method:

  1. Installed dependencies: pyscopg2 and py-mysql2pgsql

  2. Setup Postgres database

  3. Migrated data base: $ py-mysql2pgsql — this creates a mysql2pgsql.yml file, which looks like this:

    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. Transferred the data via: $ py-mysql2pgsql -v -f mysql2pgsql.yml

After many cups of coffee, the deployment to Heroku was successful (for the most part, until I discovered this issue today) and the app works as expected on heroku and when I run $ heroku local. But today, when accessing a certain a function on the front end, an error with the database appeared in the browser: 

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 ...

I believe that the relevant code is in a file called 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

I'm aware of a few posts that talk about similar issues:

Postgresql - concat_ws like function?

Postgresql GROUP_CONCAT equivalent?

Which led me to try and change group_concat(au.email SEPARATOR '\n') to string_agg(au.email SEPARATOR '\n') and then array_agg(au.email SEPARATOR '\n') but I'm still getting the same error.

Is there another way I should approach this and adjust this function to effect a workaround?

UPDATE: for anyone who's interested, the selected answer (and comments) provided by Consider Me below led me to the following altered query statement which works with no issues thus far:

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")

As the original developer of this app has created all the tables with double quotes, this led to the column p.projecttype_id does not exist error and other similar does not exist errors I kept getting when all I had done was deleted SEPARATOR and changed group_concat (neither of which exist in Postgres) to string_agg. In other words, as per Consider Me's suggestion, I had to encapsulate with double quotes pretty much all the table and column names (and escape the quotes of course).

Community
  • 1
  • 1
AdjunctProfessorFalcon
  • 1,790
  • 6
  • 26
  • 62

1 Answers1

1

As you've already seen you need to replace GROUP_CONCAT() with STRING_AGG(). Include every column that is not used inside an aggregate function in GROUP BY clause.

select 
  p.id, 
  p.title, 
  p.description, 
  p.deadline, 
  pt.name as project_type, 
  s.name as status, 
  sc.name as sport_category, 
  string_agg(au.email, E'\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 1,2,3,4,5,6,7

There is no SEPARATOR keyword in Postgres (at least I'm not aware of its' existence).

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • Thanks for the suggestion. Now I'm getting this error message in the browser when trying to load that page: `column p.projecttype_id does not exist LINE 2: ..._manager_id = au.id inner join project_type pt on p.projectT...` – AdjunctProfessorFalcon Mar 22 '16 at 22:08
  • Error is pretty straightforward. You don't have a column in your table that you're referring to (projecttype_id in table project) – Kamil Gosciminski Mar 22 '16 at 22:11
  • Except that there is a `projectType_id` column (type Integer) in the `project` table, hence my confusion. – AdjunctProfessorFalcon Mar 22 '16 at 22:30
  • Maybe there is a `"projectType_id"` column, but not `projectType_id`. Try encapsulating that name with double quotes. This would correspond to backticks that are commonly used in MySQL. – Kamil Gosciminski Mar 22 '16 at 22:44
  • Further errors you're receiving are not a fit for this question. Create a new one and if my answer was helpful in resolving the issue from your question, you can mark is as accepted. – Kamil Gosciminski Mar 22 '16 at 22:50