Thread: SERIAL type - auto-increment grouped by other field
hi all, I have problem with SERIAL field type (or sequence functionality). I have table with three columns - ID, IDS, NAME. I want auto-increment IDS grouped by ID. Example: 1, 1, Ferdo 1, 2, John 2, 1, Martin 1, 3, Elvira 2, 2, Georgia but... when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is: 1, 1, Ferdo 1, 2, John 2, 3, Martin 1, 4, Elvira 2, 5, Georgia where i make misstake??? how can i do it??? in documentation there is description only for one auto-increment column. I didn't find auto increment as I described upper. Do you have any idea how can I do it??? tnx a lot ________ Information from NOD32 ________ This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com
Paulovič Michal wrote: >hi all, > >I have problem with SERIAL field type (or sequence functionality). >I have table with three columns - ID, IDS, NAME. >I want auto-increment IDS grouped by ID. >Example: >1, 1, Ferdo >1, 2, John >2, 1, Martin >1, 3, Elvira >2, 2, Georgia > >but... >when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is: >1, 1, Ferdo >1, 2, John >2, 3, Martin >1, 4, Elvira >2, 5, Georgia > >where i make misstake??? how can i do it??? in documentation there is >description only for one auto-increment column. I didn't find auto increment as >I described upper. Do you have any idea how can I do it??? > >tnx a lot > > > A sequence (which is what a serial is) does not promise you consecutive numbers. For example - try out the following: begin a transaction insert a new row to your table rollback the transaction Now add a new row. You will see that the new row did not get the number that was assigned inside the transaction. All a sequence promises you is uniqueness (within the 2^32 limit), and that promise is kept for your example as well. I can think of something that may help you out here, but you will have to tell in advance how many groups you will need (how many IDs). Just create that many sequences and put the id into the sequence name. Then put in a default value based on that. -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/
One possible implementation of a two level numbering outline will be published in PostgreSQL GeneralBits Issue #64 due out Monday morning, 3/1. http://www.varlena.com/GeneralBits/ elein ============================================================ elein@varlena.com Varlena, LLC www.varlena.com 1-866-VARLENA PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ ============================================================= Its a doggy dog world out there. On Sun, Feb 29, 2004 at 02:44:01AM +0100, Paulovi? Michal wrote: > hi all, > > I have problem with SERIAL field type (or sequence functionality). > I have table with three columns - ID, IDS, NAME. > I want auto-increment IDS grouped by ID. > Example: > 1, 1, Ferdo > 1, 2, John > 2, 1, Martin > 1, 3, Elvira > 2, 2, Georgia > > but... > when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is: > 1, 1, Ferdo > 1, 2, John > 2, 3, Martin > 1, 4, Elvira > 2, 5, Georgia > > where i make misstake??? how can i do it??? in documentation there is > description only for one auto-increment column. I didn't find auto increment as > I described upper. Do you have any idea how can I do it??? > > tnx a lot > > > ________ Information from NOD32 ________ > This message was checked by NOD32 Antivirus System for Linux Mail Server. > http://www.nod32.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
tnx a lot,
but i am using PostgreSQL 7.1.2 and your script result errors:
-------
ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
Recognized languages are sql, C, internal, and created procedural languages.
-------
elein wrote:
>One possible implementation of a two level numbering outline
>will be published in PostgreSQL GeneralBits Issue #64 due
>out Monday morning, 3/1. http://www.varlena.com/GeneralBits/
>
>elein
>============================================================
>elein@varlena.com Varlena, LLC www.varlena.com
> 1-866-VARLENA
> PostgreSQL Consulting, Support & Training
>
>PostgreSQL General Bits http://www.varlena.com/GeneralBits/
>=============================================================
>Its a doggy dog world out there.
>
>
>On Sun, Feb 29, 2004 at 02:44:01AM +0100, Paulovi? Michal wrote:
>
>
>>hi all,
>>
>>I have problem with SERIAL field type (or sequence functionality).
>>I have table with three columns - ID, IDS, NAME.
>>I want auto-increment IDS grouped by ID.
>>Example:
>>1, 1, Ferdo
>>1, 2, John
>>2, 1, Martin
>>1, 3, Elvira
>>2, 2, Georgia
>>
>>but...
>>when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is:
>>1, 1, Ferdo
>>1, 2, John
>>2, 3, Martin
>>1, 4, Elvira
>>2, 5, Georgia
>>
>>where i make misstake??? how can i do it??? in documentation there is
>>description only for one auto-increment column. I didn't find auto increment as
>>I described upper. Do you have any idea how can I do it???
>>
>>tnx a lot
>>
>>
>>________ Information from NOD32 ________
>>This message was checked by NOD32 Antivirus System for Linux Mail Server.
>>http://www.nod32.com
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>
>
>________ Information from NOD32 ________
>This message was checked by NOD32 Antivirus System for Linux Mail Server.
>http://www.nod32.com
>
>
>
You just need to define 'plpgsql' as a language in
your database. At the shell, to define the language use:
createlang 'plpgsql' <dbname>;
After you do the createlang, you can write functions
using plpgsql.
Let me know if you are still having trouble.
--elein
>
> On Mon, Mar 01, 2004 at 09:52:33PM +0100, Paulovi?? Michal wrote:
> > tnx a lot,
> >
> > but i am using PostgreSQL 7.1.2 and your script result errors:
> > -------
> > ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
> > Recognized languages are sql, C, internal, and created procedural languages.
> > -------
> >
> > elein wrote:
> >
> > >One possible implementation of a two level numbering outline
> > >will be published in PostgreSQL GeneralBits Issue #64 due
> > >out Monday morning, 3/1. http://www.varlena.com/GeneralBits/
> > >
> > >elein
> > >============================================================
> > >elein@varlena.com Varlena, LLC www.varlena.com
> > > 1-866-VARLENA
> > > PostgreSQL Consulting, Support & Training
> > >
> > >PostgreSQL General Bits http://www.varlena.com/GeneralBits/
> > >=============================================================
> > >Its a doggy dog world out there.
> > >
> > >
> > >On Sun, Feb 29, 2004 at 02:44:01AM +0100, Paulovi? Michal wrote:
> > >
> > >
> > >>hi all,
> > >>
> > >>I have problem with SERIAL field type (or sequence functionality).
> > >>I have table with three columns - ID, IDS, NAME.
> > >>I want auto-increment IDS grouped by ID.
> > >>Example:
> > >>1, 1, Ferdo
> > >>1, 2, John
> > >>2, 1, Martin
> > >>1, 3, Elvira
> > >>2, 2, Georgia
> > >>
> > >>but...
> > >>when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is:
> > >>1, 1, Ferdo
> > >>1, 2, John
> > >>2, 3, Martin
> > >>1, 4, Elvira
> > >>2, 5, Georgia
> > >>
> > >>where i make misstake??? how can i do it??? in documentation there is
> > >>description only for one auto-increment column. I didn't find auto
> > >>increment as
> > >>I described upper. Do you have any idea how can I do it???
> > >>
> > >>tnx a lot
> > >>
> > >>
> > >>________ Information from NOD32 ________
> > >>This message was checked by NOD32 Antivirus System for Linux Mail Server.
> > >>http://www.nod32.com
> > >>
> > >>---------------------------(end of broadcast)---------------------------
> > >>TIP 2: you can get off all lists at once with the unregister command
> > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >>
> > >>
> > >
> > >
> > >________ Information from NOD32 ________
> > >This message was checked by NOD32 Antivirus System for Linux Mail Server.
> > >http://www.nod32.com
> > >
> > >
> > >
:(( sorry At server ther is istalled pgsql 7.1.2 but creatlang works from 7.2.X and upper version :( do you have any other ideas?? elein wrote: > You just need to define 'plpgsql' as a language in > your database. At the shell, to define the language use: > > createlang 'plpgsql' <dbname>; > > After you do the createlang, you can write functions > using plpgsql. > > Let me know if you are still having trouble. > >--elein > > >>On Mon, Mar 01, 2004 at 09:52:33PM +0100, Paulovi?? Michal wrote: >> >> >>>tnx a lot, >>> >>>but i am using PostgreSQL 7.1.2 and your script result errors: >>>------- >>>ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. >>> Recognized languages are sql, C, internal, and created procedural languages. >>>------- >>> >>>elein wrote: >>> >>> >>> >>>>One possible implementation of a two level numbering outline >>>>will be published in PostgreSQL GeneralBits Issue #64 due >>>>out Monday morning, 3/1. http://www.varlena.com/GeneralBits/ >>>> >>>>elein >>>>============================================================ >>>>elein@varlena.com Varlena, LLC www.varlena.com >>>> 1-866-VARLENA >>>> PostgreSQL Consulting, Support & Training >>>> >>>>PostgreSQL General Bits http://www.varlena.com/GeneralBits/ >>>>============================================================= >>>>Its a doggy dog world out there. >>>> >>>> >>>>On Sun, Feb 29, 2004 at 02:44:01AM +0100, Paulovi? Michal wrote: >>>> >>>> >>>> >>>> >>>>>hi all, >>>>> >>>>>I have problem with SERIAL field type (or sequence functionality). >>>>>I have table with three columns - ID, IDS, NAME. >>>>>I want auto-increment IDS grouped by ID. >>>>>Example: >>>>>1, 1, Ferdo >>>>>1, 2, John >>>>>2, 1, Martin >>>>>1, 3, Elvira >>>>>2, 2, Georgia >>>>> >>>>>but... >>>>>when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is: >>>>>1, 1, Ferdo >>>>>1, 2, John >>>>>2, 3, Martin >>>>>1, 4, Elvira >>>>>2, 5, Georgia >>>>> >>>>>where i make misstake??? how can i do it??? in documentation there is >>>>>description only for one auto-increment column. I didn't find auto >>>>>increment as >>>>>I described upper. Do you have any idea how can I do it??? >>>>> >>>>>tnx a lot >>>>> >>>>> >>>>>________ Information from NOD32 ________ >>>>>This message was checked by NOD32 Antivirus System for Linux Mail Server. >>>>>http://www.nod32.com >>>>> >>>>>---------------------------(end of broadcast)--------------------------- >>>>>TIP 2: you can get off all lists at once with the unregister command >>>>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >>>>> >>>>> >>>>> >>>>> >>>>________ Information from NOD32 ________ >>>>This message was checked by NOD32 Antivirus System for Linux Mail Server. >>>>http://www.nod32.com >>>> >>>> >>>> >>>> >>>> > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > >________ Information from NOD32 ________ >This message was checked by NOD32 Antivirus System for Linux Mail Server. >http://www.nod32.com > > >