Doctrine - PostgreSQL - Uppercase and Spaces in Ta

Question

Having issues with Doctrine 2.3 and PostgreSQL with Spaces and Upper case Field / Table names

Example: ( Yes we are working on migrating away from this )

SELECT "Field Name"
FROM "Table Name"

We also have a mix of the two formats

SELECT "Field Name", another_field_name
FROM "Table Name", another_table_name

When using doctrine I'm getting a PDOException. When looking at the error I see that there are no double quotes around Fields / Tables ( names ) that have Upper case and Spaces.

Is there a fix? Workaround?

Here is an example of what Doctrine is generating

SELECT t0.TheId AS theid1, t0.Name AS name2, t0.User AS user3
FROM The Table t0 
WHERE t0.TheId = 1234

Here is how I need it

SELECT t0."TheId" AS theid1, t0."Name" AS name2, t0."User" AS user3
FROM "The Table" t0 
WHERE t0."TheId" = 1234

Answer 1

Unfortunately the accepted answer won't work in the presented situation. The backticks will not work as every column in a doctrine query gets an alias by using the lower cased name with an appended identifier. This alias would have spaces in it and would therefore generate a sql error when passed to postgres.

The only way this could be corrected is in Doctrine itself when the query is generated.

update it seems this may have been corrected in the latest version of doctrine2. It now replaces non acceptable characters.

Answer 2

You may need to use backticks to let Doctrine know that it should be quoted: Quoting reserved words. E.g:

<?php
/** @Column(name="`number`", type="integer") */
private $number;