Update Fails when using autoincrement (Serial) fields

andychez · Post by **andychez** » Thu 10 Mar 2011 15:59

Hello, we have converted from MySQL to postgre.
Previous Auto-Increment fields are handled by Serial Fields using Sequences within postgre. The serial field is the primary index.

This causes a few problems with pgDAC.

1 -The insert query needs to be changed so that it does not include the Serial Field or this fails with null violation error.

2 - After an insert operation, further edits on the inserted record fail with "Update Fail, Found 0 records" I assume that this fails because pgDAC does not know the new value of the serial field.

Once the dataset is refreshed, edits work fine.

Is there a more elegant way around this than refreshing the entire dataset?

Thanks
AndyC++

bork · Post by **bork** » Fri 11 Mar 2011 11:16

Hello

I created the following table and sequence:

Code: Select all

CREATE SEQUENCE seq_test_table
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
  
CREATE TABLE test_table
(
  id integer NOT NULL DEFAULT nextval('seq_test_table'::regclass),
  "name" character varying(250),
  CONSTRAINT test_table_pk PRIMARY KEY (id)
);

To fill ID field automatically you can use two ways:

1. Set the Options.DefaultValues option to True. In this case default value for the ID field will be set to "nextval(''test.seq_test_table''::regclass)" and the ID field will be filled automatically.

2. Set the KeySequence property to "seq_test_table". In this case field ID will be filled from this sequence.

Please provide us sample code that raises the "Update Fail, Found 0 records" error on update and we will help you to resolve this issue.

andychez · Post by **andychez** » Fri 11 Mar 2011 18:20

Hi,

Setting the DefaultValues to True then regenerating the insert query (I removed the serial field remember) fixed both problems.

Thanks for your help.
AndyC++