Doctrine 2 and Postgres, serial field on non primary key columns

Question

How can I use serial datatype in Doctrine 2 on non primary key columns?

Doctrine can use serial column as primary key with generator strategy set to SEQUENCE or IDENTITY but generator can not be used on non primary key columns.

I have table like this:

create table test (
    id integer not null generated by default as identity, 
    order serial
)
constraint test_pk primary key (id)

Doctrine entity:

App\Entity\Test:
    type: entity
    table: test

    id:
        id:
            type: integer
            nullable: false
            id: true
            generator:
                strategy: SEQUENCE
            sequenceGenerator:
                sequenceName: test_id_seq
    fields:
        order:
            type: integer
            nullable: false
            column: '"order"'

What I want is that when I insert new row into this table, I don't have to specify order explicitly but db should take care of it.

In principle resulting sql code should look like this:

insert into test (DEFAULT, DEFAULT);

Without any configuration, doctrine try to insert null into column "order" resulting in error "can't insert null value into not null field"

What I tried

I tried to set default value for "order" property inside php entity class, like this:

private $order = 'DEFAULT'

but that erred out as "invalid syntax for integer"

Then I tried to put options into yaml definition like this:

fields:
        order:
            type: integer
            nullable: false
            column: '"order"'
            options:
                default: DEFAULT

That did't make any difference.

Than I tried to make custom type named order but that failed too.

Code for custom type:

use Doctrine\DBAL\Types\Type;

class OrderType extends Type
{
    public function getName(): string
    {
        return 'order';
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if (is_numeric($value)) {
            return $value;
        }

        return 'DEFAULT';
    }

    public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform)
    {
        return 'DEFAULT';
    }

    public function canRequireSQLConversion(): bool
    {
        return true;
    }

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return 'serial';
    }
}

This ends in error "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined" which is problem with method convertToDatabaseValueSQL. When I comment it out and use default implementation, error "SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "DEFAULT""

It still quote word DEFAULT but I need it to be not quoted, it must used as keyword not as string.

I think custom type should work shomehow. In old Zend Framework v1 there was class Zend_Db_Exp which when used in sql statements was not quoted and value was used as is. I think I need something similar here.

Version info

PHP 7.3

Postgres 11

Doctrine ORM 2.6 (I'll upgrade if needed)

Peter Peter · Accepted Answer · 2020-02-20T12:35:56

DB trigger can be used to manage misbehaving clients, in this case Doctrine 2 based app.

Create trigger function:

create or replace function public."t_setDefaultValueOnOrderField"()
    returns trigger as
$body$
declare
    fq_table_name text := '"' || TG_TABLE_SCHEMA || '"' || '.' || '"' || TG_TABLE_NAME || '"';
    sequence_name text := pg_get_serial_sequence(fq_table_name, 'order');
begin
    if NEW.order is null then
        NEW.order := nextval(sequence_name);
    end if;

    return NEW;
end;
$body$ language plpgsql;

And attach it to table:

create trigger "setDefaultValueOnOrderField"
    before insert
    on public.test
    for each row
execute procedure public."t_setDefaultValueOnOrderField"();

This is an ad hoc trigger for order field only, it may be adapted to handle any serial field present in target table.

Until better solution is found, this will remain as accepted answer.

Doctrine 2 and Postgres, serial field on non primary key columns

What I tried

Version info

1 Answers