Multi row sequence?

On Fri, Dec 17, 2004 at 11:10:12AM -0000, Filip Wuytack wrote:

Is it possible to have a sequence (as a multirow prim key), where sequence
(id) only increase per group of data (grp).

E.g.
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+

PostgreSQL's sequences are simply number generators that return a
unique value. If you want to generate keys in the manner you
describe, then you could use a trigger to calculate what the next
id should be. You'd probably have to lock the table to ensure that
the operation works when multiple transactions are updating at the
same time.

Take a look at the "Triggers" chapter in the documentation, as well
as the "Trigger Procedures" section of the "PL/pgSQL - SQL Procedural
Language" chapter and the "Concurrency Control" chapter.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, Dec 17, 2004 at 11:10:12 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:

Hi,

I just started looking into PostgreSQL (coming from Mysql before), but have
a question:

Is it possible to have a sequence (as a multirow prim key), where sequence
(id) only increase per group of data (grp).

Why do you want to do this? It would be a lot simpler to generate unique
values over the table and that will work just fine if all you need
is uniqueness.

I'm working on a database that will contain companies (group) and the
relevant listing (securities-> id) and related periodic information. I want
the end users of the data to see the relationship between the 2 (comp A,
security 01, security 02;comp B, security 01, security 02) in the key
(company,security) as this would for part of the keys in all the other
related tables (depending if the info is on a company level or security
level and e.g using the date). By using a normal increment value as unique
key, I would loose this relation information in key, no?

Thanks for your input on this...

~ Filip

-----Original Message-----
From: Bruno Wolff III [mailto:bruno@wolff.to]
Sent: 18 December 2004 17:08
To: Filip Wuytack
Cc: pgsql-general@postgresql.org
Subject: Re: Multi row sequence?

On Fri, Dec 17, 2004 at 11:10:12 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:

Hi,

I just started looking into PostgreSQL (coming from Mysql before), but

have

a question:

Is it possible to have a sequence (as a multirow prim key), where sequence
(id) only increase per group of data (grp).

Why do you want to do this? It would be a lot simpler to generate unique
values over the table and that will work just fine if all you need
is uniqueness.

On Sat, Dec 18, 2004 at 17:19:27 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:

I'm working on a database that will contain companies (group) and the
relevant listing (securities-> id) and related periodic information. I want
the end users of the data to see the relationship between the 2 (comp A,
security 01, security 02;comp B, security 01, security 02) in the key
(company,security) as this would for part of the keys in all the other
related tables (depending if the info is on a company level or security
level and e.g using the date). By using a normal increment value as unique
key, I would loose this relation information in key, no?

No. Though from what you are saying here, it doesn't look like you need
that ID at all.
It looks like there should be a company table, a security table and
a company - security table. You haven't said enough about the peridoic
data to suggest how to handle that.
You probably want to use ID columns for the companies and securities,
because there might be companies with the same name (and perhaps securities
as well).
To model this relation you could use the tables below. (In reality you
probably need something more complicated to handle other information.)

create table company (
id serial primary key,
name text not null
);

create table security (
id serial primary key,
name text not null
);

create table compsec (
company int references company(id),
security int references security(id),
primary key (company, security)
);

On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <bruno@wolff.to> wrote:

On Fri, Dec 17, 2004 at 11:10:12 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:

Is it possible to have a sequence (as a multirow prim key), where
sequence (id) only increase per group of data (grp).

Why do you want to do this? It would be a lot simpler to generate unique
values over the table and that will work just fine if all you need
is uniqueness.

Here's a case where what he said would come in handy: arranging a
particular display order within the individual groups. You have the unique
key for the entire table, but you need something like a serial restricted
to just a group of rows.

--
Ciprian Popovici

On Sun, 2004-12-19 at 15:02 +0200, Ciprian Popovici wrote:

On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <bruno@wolff.to> wrote:

On Fri, Dec 17, 2004 at 11:10:12 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:

Is it possible to have a sequence (as a multirow prim key), where
sequence (id) only increase per group of data (grp).

Why do you want to do this? It would be a lot simpler to generate unique
values over the table and that will work just fine if all you need
is uniqueness.

Here's a case where what he said would come in handy: arranging a
particular display order within the individual groups. You have the unique
key for the entire table, but you need something like a serial restricted
to just a group of rows.

would a normal sequence not do if that was the only purpose?

gnari

On Sun, Dec 19, 2004 at 15:02:27 +0200,
Ciprian Popovici <ciprian@zuavra.net> wrote:

On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <bruno@wolff.to> wrote:

On Fri, Dec 17, 2004 at 11:10:12 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:

Is it possible to have a sequence (as a multirow prim key), where
sequence (id) only increase per group of data (grp).

Why do you want to do this? It would be a lot simpler to generate unique
values over the table and that will work just fine if all you need
is uniqueness.

Here's a case where what he said would come in handy: arranging a
particular display order within the individual groups. You have the unique
key for the entire table, but you need something like a serial restricted
to just a group of rows.

You wouldn't want to use a serial type for that. Inserts would become a big
pain. You can use numeric or text so that it is easy to do inserts.

On Sun, 19 Dec 2004 13:51:39 +0000 Ragnar Hafstað <gnari@simnet.is> wrote:

On Sun, 2004-12-19 at 15:02 +0200, Ciprian Popovici wrote:

On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <bruno@wolff.to>

wrote:

On Fri, Dec 17, 2004 at 11:10:12 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:

Is it possible to have a sequence (as a multirow prim key), where
sequence (id) only increase per group of data (grp).

Why do you want to do this? It would be a lot simpler to generate

unique

values over the table and that will work just fine if all you need
is uniqueness.

Here's a case where what he said would come in handy: arranging a
particular display order within the individual groups. You have the

unique

key for the entire table, but you need something like a serial

restricted

to just a group of rows.

would a normal sequence not do if that was the only purpose?

Not if you need the main key values to stay put.

--
Ciprian Popovici

On Sun, 2004-12-19 at 22:43 +0200, Ciprian Popovici wrote:

On Sun, 19 Dec 2004 13:51:39 +0000 Ragnar Hafsta� <gnari@simnet.is> wrote:

On Sun, 2004-12-19 at 15:02 +0200, Ciprian Popovici wrote:

On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <bruno@wolff.to>

wrote:

On Fri, Dec 17, 2004 at 11:10:12 -0000,
Filip Wuytack <fwuytack@fgscapital.com> wrote:

[question about mysql's special AUTO_INCREMENT on a secondary column in
a multiple-column index]

Here's a case where what he said would come in handy: arranging a
particular display order within the individual groups. You have the

unique

key for the entire table, but you need something like a serial

restricted

to just a group of rows.

would a normal sequence not do if that was the only purpose?

Not if you need the main key values to stay put.

I am afraid I do not follow you.

gnari