pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddata dependent sequences?
11+ messages / 6 participants
[nested] [flat]
* data dependent sequences?
@ 2007-07-15 15:28 Stuart McGraw <smcg2297@frii.com>
0 siblings, 2 replies; 11+ messages in thread
From: Stuart McGraw @ 2007-07-15 15:28 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-15 16:04 Ragnar <gnari@hive.is>
parent: Stuart McGraw <smcg2297@frii.com>
1 sibling, 1 reply; 11+ messages in thread
From: Ragnar @ 2007-07-15 16:04 UTC (permalink / raw)
To: Stuart McGraw <smcg2297@frii.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-15 16:07 chester c young <chestercyoung@yahoo.com>
parent: Stuart McGraw <smcg2297@frii.com>
1 sibling, 1 reply; 11+ messages in thread
From: chester c young @ 2007-07-15 16:07 UTC (permalink / raw)
To: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-17 13:34 Stuart <smcg2297@frii.com>
parent: Ragnar <gnari@hive.is>
0 siblings, 1 reply; 11+ messages in thread
From: Stuart @ 2007-07-17 13:34 UTC (permalink / raw)
To: pgsql-sql
"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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-17 13:34 Stuart <smcg2297@frii.com>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 11+ messages in thread
From: Stuart @ 2007-07-17 13:34 UTC (permalink / raw)
To: pgsql-sql
"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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-17 14:19 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Stuart <smcg2297@frii.com>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Broersma Jr @ 2007-07-17 14:19 UTC (permalink / raw)
To: Stuart <smcg2297@frii.com>; pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-17 15:00 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Stuart <smcg2297@frii.com>
0 siblings, 1 reply; 11+ messages in thread
From: Andrew Sullivan @ 2007-07-17 15:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-17 15:40 Stuart <smcg2297@frii.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 11+ messages in thread
From: Stuart @ 2007-07-17 15:40 UTC (permalink / raw)
To: pgsql-sql
"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 :-)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-17 16:44 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Stuart <smcg2297@frii.com>
0 siblings, 1 reply; 11+ messages in thread
From: Andrew Sullivan @ 2007-07-17 16:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-17 18:23 Stuart <smcg2297@frii.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 11+ messages in thread
From: Stuart @ 2007-07-17 18:23 UTC (permalink / raw)
To: pgsql-sql
"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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: data dependent sequences?
@ 2007-07-17 20:48 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Stuart <smcg2297@frii.com>
0 siblings, 0 replies; 11+ messages in thread
From: Andrew Sullivan @ 2007-07-17 20:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-07-17 20:48 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-15 15:28 data dependent sequences? Stuart McGraw <smcg2297@frii.com>
2007-07-15 16:04 ` Ragnar <gnari@hive.is>
2007-07-17 13:34 ` Stuart <smcg2297@frii.com>
2007-07-17 15:00 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-17 15:40 ` Stuart <smcg2297@frii.com>
2007-07-17 16:44 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-17 18:23 ` Stuart <smcg2297@frii.com>
2007-07-17 20:48 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-15 16:07 ` chester c young <chestercyoung@yahoo.com>
2007-07-17 13:34 ` Stuart <smcg2297@frii.com>
2007-07-17 14:19 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox