pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexing question
3+ messages / 3 participants
[nested] [flat]
* Indexing question
@ 2012-08-14 16:54 amit sehas <cun23@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: amit sehas @ 2012-08-14 16:54 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
In SQL, given a table T, with two fields f1, f2,
is it possible to create an index such that the same record is indexed in the index, once with field f1 and once with field f2. (I am not looking for a compound index in which the key would look like <f1, f2>, instead there should be two entries in the index <f1> and <f2>).
we have a few use cases for the above, perhaps we need to alter the schema somehow to accommodate the above,
any advice is greatly appreciated ..
thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Indexing question
@ 2012-08-14 17:19 David Johnston <polobo@yahoo.com>
parent: amit sehas <cun23@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: David Johnston @ 2012-08-14 17:19 UTC (permalink / raw)
To: 'amit sehas' <cun23@yahoo.com>; pgsql-sql; pgsql-general@postgresql.org
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of amit sehas
> Sent: Tuesday, August 14, 2012 12:55 PM
> To: pgsql-sql@postgresql.org; pgsql-general@postgresql.org
> Subject: [GENERAL] Indexing question
>
> In SQL, given a table T, with two fields f1, f2,
>
> is it possible to create an index such that the same record is indexed in
the
> index, once with field f1 and once with field f2. (I am not looking for a
> compound index in which the key would look like <f1, f2>, instead there
> should be two entries in the index <f1> and <f2>).
>
> we have a few use cases for the above, perhaps we need to alter the
> schema somehow to accommodate the above,
>
> any advice is greatly appreciated ..
>
> thanks
>
In short: No, you cannot create an index on T in the way you describe. You
need to create a new table: TF, with columns {T(id), f}, and having rows 1
and 2 with the same T(id) value; An index over "f" on table TF will then
contain both values.
Slightly longer:
This seems like a classic case of column duplication. I am assuming that
the columns in question are, say, phone1 and phone2 an you want to be able
to search by phone number without having to specify the two fields
separately. The correct way to do this is to create a "phone" table and add
a single line for each phone number you want to store (along with the
corresponding FK value of the original table) - with possibly a "phone_type"
column.
If this is not what you are after then you should be more explicit in your
requirements. Why is creating two separate indexes (on f1 and f2) not
acceptable?
If indeed you are dealing with variations of the above example you really
want to consider modifying your schema to use two tables with a one-to-many
relationship because the current scenario begs the question(s): "why only f1
and f2? Why isn't there an f3?". The idea is that there are generally 3
separate cardinalities {0, 1, >1}. Zero you ignore, 1 you generally put on
the same table - though not always, and more-than-one you create a separate
table and store multiple values as separate rows instead of as columns.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Indexing question
@ 2012-08-14 17:33 Andreas Kretschmer <akretschmer@spamfence.net>
parent: amit sehas <cun23@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Andreas Kretschmer @ 2012-08-14 17:33 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
amit sehas <cun23@yahoo.com> wrote:
> In SQL, given a table T, with two fields f1, f2,
>
> is it possible to create an index such that the same record is indexed
> in the index, once with field f1 and once with field f2. (I am not
> looking for a compound index in which the key would look like <f1,
> f2>, instead there should be two entries in the index <f1> and <f2>).
>
> we have a few use cases for the above, perhaps we need to alter the
> schema somehow to accommodate the above,
2 separate indexes? One on f1 and one on f2?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-08-14 17:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-14 16:54 Indexing question amit sehas <cun23@yahoo.com>
2012-08-14 17:19 ` David Johnston <polobo@yahoo.com>
2012-08-14 17:33 ` Andreas Kretschmer <akretschmer@spamfence.net>
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