[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.
Re: [GENERAL] 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 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.
Re: [GENERAL] How to auto-increment?
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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to auto-increment?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to auto-increment?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general