Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Unanswered: how to auto increment a text field

    Hi All,

    I am using Postgres 8.4. I have a table like follows

    create table text
    (
    id text,
    emp_name text
    );

    I want to auto increment the field 'id'. The field value is like
    'Emp1'
    'Emp2'
    'Emp3'. like this.

    Is there any key word for the auto increment.
    Please help me.

    Thanks.

  2. #2
    Join Date
    May 2008
    Posts
    277
    Your best option, in my opinion, is to change it to a SERIAL field.

  3. #3
    Join Date
    Jul 2010
    Posts
    10
    thanks. I know I can use it to a serial field. but it is a integer value which is incremented. and I want to increment a text field. How can I do that.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Is there really any reason to? This isn't Access after all. But you'll need to use a trigger on inserts to combine a sequence and text.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    or as already mentioned use a number. If you really want to add on the EMP tag do it in the select or view.
    Dave

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    a less stringent approach could be this: You set up a sequence for the incrementing numeric portion, then in the default value for the text field concatenate 'Emp' to the sequence nextvalue.

    This does not enforce the format, nor does it require that the sequence be used. But, if you don't supply a pk value when you insert, pg will provide it for you.

    HOWEVER, I agree with everyone else that you would be better served by using only a serial(sequence) for the key field, and provide a view containing 'Emp' concatenated with the key field for all users/reports.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Sep 2010
    Posts
    153

    would varchar data type do instead of text?

    if you are happy with varchar daat type instead of text datatype then i can provide you the solution. it can be done by auto computing formula. it will automatically count and give you the required result and automaticall it will take your column as varchar datatype by default.

    if you are fine with varchar datatype, let me know. i will provide you the solution.

    Thanks

  8. #8
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    There is a concurrency issue with that approach sunny. If two people try to insert at the same time, they'll get the same count and one of them will fail. Sequences handle that for you.

  9. #9
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Since I have been using PostgreSQL (2 months) coming from MySQL, the lack of or missing 'auto_increment' option in PostgreSQL is extremely frustrating for me. I have to generate a sequence:

    Code:
    CREATE SEQUENCE seq_meh_id;
    SELECT setval('seq_meh_id', max(id)) FROM meh; 
    
    ALTER TABLE meh ALTER COLUMN id SET DEFAULT
    nextval('seq_meh_id');
    I hope developers change this for future releases...

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    Since I have been using PostgreSQL (2 months) coming from MySQL, the lack of or missing 'auto_increment' option in PostgreSQL is extremely frustrating for me.
    Simply use the "serial" datatype.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •