New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PostgreSQL - Syntax error at or near "." #4216

Closed
giannico opened this Issue Jul 30, 2015 · 5 comments

Comments

3 participants
@giannico

giannico commented Jul 30, 2015

I just transitioned a sequelize project off of sqlite and onto PostgreSql. I'm finding Postgres isn't liking the "SELECT col AS" naming conventions that Sequelize is generating. All of the queries with relationships are being aliased as names with dots in them, which is generating syntax errors. For instance, a basic auth check query for checking whether the active user (account.id = 3) has a task with the code "viewProfile", is generating the following query:

SELECT count(account.id) AS count
  FROM account AS account
  INNER JOIN role AS role ON account.roleId = role.id 
  INNER JOIN (roleTasks AS role.tasks.roleTasks
  INNER JOIN task AS role.tasks ON role.tasks.id = role.tasks.roleTasks.taskId) 
  ON role.id = role.tasks.roleTasks.roleId AND role.tasks.code = 'viewProfile'
  WHERE account.id = 3;

Which, is generating the following postgres error:

error: [app]  SequelizeDatabaseError: syntax error at or near "."

All of this worked fine with the sqlite dialect. Any help would be greatly appreciated.

@giannico

This comment has been minimized.

giannico commented Jul 30, 2015

Here's a better example, I know I haven't included my model definitions - but this is an eager selection of a simple relationship between a "role" table and a "task" table, with a "role_tasks" association table.

Generated PostgreSQL query (missing quotes on inner identifiers):

SELECT role.id, role.code, role.description, role.createdAt, role.updatedAt,
       tasks.id AS "tasks.id", tasks.code AS "tasks.code",
       tasks.description AS "tasks.description", tasks.createdAt AS "tasks.createdAt",
       tasks.updatedAt AS "tasks.updatedAt"
  FROM role AS role LEFT OUTER JOIN (
        roleTasks AS tasks.roleTasks INNER JOIN task AS tasks
        ON tasks.id = tasks.roleTasks.taskId) ON role.id = tasks.roleTasks.roleId;

Correct PostgreSQL query would be (with quotes around tasks.roleTasks):

SELECT role.id, role.code, role.description, role.createdAt, role.updatedAt,
       tasks.id AS "tasks.id", tasks.code AS "tasks.code",
       tasks.description AS "tasks.description", tasks.createdAt AS "tasks.createdAt",
       tasks.updatedAt AS "tasks.updatedAt"
  FROM role AS role LEFT OUTER JOIN (
        roleTasks AS "tasks.roleTasks" INNER JOIN task AS tasks
        ON tasks.id = "tasks.roleTasks".taskId) ON role.id = "tasks.roleTasks".roleId;
@janmeier

This comment has been minimized.

Member

janmeier commented Jul 30, 2015

Please do post model definitions and associations so we can actually reproduce the problem.

You have have quoteIdentifiers set to false by any chance?

@giannico

This comment has been minimized.

giannico commented Jul 30, 2015

I cleaned up my models significantly, removing all 'field', 'tableName' and 'as' properties (when defining relationships)... this still didn't fix the issue. I then realized I had quoteIdentifiers set to false, and I changed it to true - and magically it started working!

I'm going to work on reverting my models back to their original definition, to see if the problem was strictly related to quoteIdentifiers, or a combination of issues.

So it kind of feels like quoteIdentifiers is a mandatory option, no? I intentionally set it to false, because I didn't really want to bother quoting all of my mixed-case tables when querying from the command line... but I can obviously live with that.

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jul 30, 2015

Support for quoteIdentifiers: false is spotty at best, some 3rd party contributions have made efforts at improving it but it's not great.

If you create your table names with lowercase you should still be able to query them fine from the command line (atleast it works for me).

@janmeier

This comment has been minimized.

Member

janmeier commented Oct 12, 2015

Closing as "please don't use quoteIdentifiers: false unless you really, really, have to!"

@janmeier janmeier closed this Oct 12, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment