Thread: 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

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

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

On Sun, Sep 12, 2004 at 09:16:37 -0700,
  Nick <nboutelier@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.

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