how to auto increment a text field

debadutta · #1 (**permalink**) 08-24-10, 06:03

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.

futurity · #2 (**permalink**) 08-24-10, 12:35

Your best option, in my opinion, is to change it to a SERIAL field.

debadutta · #3 (**permalink**) 08-25-10, 01:13

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.

artacus72 · #4 (**permalink**) 08-25-10, 11:49

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.

dav1mo · #5 (**permalink**) 08-25-10, 12:00

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

loquin · #6 (**permalink**) 09-02-10, 13:52

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.

sunny_007 · #7 (**permalink**) 10-04-10, 08:41

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

artacus72 · #8 (**permalink**) 10-04-10, 15:11

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.

CarlosinFL · #9 (**permalink**) 10-06-10, 19:42

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...

shammat · #10 (**permalink**) 10-07-10, 03:45

Quote:

Originally Posted by CarlosinFL

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech