Thread: data dependent sequences?
Advice requested :-) I have a table like:
CREATE TABLE items ( id INT, typ INT ... PRIMAY KEY (seq,typ));
I would like 'id' to be like a SERIAL except that I
want independent sequences for each value of 'typ'.
So if 'items' is:
id typ ----+----- 1 'a' 2 'a' 3 'a' 1 'b'
then doing:
INSERT items(typ) VALUES('a'); INSERT items(typ) VALUES('b');
will result in:
id typ ----+----- 1 'a' 2 'a' 3 'a' 1 'b' 4 'a' 2 'b'
Because there can be dozens of values of 'typ'
and new ones added not infrequently, creating
a postgresql sequence for each seems awkward.
Are there other ways I could accomplish this?
On sun, 2007-07-15 at 09:28 -0600, Stuart McGraw wrote: > Advice requested :-) I have a table like: > > CREATE TABLE items ( > id INT, > typ INT ... > PRIMAY KEY (seq,typ)); > > I would like 'id' to be like a SERIAL except that I > want independent sequences for each value of 'typ'. what possible advantage could there be to that? if you need gapless series, then sequences (and serial) are not adequate anyways. just use one sequence. gnari
>
> CREATE TABLE items (
> id INT,
> typ INT ...
> PRIMAY KEY (seq,typ));
>
> id typ
> ----+-----
> 1 'a'
> 2 'a'
> 3 'a'
> 1 'b'
> 4 'a'
> 2 'b'
>
you will need to use pre insert trigger since you cannot use column
references in default expression.
you could use this same trigger to either:
- create sequences as needed and apply the right one
- with locking, lookup for last id of typ
- with locking, keep another table of typ and nextval
____________________________________________________________________________________
Yahoo! oneSearch: Finally, mobile search
that gives answers, not web links.
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC
"Ragnar" <gnari@hive.is> wrote in message news:1184515497.5778.141.camel@localhost.localdomain... > On sun, 2007-07-15 at 09:28 -0600, Stuart McGraw wrote: > > Advice requested :-) I have a table like: > > > > CREATE TABLE items ( > > id INT, > > typ INT ... > > PRIMAY KEY (seq,typ)); > > > > I would like 'id' to be like a SERIAL except that I > > want independent sequences for each value of 'typ'. > > what possible advantage could there be to that? > > if you need gapless series, then sequences (and serial) > are not adequate anyways. > > just use one sequence. I am not looking for gapless sequences. The reason I want to do this is the "typ" column is actually an indicator of the source of the rest of the infomation in the row. The "rules" for assigning the id number vary depending on the source -- in some cases they start at 1 and increment by one, in other cases they start at, say, 1000000 and increment by 10. There are a lot existing data using these rules and I cannot change that. I can of course have the application do the assignments, but in general eould prefer to push this down into the database if posible.
"chester c young" <chestercyoung@yahoo.com> wrote in message news:609880.51564.qm@web54306.mail.re2.yahoo.com... > > > > CREATE TABLE items ( > > id INT, > > typ INT ... > > PRIMAY KEY (seq,typ)); > > > > > id typ > > ----+----- > > 1 'a' > > 2 'a' > > 3 'a' > > 1 'b' > > 4 'a' > > 2 'b' > > you will need to use pre insert trigger since you cannot use column > references in default expression. > > you could use this same trigger to either: > - create sequences as needed and apply the right one > - with locking, lookup for last id of typ > - with locking, keep another table of typ and nextval Thanks, that summerizes the options nicely. I noticed that sequences are tables with a single row that defines the sequence properties. I was hoping that there was some way of using a sequence with multiple rows to maintain multiplre sequences in a sngle table, which would make having a large number of sequences a little less cluttered, but sounds like I need to implement that from scratch.
--- Stuart <smcg2297@frii.com> wrote: > > you will need to use pre insert trigger since you cannot use column > > references in default expression. > > > > you could use this same trigger to either: > > - create sequences as needed and apply the right one > > - with locking, lookup for last id of typ > > - with locking, keep another table of typ and nextval here is supplementary information of gap-less sequences: http://www.varlena.com/GeneralBits/130.php Regards, Richard Broersma Jr.
On Tue, Jul 17, 2007 at 07:34:26AM -0600, Stuart wrote: > I am not looking for gapless sequences. The reason I > want to do this is the "typ" column is actually an indicator > of the source of the rest of the infomation in the row. Why do you need the sequence to be 1. . .n for each typ, then? If they're just there to preserve order, one sequence will work just fine. Otherwise, I think you have a normalisation problem. A -- Andrew Sullivan | ajs@crankycanuck.ca A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton
"Andrew Sullivan" <ajs@crankycanuck.ca> wrote in message news:20070717150046.GC21688@phlogiston.dyndns.org... > On Tue, Jul 17, 2007 at 07:34:26AM -0600, Stuart wrote: > > I am not looking for gapless sequences. The reason I > > want to do this is the "typ" column is actually an indicator > > of the source of the rest of the infomation in the row. > > Why do you need the sequence to be 1. . .n for each typ, then? If > they're just there to preserve order, one sequence will work just > fine. Otherwise, I think you have a normalisation problem. I probably shouldn't have indicated that 'typ' was part of the PK, or named the other column 'id'. There is actually a separate (surrogate) PK, and there is a unique index on the on (id,typ) only to prevent accidental dupicates. So 'id' is not really structually important -- it is a value that exists soley for the UI. In the app, the user can explicity request an explicit 'id' value. My desire to use a sequence to assign them is to handle the 99% common case where the user doesn't care about assigning a specific id, and just wants the "next" resonable value, consistent with the other values for that typ row. If there are 3 rows of typ=20 and 2000000 rows of typ=21, I don't want the next typ=20 row to get an id of 2000004, when the other rows have values of 1,2,3. This is simply a user expectation, based on existing data, that I can't change. I would just prefer to implement it in the database if possible rather than than the app. Hope I have clarified a little :-)
On Tue, Jul 17, 2007 at 09:40:21AM -0600, Stuart wrote: > is not really structually important -- it is a value that > exists soley for the UI. Hmm. Maybe you should use generate_series() for the UI instead? It would always give you the order you like, you could use the universal sequence or whatever for your ORDER BY clause, and not store data that you actually don't care about. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner
"Andrew Sullivan" <ajs@crankycanuck.ca> wrote in message news:20070717164437.GJ21688@phlogiston.dyndns.org... > On Tue, Jul 17, 2007 at 09:40:21AM -0600, Stuart wrote: > > is not really structually important -- it is a value that > > exists soley for the UI. > > Hmm. Maybe you should use generate_series() for the UI instead? It > would always give you the order you like, you could use the universal > sequence or whatever for your ORDER BY clause, and not store data > that you actually don't care about. Except that the value does matter outside of the database and thus needs to be remembered. It is not used for ordering at all.
On Tue, Jul 17, 2007 at 12:23:21PM -0600, Stuart wrote: > > Except that the value does matter outside of the database and thus > needs to be remembered. It is not used for ordering at all. If you have two rows of data, where one column is data that varies as a function of the data in some other column, then what you have is data from two different tables, and not one table at all. At least, according to the rules of normalisation I know. My best guess, however, is that you're trying to implement a poor requirement. My bet is that if you dig harder, you'll find out what the requirement _really_ is, and then you won't have to implement what sounds like a bad idea. A -- Andrew Sullivan | ajs@crankycanuck.ca However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton