Search code examples
mysqldatabasepostgresqlcompatibility

Errors running MySQL queries in PostgreSQL

I wrote queries in what is supposed to be standard SQL, and tested them in MySQL.

Now when I try to run them in PostgreSQL I get all sorts of errors, which I don't really understand. Here's the original query, that works in MySQL:

CREATE VIEW popularCSsections AS (
    SELECT  sect.csid, COUNT(sc.sid) as numStudents
    FROM courseSection sect, studentCourse sc, department d
    WHERE sect.csid = sc.csid 
        AND sect.dcode = d.dcode 
        AND dname = "Computer Science"
    GROUP BY sect.csid
    HAVING numStudents > 2
);

gives this error:

psql:a2tables:60: ERROR:  column "Computer Science" does not exist
LINE 8:   AND department.dname = "Computer Science" 
                                 ^

Can you please help me understand the nature of the errors and help me fix them?

Additional issue:

CREATE VIEW popularCSsections AS (
SELECT  sect.csid, COUNT(sc.sid) as numStudents
FROM courseSection sect, studentCourse sc, department d
WHERE sect.csid = sc.csid 
    AND sect.dcode = d.dcode 
    AND dname = 'Computer Science'
    GROUP BY sect.csid
    HAVING numStudents > 2
);

Error:

psql:a2tables:70: ERROR:  column "numstudents" does not exist
LINE 8:  HAVING numStudents > 2
                ^
Solution

  • You need to quote strings with single quotes:

     AND dname = 'Computer Science'

    What's happening here is that the double quotes are being interpreted as "identifier quotes" which signal that you want the contents interpreted as a database identifier (column or table name).