Orm: GeneratedValue strategy AUTO should use IDENTITY on PostgreSQL

[Seldaek]

As per http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#identifier-generation-strategies - AUTO uses SEQUENCE by default on PostgreSQL. This seems sub-optimal as it forces INSERT queries to provide the id explicitly using nextval('sequence_name') which is not easy to do in ORM queries, and requires that you know the sequence name. On the other hand if it's set to IDENTITY it works better as it does it like MySQL's AUTO_INCREMENT, you can just omit the id field entirely.

As doing this would cause schema changes I can imagine it won't be fixed in a minor release, but should be considered for v3 I think.

[Majkl578]

AFAIK this would only work with SERIAL fields (fields with default value being nextval('...')). This is not default behavior and IMHO should not be considered as such.

[flaushi]

Hmm, I also stumpled upon this issue.
I have an id field with @GeneratedValue, which gives 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;

So the sequence is not assigned as default value. However, when adding entitities, the id is incremented. I don't understand why.

[DrummerKH]

@flaushi Because of doctrine getting ID from the sequence and add to the INSERT statement.

[breitsmiley]

Starting with the PostgreSQL 10 more prefered way for using autoincrement is syntax GENERATED AS IDENTITY that is very similar to mysql AUTO_INCREMENT

It would be great to support by doctrine this features

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

http://www.postgresqltutorial.com/postgresql-identity-column/
https://postgrespro.com/docs/postgresql/11/sql-createtable

[menezes-]

In light of the changes introduced in PostgreSQL 10 (as mentioned by @breitsmiley) this should be reviewed. At least make the default when Postgres version equals 10