#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    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.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0
    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!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Originally Posted by Scatty
    however I have been told specifically not to use Sequence or autoincrement
    Then whoever told you that, doesn't understand how a transactional database works.

    Originally Posted by ScattyT
    It's is only for a very small amount of data so the timing isn't really an issue!
    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.

    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 you did something wrong:

    Code:
    postgres=> create table foo (id serial);
    CREATE TABLE
    postgres=> insert into foo values (default);
    INSERT 0 1
    postgres=> insert into foo values (default);
    INSERT 0 1
    postgres=> insert into foo values (default);
    INSERT 0 1
    postgres=> select * from foo;
     id
    ----
      1
      2
      3
    (3 rows)
    
    
    postgres=> truncate table foo restart identity;
    TRUNCATE TABLE
    postgres=> insert into foo values (default);
    INSERT 0 1
    postgres=> select * from foo;
     id
    ----
      1
    (1 row)
    Last edited by shammat; November 28th, 2012 at 04:55 PM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0
    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??
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Originally Posted by ScattyT
    ---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:

    Code:
    alter sequence ticket_no_seq owned by ticket.sno;
    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!
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo