Search code examples
phppostgresqlsymfonyauto-increment

autoincrement id null when inserting into postgresql table

Hi guys first post on here so hopefully I have done this correctly I am also new to symfony ad postgresql so maybe I have done a silly mistake :)

Problem description

I have an entity Address that holds address information. I generate the schema using doctrine:schema:create (Database type Postgresql)

The table is created and can be seen in the database. I need to import information into this table which i get from an open street maps database. So I am trying to do an insert into my entities table. The problem I am having is that I am not passing an ID as it is meant to be autoincremented but the database is rejecting my insert into request as id is a null value.

I am new to symfony and new to postgresql so perhaps I am making a stupid basic mistake. I hope someone can help me (On a side note this project was originally in mysql and it worked fine as IDs were autoincrement)

Details below:

ENTITY YAML

DD\Bundle\DDGeneratorBundle\Entity\Address:

    type:  entity
    table: address
    readOnly: true
    repositoryClass: DD\Bundle\DDGeneratorBundle\Repository\AddressRepository

    id:
        id:
            type: integer
            generator:
                strategy: AUTO

    fields:
        number:
            type: string
            length: 255
        street:
            type: string
            length: 255
        city:
            type: string
            length: 255
        postcode:
            type: string
            length: 255
        latitude:
            type: decimal
            scale: 7
        longitude:
            type: decimal
            scale: 7

    indexes:
        coordinates:
            columns: [latitude, longitude]

    lifecycleCallbacks: {  }

TABLE Generated by symfony

-- Table: address

-- DROP TABLE address;

CREATE TABLE address
(
  id integer NOT NULL,
  "number" character varying(255) NOT NULL,
  street character varying(255) NOT NULL,
  city character varying(255) NOT NULL,
  postcode character varying(255) NOT NULL,
  latitude numeric(10,7) NOT NULL,
  longitude numeric(10,7) NOT NULL,
  CONSTRAINT address_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE address
  OWNER TO postgres;

-- Index: coordinates

-- DROP INDEX coordinates;

CREATE INDEX coordinates
  ON address
  USING btree
  (latitude, longitude);

Insert Into SQL

insert into address ("number",street,city,postcode,latitude,longitude)
select *
from dblink('dbname=osm',
            '
SELECT tag_housenumber.v as number,
tag_street.v as street,
tag_city.v as city,
tag_postcode.v as postcode,
ST_Y(ST_Transform(nodes.geom, 4326)) AS latitude,
ST_X(ST_Transform(nodes.geom, 4326)) AS longitude


FROM ways

LEFT JOIN way_tags AS tag_housenumber ON tag_housenumber.way_id = ways.id
LEFT JOIN way_tags AS tag_street ON tag_street.way_id = ways.id
LEFT JOIN way_tags AS tag_city ON tag_city.way_id = ways.id
LEFT JOIN way_tags AS tag_postcode ON tag_postcode.way_id = ways.id
INNER JOIN way_nodes ON ways.id = way_nodes.way_id
INNER JOIN nodes on way_nodes.node_id = nodes.id

WHERE tag_housenumber.k = ''addr:housenumber''
AND tag_street.k = ''addr:street''
AND tag_city.k = ''addr:city''
AND tag_postcode.k = ''addr:postcode'' ')
       as t1("number" text,street text,city text,postcode text,latitude numeric(10,7),longitude numeric(10,7));

ERROR

The following is the error i get from pgadmin3 with address details X'd out:

ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, X, XXXXXX, XXXX, XXXX, XXXX, XXXX). ****** Error ******

ERROR: null value in column "id" violates not-null constraint SQL state: 23502 Detail: Failing row contains (null, X, XXXXX, XXXXXX, XXXX, XXXX, XXXX).

Solution

  • Solution

    The problem was that Symfony does not add the auto increment default value for the id. Symfony does not appear to need this as it does a separate request to get the sequence value required for the new id.

    I have modified my yaml file so that symfony adds the default value for the id field.

    Obviously for anyone else looking at how to solve this problem you will need to change the string value in the default to match that of the sequence name generated for your entity in symfony.

    DD\Bundle\DDGeneratorBundle\Entity\Address:

    type:  entity
    table: dd_address
    readOnly: true
    repositoryClass: DD\Bundle\DDGeneratorBundle\Repository\AddressRepository

    id:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
            options:
                default: nextval('dd_address_id_seq')

    fields:
        number:
            type: string
            length: 255
        street:
            type: string
            length: 255
        city:
            type: string
            length: 255
        postcode:
            type: string
            length: 255
        latitude:
            type: decimal
            scale: 7
        longitude:
            type: decimal
            scale: 7

    indexes:
        coordinates:
            columns: [latitude, longitude]

    lifecycleCallbacks: {  }

    Hopefully this helps someone else in the same situation

    Kind Regards David