How to auto-increment?

See the data type "SERIAL" in the PostgreSQL manual for whatever flavor of the database you are using ...

Apologies for top-posting -- challenged mail client.

HTH,

Greg W.

________________________________
From: Andre Lopes <lopes80andre@gmail.com>
To: pgsql-general@postgresql.org
Sent: Wed, December 2, 2009 2:52:51 PM
Subject: [GENERAL] How to auto-increment?

Hi,

I have a table like this:

id_product
id_increment

and I need to increment values in id_increment like this

prod_1
1

prod_1
2

prod_1
3

prod_2
1

Wich is the best way to do this? Using a trigger? Where can I find examples of plpgsql doing this?

Best Regards, André.

Sorry for my bad english.

Andre Lopes wrote:

Hi,

I have a table like this:

id_product
id_increment

and I need to increment values in id_increment like this

prod_1
1

prod_1
2

prod_1
3

prod_2
1

Wich is the best way to do this? Using a trigger? Where can I find
examples of plpgsql doing this?

offhand, I'd have another table that has (id_product primary key,
next_increment integer), and use it to populate your id_increment
fields, bumping the next_increment each time you fetch it.

I assume its OK if there are missing increment values, like if you
delete a product/increment from your table, or if in the middle of
inserting a new one, there's a transaction rollback for some reason?

In response to Andre Lopes :

Hi,
�
I have a table like this:
�
id_product
id_increment
�
and I need to increment values in id_increment like this
�
prod_1
1
�
prod_1
2
�
prod_1
3
�
prod_2
1
�

Ahh, you want to count per group, yes? Do you have 8.4? If yes, i would
suggest you an other way: use a CTE-query to count that. Or create a view
based on a CTE-query.

In your case:

select id_product, row_number() over (partition by id_product) from table

Wich is the best way to do this? Using a trigger? Where can I find examples of
plpgsql doing this?

With a TRIGGER there are possible, but what happens if you delete the
first record containing 'prod_1'?

Sorry for my bad english.

Mee too ...

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

In response to Andre Lopes :

Hi,

Pleaase answer to the list and not to me, okay?

This is an exemple in ORACLE of what I need. I will see if this works in
Postgres.

Why not? You have to rewrite it for PostgreSQL, but the way is okay.

Another question. It is possible in Postgres to use more than one Trigger by
table in Postgres?

Sure, why not? This fire in alphabetical order.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99