Thread: Re: [GENERAL] Limitation

The answer is that you are thinking as indexes in the context of fast lookup
on fields.

However my requirement comes from a need to ensure that every single field
in the
table forms a unique combination. (not my design, I might add)

Now you know why, can you append it to the TODO list?

On thinking about it, I wonder if the same field can appear more than
once in an index. Now that would be weird!



>> 1.  Have more than 7 fields on a btree index
>
>We have never understood why someone would want an index with more than
>seven columns.
>
>--
>  Bruce Momjian                        |  http://www.op.net/~candle

John,

Can't you do this check in a trigger?  Have a select count(*) with a
where so that all columns are checked against the row being
edited/inserted.  If the count is not zero then rejectr the
update/insert.

If you have one index which is for 7 fields (I guess the ones that
differ most often) then the select should be quite fast.

NB I have not written any postgresql triggers so I am not certain of
what is possible in them. But you could definately do this in the
various dbms I have written triggers for.

I realise this will be slow to write the code for so it would probably
be best to script the trigger definitions.

I came to sql from a background in DataFlex and Btrieve so I understand
many of the problems. We had similar indexes in some db's and it took a
while to get people used to designing without indexes and then add them
for speed.

Dave

John Huttley wrote:
>
> The answer is that you are thinking as indexes in the context of fast lookup
> on fields.
>
> However my requirement comes from a need to ensure that every single field
> in the
> table forms a unique combination. (not my design, I might add)
>
> Now you know why, can you append it to the TODO list?
>
> On thinking about it, I wonder if the same field can appear more than
> once in an index. Now that would be weird!
>
> >> 1.  Have more than 7 fields on a btree index
> >
> >We have never understood why someone would want an index with more than
> >seven columns.
> >
> >--
> >  Bruce Momjian                        |  http://www.op.net/~candle

--
David Warnock
Sundayta Ltd

On Fri, 25 Jun 1999, John Huttley wrote:

    Then make a trigger.  Your attitude towards this project would
make sense if you were a paying customer.  If you want to use postgres,
then use it, nobody gets paid any less if you don't.  If you want
structural changes in the database to accomodate a bad design, then you're
free to make them, you have the source.

# The answer is that you are thinking as indexes in the context of fast lookup
# on fields.
#
# However my requirement comes from a need to ensure that every single field
# in the
# table forms a unique combination. (not my design, I might add)
#
# Now you know why, can you append it to the TODO list?
#
# On thinking about it, I wonder if the same field can appear more than
# once in an index. Now that would be weird!
#
#
#
# >> 1.  Have more than 7 fields on a btree index
# >
# >We have never understood why someone would want an index with more than
# >seven columns.
# >
# >--
# >  Bruce Momjian                        |  http://www.op.net/~candle
#
#
#
#

--
SA, beyond.com           My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> The answer is that you are thinking as indexes in the context of fast lookup
> on fields.
>
> However my requirement comes from a need to ensure that every single field
> in the
> table forms a unique combination. (not my design, I might add)

Ooow, that is a good point.  Unique requires a unique index.

>
> Now you know why, can you append it to the TODO list?

Can I get someone else to agree it should be added?

>
> On thinking about it, I wonder if the same field can appear more than
> once in an index. Now that would be weird!

I bet it can.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026