Thread: Multi row sequence?
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). E.g. +--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+ Many thanks, Filip
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.
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
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.