Re: SERIAL type - auto-increment grouped by other field

#1

Pauloviè Michal wrote:

Quote:

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/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#2

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 (AT) varlena (DOT) 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:

Quote:

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 (AT) postgresql (DOT) org)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#3

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

Quote:

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 (AT) varlena (DOT) 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 (AT) postgresql (DOT) 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 (AT) postgresql (DOT) org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: SERIAL type - auto-increment grouped by other field

comp.databases.postgresql.general comp.databases.postgresql.general

Re: SERIAL type - auto-increment grouped by other field - 02-29-2004 , 12:47 AM

Re: SERIAL type - auto-increment grouped by other field - 02-29-2004 , 04:44 PM

Re: SERIAL type - auto-increment grouped by other field - 03-01-2004 , 03:00 PM