SERIAL type - auto-increment grouped by other field

Started by Paulovič Michalabout 22 years ago6 messagesgeneral

michal@paulovic.sk

about 22 years ago

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

Shachar Shemesh

psql@shemesh.biz

about 22 years ago

In reply to: Paulovič Michal (#1)

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

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/

elein

elein@varlena.com

about 22 years ago

In reply to: Paulovič Michal (#1)

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

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.

Show quoted text

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)

Paulovič Michal

michal@paulovic.sk

about 22 years ago

In reply to: elein (#3)

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

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:

Show quoted text

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

elein

elein@varlena.com

about 22 years ago

In reply to: Paulovič Michal (#1)

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

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

Show quoted text

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

Import Notes

Reply to msg id not found: 20040301125342.D4303@cookie.varlena.com

Paulovič Michal

michal@paulovic.sk

about 22 years ago

In reply to: elein (#5)

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

:((

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:

Show quoted text

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