# AUTOINC With Multiples Columns ODBC **URL:** \ **Category:** questions\ **Tags:** odbc, postgresql\ **Created:** [December 1, 2022, 8:55pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723 "2022-12-01T20:55:48Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![ThalesSilveirs](https://clarionhub.com/user_avatar/clarionhub.com/thalessilveirs/32/3642_2.png) [@ThalesSilveirs](https://clarionhub.com/u/ThalesSilveirs)\ **Post date:** [December 1, 2022, 8:55pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/1 "2022-12-01T20:55:49Z")
Hello guys, I would like to ask, how to do an autoincrement in C9, using PGSQL. I want to do it using two columns. EXAMPLE: A B 1 1 1 2 2 1 1 3 2 2 ANY TIPS or CODE? ---
**Author:** ![totalhip](https://clarionhub.com/user_avatar/clarionhub.com/totalhip/32/1477_2.png) [@totalhip](https://clarionhub.com/u/totalhip)\ **Post date:** [December 1, 2022, 10:34pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/2 "2022-12-01T22:34:49Z")
Should be coded in a before insert trigger.for the PG table. ---
**Author:** ![totalhip](https://clarionhub.com/user_avatar/clarionhub.com/totalhip/32/1477_2.png) [@totalhip](https://clarionhub.com/u/totalhip)\ **Post date:** [December 5, 2022, 4:03pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/3 "2022-12-05T16:03:09Z")
An example for the before insert PG trigger code: IF NEW.b = 0 OR NEW.b IS NULL THEN SELECT COALESCE(max(b),0)+1 INTO NEW.b FROM mytable WHERE a = NEW.a; END IF; RETURN NEW; ---
**Author:** ![mriffey](https://clarionhub.com/letter_avatar_proxy/v4/letter/m/cab0a1/32.png) [@mriffey](https://clarionhub.com/u/mriffey)\ **Post date:** [March 19, 2023, 1:58pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/4 "2023-03-19T13:58:52Z")
If you use a serial or big serial data type (it’s an integer/dbl with a sequence to initialize the integer), then the db will do it for you. You can also use a sequence manually, and run a simple query to get the next value. Or leave such work to the templates, which I avoid with sql. ---
**Author:** ![JonW](https://clarionhub.com/user_avatar/clarionhub.com/jonw/32/1344_2.png) [@JonW](https://clarionhub.com/u/JonW)\ **Post date:** [March 21, 2023, 12:56pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/5 "2023-03-21T12:56:45Z")
Hi, There is a recent thread on the softvelocity.clarion.databasedrivers newsgroup started Jan 28 by Ramon Hernandez. If I had to summarize I would say: don’t do it like that, it’s not worth it. Just use a serial data type for the second one, and if you want to show things consecutively numbered within each column A, do that in a view when you are displaying things to the user. ---
**Author:** ![totalhip](https://clarionhub.com/user_avatar/clarionhub.com/totalhip/32/1477_2.png) [@totalhip](https://clarionhub.com/u/totalhip)\ **Post date:** [March 21, 2023, 1:49pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/6 "2023-03-21T13:49:56Z")
> [@JonW](#): > > If I had to summarize I would say: don’t do it like that, it’s not worth it. Just use a serial data type for the second one Perhaps a detail that is being lost - serial data type for primary key. Autoinc a different column value for a two column display order. Yes? ---
**Author:** ![JonW](https://clarionhub.com/user_avatar/clarionhub.com/jonw/32/1344_2.png) [@JonW](https://clarionhub.com/u/JonW)\ **Post date:** [March 24, 2023, 5:05pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/7 "2023-03-24T17:05:12Z")
The original suggestion was this: EXAMPLE: A B 1 1 1 2 2 1 1 3 2 2 And I’m suggesting that you could happily have data that looks like this: A B 1 1 1 2 2 3 1 4 2 5 The B column could, yes, be used as a primary key. It would have unique values, and you would set it up as a serial column (i.e. autonumbered by Postgres). Your Column A presumably gets set some way – it’s a customer ID or something. And if you want the user to see those items for Customer 1 as items 1,2, and 3 rather than 1,2 and 4, you have a view like: select a,row\_number() over (partition by a order by b) item from mytable ---
**Author:** ![ThalesSilveirs](https://clarionhub.com/user_avatar/clarionhub.com/thalessilveirs/32/3642_2.png) [@ThalesSilveirs](https://clarionhub.com/u/ThalesSilveirs)\ **Post date:** [March 29, 2023, 2:05pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/8 "2023-03-29T14:05:14Z")
I managed to solve it using some functions, I’ll leave an example here: ```auto !PEGA ULTIMO NUMERO IF SELF.Request = InsertRecord CLEAR(DEP:Record,1)! if MASTER is the prefix DEP:empresa = GLO:Empresa_Padrao SET(DEP:por_empresa_deposito,DEP:por_empresa_deposito) PREVIOUS(depositos) IF ~ERRORCODE() IF DEP:empresa <> GLO:Empresa_Padrao LOC:Codigo = 1 ELSE LOC:Codigo = DEP:deposito + 1 END ELSE LOC:Codigo = 1 END CLEAR(depositos) DEP:empresa = GLO:Empresa_Padrao DEP:deposito = LOC:Codigo END ``` ---
**Author:** ![ThalesSilveirs](https://clarionhub.com/user_avatar/clarionhub.com/thalessilveirs/32/3642_2.png) [@ThalesSilveirs](https://clarionhub.com/u/ThalesSilveirs)\ **Post date:** [March 29, 2023, 2:06pm UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/9 "2023-03-29T14:06:18Z")
I define primary keys, however, I don’t use autoincrement, either from SQL or Clarion. ---
**Author:** ![JonW](https://clarionhub.com/user_avatar/clarionhub.com/jonw/32/1344_2.png) [@JonW](https://clarionhub.com/u/JonW)\ **Post date:** [March 31, 2023, 2:09am UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/10 "2023-03-31T02:09:47Z")
The code you posted is exactly what Clarion would have written for you if you had defined your key in the dictionary as autoincremented, except for the Clarion version is a little better about not overwriting things you already had in the buffer and dealing with conflicts if two stations are trying to add a record at the same time. So, congratulations, you have now written code there was no need for you to write; you could have just checked auto-increment in the dictionary. Personally, with PG as the backend I would still prefer to have PG do the autonumbering. It’s a lot quicker to ask PG to add in a number from a sequence than it is to do a “Select max(id)+1 from mytable where category = ‘whatever’” and have the result passed back to your client program to get the number. ---
**Author:** ![ThalesSilveirs](https://clarionhub.com/user_avatar/clarionhub.com/thalessilveirs/32/3642_2.png) [@ThalesSilveirs](https://clarionhub.com/u/ThalesSilveirs)\ **Post date:** [May 4, 2023, 11:51am UTC](https://clarionhub.com/t/autoinc-with-multiples-columns-odbc/5723/11 "2023-05-04T11:51:29Z")
Thanks @JonW , but I put it in the Dictionary, and even so, it didn’t work, I don’t know why, even more so with a key that sometimes reaches 3 fields. But it has worked well for me, so that’s what programming is, if it works well for me, it’s ok. And so I can have more flexibility in Clarion. And now, I’m already using a location at runtime, Codigo$ between LOC:Codigo.