Doctrine2 doesn't set sequence to default for id column (postgres)

Just a simple example: If I want create a table with auto fill id in postgres I run this sql:

CREATE SEQUENCE person_id_seq  START 1;

CREATE TABLE person (
    id         integer PRIMARY KEY DEFAULT nextval('person_id_seq'),
    name       varchar(100) NOT NULL
);

and in doctrine I set all property

class Person {

/**
 * @Id
 * @Column(type="integer", nullable=false)
 * @GeneratedValue(strategy="SEQUENCE")
 * @SequenceGenerator(sequenceName="person_id_seq", initialValue=1, allocationSize=100)
 */
private $id;

but when I generated sql (php doctrine orm:schema-tool:create --d开发者_开发百科ump-sql) I got it:

CREATE TABLE person (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL
);
CREATE SEQUENCE person_id_seq INCREMENT BY 100 MINVALUE 1 START 1

but don't set it to default

\d person

      Column       |              Type              | Modifiers
-------------------+--------------------------------+-----------
 id                | integer                        | not null
...
..
.

---

From the fine manual:

4.8.1. Identifier Generation Strategies
...
AUTO (default): Tells Doctrine to pick the strategy that is preferred by the used database platform. The preferred strategies are IDENTITY for MySQL, SQLite and MsSQL and SEQUENCE for Oracle and PostgreSQL. This strategy provides full portability.
...
IDENTITY: Tells Doctrine to use special identity columns in the database that generate a value on insertion of a row. This strategy does currently not provide full portability and is supported by the following platforms: MySQL/SQLite (AUTO_INCREMENT), MSSQL (IDENTITY) and PostgreSQL (SERIAL).

They suggest AUTO for maximum portability:

/**
 * @Id
 * @Column(type="integer", nullable=false)
 * @GeneratedValue
 */

That should create and wire up a sequence for you. An alternative would be to ask for a serial column using the IDENTITY strategy:

/**
 * @Id
 * @Column(type="integer", nullable=false)
 * @GeneratedValue(strategy="IDENTITY")
 */

This one should create your id column as type serial and PostgreSQL will create the sequence and set up the default value for you.

The documentation indicates that what you're doing should work but the documentation usually only provides a simplified version of reality.

Try using strategy="AUTO". If that doesn't work, try strategy="IDENTITY".

---

I encountered this problem today and I found that:

• IDENTITY work good, because it uses SERIAL type for PostgreSQL, which automatically creates related sequence and set default value as nextval(sequence)

• AUTO creates table and then related sequence, but doesn't set default value for id column. It can be set by adding following code:

  /**
   * Webpage's ID
   *
   * @ORM\Id
   * @ORM\Column(type="integer", options={"default"="nextval('webpages_id_seq'::regclass)"})
   * @ORM\GeneratedValue(strategy="IDENTITY")
   */
  protected $id;
  

  but unfortunately Doctrine create table first, so we need to swap SQL code creating table and sequence in order that sequence will be created first

• SEQUENCE works the same as AUTO

---

I also encounter, that the sequence is generated, but not assigned.

So, this is the output of bin/console doctrine:schema:create --dump-sql

CREATE TABLE data_sample (id INT NOT NULL, hashvalue VARCHAR(64) DEFAULT NULL, date TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, refdate TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, value NUMERIC(20, 2) NOT NULL, PRIMARY KEY(id));

CREATE SEQUENCE data_sample_id_seq INCREMENT BY 1 MINVALUE 1 START 1;

---

Example of XML PostgreSQL Serial type

<id name="addressId" type="integer" column="address_id">
  <generator strategy="IDENTITY"/>
  <options>
    <option name="default">nextval('decide_config.address_address_id_seq'::regclass)</option>
  </options>
</id>