469,167 Members | 1,285 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Auto increment/sequence on multiple columns?

Is it possible to have a sequence across two columns. For example

table1
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+---+---+

Would I have to create a new sequence for every unique 'a' column?
That seems pretty tedious. -Nick
Nov 23 '05 #1
4 3514
How does this imply one sequence? Is it guaranteed that for each value
of a, the values of b will be equivalent to all (and only) values of a?

There's plenty of flexibility within postgres for ways to use sequences.

Regardless, I think you need to have (and present) a better idea of
what you're trying to do with your data, which itself might be tedious,
but good data design will save you countless headaches down the road.

-tfo

On Sep 10, 2004, at 7:32 PM, Nick wrote:
Is it possible to have a sequence across two columns. For example

table1
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+---+---+

Would I have to create a new sequence for every unique 'a' column?
That seems pretty tedious. -Nick

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
This is actually a table that holds message threads for message
boards. Column A is really 'message_board_id' and column B is
'thread_id'. I would like every new thread for a message board to have
a 'thread_id' of 1 and increment from there on. -Nick
Nov 23 '05 #3
On Sun, Sep 12, 2004 at 09:16:37 -0700,
Nick <nb********@hotmail.com> wrote:
This is actually a table that holds message threads for message
boards. Column A is really 'message_board_id' and column B is
'thread_id'. I would like every new thread for a message board to have
a 'thread_id' of 1 and increment from there on. -Nick


Since thread ids should be opaque just use one sequence that generates
all thread ids rather than having separate thread id sets for each message
board id.

If you really want to have thread ids start at 1 and increase by 1 for
each new thread per message boad, sequences aren't the right tool.
The simplest way to do this is when adding a new thread to lock the
table against concurrent updates and then use a new thread id that
is one higher than the current highest for the message board of interest.
This doesn't handle what to do if you need to delete a thread.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4
You'll probably need a sequence per thread. A sequence is not
necessarily tied to a column.

-tfo

On Sep 12, 2004, at 11:16 AM, Nick wrote:
This is actually a table that holds message threads for message
boards. Column A is really 'message_board_id' and column B is
'thread_id'. I would like every new thread for a message board to have
a 'thread_id' of 1 and increment from there on. -Nick

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Sherwood Botsford | last post: by
8 posts views Thread by JD via AccessMonster.com | last post: by
13 posts views Thread by S.Dickson | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.