1

So I created an sql script with a lot of data in it that I ran in a postgresql Database. I use the Doctrine2 ORM inside the application to handle the database.

The problem I would like to fix is as follow :
if my insertion line is like this: 

INSERT INTO interneo.services VALUES
    (1, 'test', 'test', 'root', 'foo', false, now(), now());

If I now want to add a new service from the application (that will insert the line using Doctrine), It will send me an error saying the id 1 is allready taken. If I reload the page it will work. I guess Doctrine has some sort of counter that starts at 1 for every model.

Is there a way to make that "counter" start with the id of the last inserted line? Or maybe a way inside my script that doctrine will take into account? (like DEFAULT instead of 1 or something, but postgresql doesn't seem to have something like that, or I didn't search right).

Anyway, thanks for your help. I very much appreciate it.

Arnaud Rochez
  • 578
  • 2
  • 4
  • 14
  • Why not read existing values before insert? – svgrafov Oct 04 '17 at 15:40
  • Thanks for your time! Wouldn't that be a problem if doctrine has to read the whole table before every insert? It would slow things down since there are some big tables no? – Arnaud Rochez Oct 04 '17 at 15:45
  • I meant to try to select single record with id that you want to insert. – svgrafov Oct 04 '17 at 15:48
  • Going to try it out, thanks for your help ! – Arnaud Rochez Oct 04 '17 at 15:57
  • It seems to work, but there are a lot of tables, and functions that were already implemented, don't you know if there is some kind of more generic way? Like a method from doctrine or something ? :/ – Arnaud Rochez Oct 04 '17 at 16:00
  • Can you show us structure of your table? Pretty sure that id is being incremented using sequence. It would explain why it worked after reloading page (sequences get incremented even if inserts fail). – Łukasz Kamiński Oct 05 '17 at 10:34

1 Answers1

0

I'm assuming the first field you insert is your id..

The normal way

Don't specify the id at all and let the DBS (Postgres) handle this.. Please see this answer how to do that: How to set auto increment primary key in PostgreSQL?

Set the primary key attribute on the field and then just omit the ID on your INSERT statement - no need to specify.

And yes, then you may need to specify the field names in your insert statement - but doing that is good practice anyway.

Please note that if your 'id' property is an auto increment field, you need to add the @Id annotation to your field in the entity Document for Doctrine. See here. Normally, every Entity has this.

Postgres: Set the primary key start

I just seen your problem is that you imported into a table that hasn't had a PRIMARY KEY field and you cannot seem be able to recreate it.

Two ways:

  • If the IDs don't matter (nobody references them), just export the rows, add the primary, import the rows again without the ID field
  • Create a custom sequence:

First create it:

  CREATE SEQUENCE services_sequence
  start 48454189498
  increment 1;

Then you can use that in your query:

INSERT INTO interneo.services VALUES
(nextval('services_sequence'), 'test', 'test', 'root', 'foo', false, now(), now());

Doctrine even has a SequenceGenerator - you can apparently use that. Check out the documentation.

The complicated Doctrine way

Alternatively, you could setup a prePersist Event Listener in Doctrine. That Listener would make a single MAX(id) query on your table, increment by one and set the ID on your entity.

This is prone to failure also, as nobody can tell if between the MAX() query of the Listener and the actual INSERT statement another record could be inserted - and then we would have another collision.

So - just let Postgres handle that. That's what it's there for ;-)

narcoticfresh
  • 257
  • 4
  • 10