Increment field without using sequence or auto-increment

Hi I'm really struggling with the following and was hoping someone could help? I need to autoincrement a field by one each time a new record is inserted but also if there are no records in the table (i.e. the data has been emptied) then I need to set the field in the new record to 1.

I've managed to get the increment part to work but I can't get the field to set to 1 if an insert is attempted on an empty table. This means my 'NOT NULL' constraint on the field returns an error.

The code I have so far is:

CREATE OR REPLACE FUNCTION increment_ticket_no()
RETURNS trigger AS
$BODY$

BEGIN
IF new.tno is null THEN
new.tno = MAX(tno)+1 from ticket;
END IF;
return new;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_ticket_no
BEFORE INSERT
ON ticket
FOR EACH ROW
EXECUTE PROCEDURE increment_ticket_no();

If anyone could advise how to set the value to 1 on inserting to an empty table (within the same trigger function above) that would be great!

Thanks alot

Do not use MAX(tno)+1 . It is plain wrong and if you get it right it will be dead slow.

Do use a sequence, it's the only reliable and scalable solution to generate IDs.

If you need to reset the sequence if you delete all rows you can do so by using setval()

In case you are using TRUNCATE to get rid of the rows, you can even use the RESTART IDENTITY option to automatically reset the sequence.

Hi,

Thanks for your reply and I agree what I am trying to do is not ideal - however I have been told specifically not to use Sequence or autoincrement and to find a way to do it by incrementing the maximum value for the field by one or setting the value to 0 where the table is empty (whichever is relevant); so I do need to find a way to do this. It's is only for a very small amount of data so the timing isn't really an issue!

Also I have already tried to use sequence along with TRUNCATE and RESTART IDENTITY however it doesn't seem to work - when I insert new rows into the empty table the number increments the old max value by one!

Then whoever told you that, doesn't understand how a transactional database works.

The problem is not only the performance, but the fact that it will simply not work if two transactions are inserting at the same time.
You will get duplicates.

Then you did something wrong:

Hi,

----The code I am using is:

CREATE SEQUENCE ticket_no_seq
START 1;

CREATE OR REPLACE FUNCTION increment_ticket_no()
RETURNS trigger AS
$BODY$

BEGIN
IF new.tno is null THEN
new.tno = nextval('ticket_no_seq') ;
END IF;
return new;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_ticket_no
BEFORE INSERT
ON ticket
FOR EACH ROW
EXECUTE PROCEDURE increment_ticket_no();


----I then insert some data:

INSERT INTO ticket (tno, ecode, sno)
VALUES (NULL, 'DSCS', '1019');

INSERT INTO ticket (tno, ecode, sno)
VALUES (NULL, 'DSCS', '1013');


----then when I want to delete the data I used:

TRUNCATE table ticket RESTART IDENTITY;

---When I then try to reinsert data again it doesn;t reset ---the ticketno to 1.......do you have any ideas why??

Because the sequence is not associated with your column.

Either create the table with a serial column (see my example) or make the sequence owned by that column:



If you declare ticket.sno as DEFAULT nextval('ticket_no_seq') then you can get rid of the trigger alltogether.

And please format your SQL statements using [code] tags!