DBAL-127: PostgreSQL: Quote "User" because it's a reserved word #1222

Closed
doctrinebot opened this Issue Jun 14, 2011 · 10 comments

4 participants

@doctrinebot

Jira issue originally created by user felicitus:

I have an entity "User" in D2-ORM which works fine on MySQL. However, when I tried to create the schema on PostgreSQL 9.0, it failed with the following error:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "User"
LINE 1: CREATE TABLE User (id INT NOT NULL, username VARCHAR(50) NOT...
^' in /usr/share/php/Doctrine/DBAL/Connection.php:577

The generated statement caused this was: CREATE TABLE User (id INT NOT NULL, username VARCHAR(50) NOT NULL, password VARCHAR(32) NOT NULL, admin BOOLEAN NOT NULL, PRIMARY KEY(id))

"User" needs to be quoted in order to work (I tested against PostgreSQL 9.0).

@doctrinebot

Comment created by @beberlei:

Doctrine does not do auto-escaping.

Please read the docs on quoting reserved words: http://www.doctrine-project.org/docs/orm/2.0/en/reference/basic-mapping.html#quoting-reserved-words

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot

Comment created by felicitus:

It's not that easy.

I can, of course, escape the table in the entity, e.g.

/*** @Entity @Table(name="User") **/

However, when using the SchemaTool, it will fail on the 2nd run because it tries to:

DROP TABLE User

That fails, of course.

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@samusenkoiv

how are things so far with this issue ?

@deeky666
Doctrine member

@samusenkoiv this issue is old and should be fixed already considering that USER is registered as reserved keyword in Doctrine and the platform explicitly quoting table names in getCreateTableSQL(). What issue are you having?

@samusenkoiv

I've already found the way to define user table:
table: "`user`"

Thanks for your attention.

@deeky666
Doctrine member

@samusenkoiv you can do that by explicit quoting for sure but DBAL supports auto quoting reserved keywords. Can you please test if it works without quotes? If not, which statement(s) are failing? Because if this still is a bug, it needs our attention. Also please tell which DBAL version you are using. Thanks for your help!

@deeky666 deeky666 reopened this Jan 15, 2016
@deeky666 deeky666 assigned deeky666 and unassigned beberlei Jan 15, 2016
@samusenkoiv

You're right, I tried it with:
table: user
table: "user"
table: 'user'
And everything worked well, I probably made a mistake.
Thank for a fast feedback.

@deeky666 deeky666 added Fixed and removed Requires Feedback labels Jan 15, 2016
@deeky666
Doctrine member

@samusenkoiv you're welcome. Thanks for investigating. Closing again as fixed, but as I do not know when it was fixes, no milestone attached.

@deeky666 deeky666 closed this Jan 15, 2016
@samusenkoiv

Run into this issue again.
When using Doctrine\ORM\Tools\Pagination\Paginator with Doctrine\ORM\EntityRepository .
I do something like that

$queryBuilder = $repository->createQueryBuilder();
$paginator = new Paginator($queryBuilder);
count($paginator); // here we go

I got this error

An exception occurred while executing 'SELECT count(DISTINCT u0_.id) AS sclr_0 FROM user u0_':
SQLSTATE[42703]: Undefined column: 7 ERROR: column u0_.id does not exist
LINE 1: SELECT count(DISTINCT u0_.id) AS sclr_0 FROM user u0_

It is solvable by specifying @ORM/Table("`user`") in User class

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