Doctrine column id does not exist on PostgreSQL

Question

I have been forced into using PostgreSQL. I have read something about it but still it's new and I have no idea why I'm getting this error:

SQLSTATE[42703]: Undefined column: 7 ERROR: column t0.id does not exist LINE 1: SELECT t0.id AS id1, t0.user AS user2, t0.email AS ma...

I checked that id column exists for thousand times (almost literally).

I asked my friend and he told me that there is no auto increment in PostgreSQL and I have to use sequences. I found that Doctrine generates sequences automatically when I set @GeneratedValue to auto (which is default). And yes, those sequences are there.

Here is my entity:

<?php

/**
 * @ORM\Entity
 */
class User
{

    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /** @ORM\Column(type="string", nullable=true) */
    protected $name;

    // some more properties similar to $name

In other question (PostgreSQL column 'foo' does not exist) they wanted to see output of \d table. Here is it:

northys=# \d user
                         Table "public.user"
  Column  |          Type          |            Modifiers            
----------+------------------------+---------------------------------
 id       | integer                | not null
 name     | character varying(255) | default NULL::character varying
 email    | character varying(255) | not null
 password | character varying(255) | not null
Indexes:
    "user_pkey" PRIMARY KEY, btree (id)
    "uniq_8d93d649e7927c74" UNIQUE, btree (email)
Referenced by:
    TABLE "child" CONSTRAINT "fk_22b3542941807e1d" FOREIGN KEY (teacher_id) REFERENCES "user"(id)
    TABLE "event" CONSTRAINT "fk_3bae0aa7a76ed395" FOREIGN KEY (user_id) REFERENCES "user"(id)

I'm having PostgreSQL 9.4.1 and I`m not using any database specific plugins for doctrine. Do you have any ideas why this shouldn't work? I got stuck and trying to find out the solution for days.

Answer 1

user is a reserved word. It's an alias for current_user.

regress=> SELECT * FROM user;
 current_user 
--------------
 myusername
(1 row)

If you want to use user as a table name, since it's a reserved word you must quote the identifier, e.g.:

SELECT id FROM "user";

Your ORM should be quoting all identifiers, or at least reserved words. Failure to do so is a bug in your ORM. You can work around the ORM bug by using a non-reserved word as a table name.

I think it's a bit of a wart in psql that it automatically quotes identifiers you pass to backslash commands. So \d user will work but select * from user won't. You should have to write \d "user". The same issue arises with case sensitivity where \d MyTable works but SELECT * FROM MyTable won't work, you have to write SELECT * FROM "MyTable".

---

It'd be nice to give a HINT message about this in the error. Unfortunately the parser and planner doesn't really have enough information at the time the "column does not exist" error gets generated to know that you originally wrote a keyword, all it sees is a function scan at that point.